*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.

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.

### Tags

- subquery
- cross join
- attributes
- tuple
- Queries in PSM
- Using Shared Variables
- System Aspects of SQL
- Instead-Of Triggers
- Schema-Level Constraints and Triggers
- Tuple-Based CHECK Constraints
- Constraints on Attributes and Tuples
- Deferring the Checking of Constraints
- Maintaining Referential Integrity
- Declaring Foreign-Key Constraints
- Keys Declared With UNIQUE
- Constraints and Triggers
- Modifying Views
- View Definitions
- Introduction to Selection of Indexes
- Default Values / Indexes
- Simple Table Declarations
- Defining a Relation Schema in SQL
- Deletion / Updates
- Database Modifications
- Grouping / HAVING Clauses
- Full-Relation Operations
- Natural Joins / Outerjoins
- Correlated Subqueries
- Conditions Involving Tuples
- Subqueries
- Union, Intersection, and Difference of Queries
- Interpreting Multirelation Queries
- Disambiguating Attributes
- Queries Involving More Than One Relation
- The Truth-Value UNKNOWN
- Null Values and Comparisons Involving NULL
- Selection in SQL
- Projection in SQL
- Additional Constraint Examples
- Referential Integrity Constraints
- Constraints on Relations
- Extending the Projection Operator
- Extended Operators of Relational Algebra
- Union, Intersection, and Difference of Bags
- Relational Operations on Bags
- A Linear Notation for Algebraic Expressions
- Dependent and Independent Operations
- Renaming
- Combining Operations to Form Queries
- Natural Joins / Theta-Joins
- Selection / Cartesian Product
- Set Operations on Relations
- An Algebra of Relational Operations
- Relational Algebra
- Attribute Lists
- Semistructured Data Representation
- Object-Oriented Versus Object-Relational
- References
- Nested Relations
- What If There Is No Key
- Representing ODL Relationships
- Representing Other Type Constructors
- Representing Set-Valued Attributes
- Nonatomic Attributes in Classes
- From ODL Designs to Relational Designs
- Declaring Keys in ODL
- Subclasses in ODL / Multiple Inheritance in ODL
- Types in ODL
- Methods in ODL
- Multiplicity of Relationships
- Relationships in ODL / Inverse Relationships
- Attributes in ODL
- Introduction to ODL
- The Type System
- Decomposition into Fourth Normal Form
- Reasoning About Multivalued Dependencies
- Definition of Multivalued Dependencies
- Multivalued Dependencies
- Third Normal Form
- Boyce-Codd Normal Form
- Projecting Functional Dependencies
- Closing Sets of Functional Dependencies
- The Transitive Rule
- Why the Closure Algorithm Works
- Computing the Closure of Attributes
- Trivial Functional Dependencies
- The Splitting/Combining Rule
- Rules About Functional Dependencies
- Keys of Relations
- Using Null Values to Combine Relations - Comparison of Approaches
- An Object-Oriented Approach
- Converting Subclass Structures to Relations
- Handling Weak Entity Sets
- Combining Relations
- From E/R Relationships to Relations
- From Entity Sets to Relations
- Relation Instances
- Equivalent Representations of a Relation
- Tuples / Domains
- Attributes / Schemas
- The Relational Data Model
- Summary of The Entity-Relationship Data Model
- Weak Entity Set Notation
- Requirements for Weak Entity Sets
- Representing Keys in the E/R Model
- Keys in the E/R Model
- The Modeling of Constraints
- Picking the Right Kind of Element
- Design Principles
- Subclasses in the E/R Model
- Attributes on Relationships
- Instances of an E/R Diagram
- Elements of the E/R Model
- Relational Database Systems