11.4. Times¶
In this section we present a simple, yet powerful primitive set operator, Times. Its power comes because it is the basis of the exactly-matching binary operators we present later, and is also used in the very interesting compare-join operator, which will appear near the end of this book.
Times works on no columns and accepts any two input relations A and B – different bases, same base, or same relation. To produce the result relation Times does no matching but instead merely juxtaposes each row of one input relation against each row of the other input relation. It is like the mathematical cartesian product set function, and is often called cartesian product or cross-join by database theorists and practitioners.
Why learn Times? Well, an understanding of what Times does and when to use it underpins your mastery of of the the rest of the non-Primitive exactly matching operators and a not-exactly matching operator, compare join. While you may hear other people say, or may have thoughts yourself, such as “Wow, Times is expensive, so don’t use it,” this is flawed logic. Understanding and using times opens the door to some amazingly interesting analyses on relational databases. So let’s see how it works.
The result relation’s name and structure for a Times are straightforward.
Name characteristics- the result relation has:
No row modifier.
No column modifier.
An identifier consisting of A’s identifying columns plus B’s identifying columns.
Structure characteristics- the result relation has:
A column corresponding to each A relation column plus each B relation column.
As many rows as the product of the number of rows of the input relations.
11.4.1. Example 1: different bases as input relations¶
The input relations can be the same relation, same base, or different base. We will first provide a different base example.
English query
Find each Creature-Skill pair.
It is important to understand that this query is finding the universe of all possible creatures paired with all possible skills. The result relation has many more rows than, for example, the Achievement relation in the small creature database.
Precedence Chart
The chart for this query looks like this:
The SQL to complete the times is also quite simple:
The result relation has more rows than the embedded version of SQLite in this web-based book will let us display, as you will see when you run the tab named SQL Query. The next tab to its right shows how we can save the result. The tab labeled ‘SQL count’ will show you how many total rows are in the result.
This universe of all possible pairings of a creature with a skill can be useful to compare to what is in Achievement or Aspiration, which also have the base noun Creature-Skill Pair in their alternative names (Achieving Creature-SKill Pair and Aspiring Creature-Skill pair, respectively).
Other pairings the result from Times are also extremely useful: we will see an example in the next chapter, which provides an example of Times followed by Filter and Project. This turns out to be a very prevalent pattern in queries.
11.4.2. Example 2: Same relation inputs¶
This example may seem odd at first, but as we continue our study later with match join examples, you will see where this Times example leads to interesting queries that start with this as the first underlying step of the join.
English query
Find each Creature-Creature pair.
Precedence Chart
The chart for this query looks like this:
The chart above and the following SQL for this query represent a challenge that arises with Times that we must consider. When there are like-named columns in the two input relations (this is the extreme case of that where each pair of columns coming from the inputs are named the same), the result relation must have new names for those columns. Note that we also apply a tag near the arrow going out of each input relation that is essentially renaming each input so that we can keep track of each one.
In the query below, notice how the keyword AS is being used to rename each column from each of the two relations. Also note how those relations are renamed to C1 and C2 in the FROM clause.
Since there are 13 creatures, the result has 13 * 13 rows, or 169 rows, including each creature paired with itself. In the first tab, the displayed result is again truncated to 100 rows. In the other tabs, you can see how an intermediate table is created and its rows can be counted to see that it indeed has 169 rows.
Important
It is also important to realize that the result of this type of Times operation when the two inputs are the same relation is an ordered pair.. In this case it is an ordered pair of creatures. Convince yourself of why this is by looking at the partial result before moving on.
11.4.3. What is the base?¶
Determining the base of the result relation from a times seems straightforward from these two examples, which I will now slightly rephrase here, given the important aspect of the second same-relation Times example mentioned above:
Creature-Skill pair.
Ordered Creature1-Creature2 pair.
The identifying columns of the new result relation are the combination of the identifying columns of the two input relations (note this in the charts for these examples if you did not catch that).
However, the naming becomes more difficult when the inputs are longer intermediate relations from other operations. Go to the next section to see an interesting combination of the operators we have seen so far where this becomes apparent.
11.4.4. Exercises¶
Try creating the precedence charts for these queries. Use a drawing tool such as draw.net.
English Query:
Ordered creature – skill pair where each creature has achieved the same skill
Ordered creature – town pair
Ordered skill – town pair