Tuple Variables

Tuple Variables

Disambiguating attributes by prefixing the relation name works as long as the query involves combining many different relations.  On the other hand, sometimes we need to ask a query that involves two or more tuples from the same relation,

Tuple Variables and Relation Names
We may list a relation R as many times as we need to in the FROM clause, but we need a way to refer to each occurrence of R. SQL allows us to define, for each occurrence of R in the FROM clause, an "alias" which we shall refer to as a tuple variable. Each use of R in the FROM clause is followed by the (optional) keyword AS and the name of the tuple variable; we shall usually omit the AS in this context.

In the SELECT and WHERE clauses, we can disambiguate attributes of R by preceding them by the appropriate tuple variable and a dot. In this way, the tuple variable serves as another name for relation R and can be used in its place when we wish.

Example 1 : While "Disambiguating Attributes" Example 1, asked for a star and an executive sharing an address, we might similarly want to know about two stars who share an address. The query is basically the same, but now we must think of two tuples chosen from relation MovieStar, rather than tuples from each of MovieStar and MovieExec. Using tuple variables as aliases for two uses of MovieStar, we can write the query as

We see in the FROM clause the declaration of two tuple variables, Star1 and Star2; each is an alias for relation MovieStar. The tuple variables are used in the SELECT clause to refer to the name components of the two tuples. These aliases are also used in the WHERE clause to say that the two MovieStar tuples represented by Star1 and Star2 have the same value in their address components.

The second condition in the WHERE clause, Star1.name < Star2.name, says that the name of the first star precedes the name of the second star alphabetically. If this condition were omitted, then tuple variables Star1 and Star2 could both refer to the same tuple. We would find that the two tuple variables referred to tuples whose address components are equal, of course, and thus produce each star name paired with itself. The second condition also forces us to produce each pair of stars with a common address only once, in alphabetical order. If we used <> (not-equal) as the comparison operator, then we would produce pairs of married stars twice, like

Tuple Variables