Subqueries in FROM Clauses

Subqueries in FROM Clauses

Another use for subqueries is as relations in a FROM clause. In a FROM list, instead of a stored relation, we may use a parenthesized subquery. Since we don't have a name for the result of this subquery, we must give it a tuple-variable alias.
We then refer to tuples in the result of the subquery as we would tuples in any relation that appears in the FROM list.

Example 1 : Let us reconsider the problem of "Conditions Involving Tuples" Example 1, where we wrote a query that finds the producers of Harrison Ford's movies. Suppose we had a relation that gave the certificates of the producers of those movies. It would then be a simple matter to look up the names of those producers in the relation MovieExec. Figure 1 is such a query.

Finding the producers of Fords movies using a subquery in the FROM clause

Lines (2) through (7) are the FROM clause of the outer query. In addition to the relation MovieExec, it has a subquery. That subquery joins Movie and StarsIn on lines (3) through (5), adds the condition that the star is Harrison Ford on line (6), and returns the set of producers of the movies at line (2). This set is given the alias Prod on line (7).

At line (8), the relations MovieExec and the subquery aliased Prod are joined with the requirement that the certificate numbers be the same. The names of the producers from MovieExec that have certificates in the set aliased by Prod is returned at line (1).

SQL Join Expressions

We can build relations by a number of variations on the join operator applied to two relations. These variants include products, natural joins, theta-joins, and outerjoins. The result can stand as a query by itself. Alternatively, all these expressions, since they produce relations, may be used as subqueries in the FROM clause of a select-from-where expression.

The simplest form of join expression is a cross join; that term is a synonym for what we called a Cartesian product or just "product" in "Selection / Cartesian Product". For example, if we want the product of the two relations


we can say

and the result will be a nine-column relation with all the attributes of Movie and StarsIn. Every pair consisting of one tuple of Movie and one tuple of StarsIn will be a tuple of the resulting relation.

The attributes in the product relation can be called R.A, where R is one of the two joined relations and A is one of its attributes. If only one of the relations has an attribute named A, then the R and dot can be dropped, as usual. In this example, since Movie and StarsIn have no common attributes, the nine attribute names suffice in the product.

On the other hand, the product by itself is rarely a useful operation. A more conventional theta-join is obtained with the keyword ON. We put JOIN between two relation names R and S and follow them by ON and a condition. The meaning of JOIN. . .ON is that the product of R x S is followed by a selection for whatever condition follows ON.

Example 2 : Assume we want to join the relations


with the condition that the only tuples to be joined are those that refer to the same movie. That is, the titles and years from both relations must be the same. We can ask this query by


The result is again a nine-column relation with the obvious attribute names. On the other hand, now a tuple from Movie and one from  StarsIn combine to form a tuple of the result only if the two tuples agree on both the title and year. As a result, two of the columns are redundant, because every tuple of the result will have the same value in both the title and movieTitle components and will have the same value in both year and movieYear.

If we are concerned with the fact that the join above has two redundant components, we can use the whole expression as a subquery in a FROM clause and use a SELECT clause to remove the undesired attributes. Thus, we could write.

to get a seven-column relation which is the Movie relation's tuples, each extended in all possible ways with a star of that movie.