Conditions Involving Tuples

Conditions Involving Tuples

A tuple in SQL is represented by a parenthesized list of scalar values. Examples are (123, 'foo') and (name, address, networth). The first of these has constants as components; the second has attributes as components. Mixing of constants and attributes is permitted.

If a tuple t has the same number of components as a relation R, then it makes sense to compare t and R in expressions of the type listed in "Subqueries" under "Conditions Involving Relations". Examples are t IN R or t <> ANY R. The latter comparison means that there is some tuple in R other than t. Note that when comparing a tuple with members of a relation R, we must compare components using the assumed standard order for the attributes of R.

Finding the producers of Harrison Fords movies

Example 1 : In Figure 1 is an SQL query on the three relations

asking for all the producers of movies in which Harrison Ford stars. It consists of a "main" query, a query nested within that, and a third query nested within the second.

We should analyze any query with subqueries from the inside out. Thus, let us start with the innermost nested subquery: lines (7) through (9). This query examines the tuples of the relation StarsIn and finds all those tuples whose starName component is 'Harrison Ford'. The titles and years of those movies are returned by this subquery. Recall that title and year, not title alone, is the key for movies, so we need to produce tuples with both attributes to identify a movie uniquely. Thus, we would expect the value produced by lines (7) through (9) to look something like Figure 2.

Now, look at the middle subquery, lines (4) through (6). It searches the Movie relation for tuples whose title and year are in the relation suggested by Figure 2. For each tuple found, the producer's certificate number is returned, so the result of the middle subquery is the set of certificates of the producers of Harrison Ford's movies.

Finally, look at the "main" query of lines (1) through (3). It examines the tuples of the MovieExec relation to find those whose cert# component is one of the certificates in the set returned by the middle subquery. For each of these tuples, the name of the producer is returned, giving us the set of producers of Harrison Ford's movies, as desired.

Title-year pairs returned by inner subquery

Incidentally, the nested query of Figure 1 can, like many nested queries, be written as a single select-from-where expression with relations in the FROM clause for each of the relations mentioned in the main query or a subquery. The IN relationships are replaced by equalities in the WHERE clause. For example, the query of Figure 3 is essentially that of Figure 1. There is a difference regarding the way duplicate occurrences of a producer - e.g., George Lucas - are handled.

Fords producers without nested subqueries