Consider the following Tutorial D expression (the query is “Get pairs of supplier numbers such that the suppliers concerned are colocated—i.e., are in the same city”):
( ( ( S RENAME { SNO AS SA } ) { SA , CITY } JOIN
( S RENAME { SNO AS SB } ) { SB , CITY } )
WHERE SA < SB ) { SA , SB }The result has two attributes, called SA and SB (it would have been sufficient to do just one attribute renaming; I did two for symmetry). The purpose of the condition SA < SB is twofold:[85]
It eliminates pairs of supplier numbers of the form (a,a).
It guarantees that the pairs (a,b) and (b,a) won’t both appear.
Be that as it may, I now show another formulation of the query in order to show how Tutorial D’s WITH construct can be used to simplify the business of formulating what might otherwise be rather complicated expressions:
WITH ( R1 := ( S RENAME { SNO AS SA } ) { SA , CITY } ,
R2 := ( S RENAME { SNO AS SB } ) { SB , CITY } ,
R3 := R1 JOIN R2 ,
R4 := R3 WHERE SA < SB ) :
R4 { SA, SB }As the example suggests, a WITH clause in Tutorial D consists of the keyword WITH followed by a parenthesized commalist of specifications of the form name := expression, the whole commalist then being followed by a colon. For each of those “name := expression” specifications, the expression is evaluated and the result effectively assigned to a temporary variable with the specified name. Also, those “name := expression” specifications are evaluated in sequence as written; as a consequence, any given specification in the commalist is allowed to refer to names introduced in specifications earlier in that same commalist.
Tutorial D allows WITH clauses on statements as well as expressions. For example:
WITH ( X := RELATION { TUPLE { SNO 'S5' , PNO 'P6' , QTY 250 } } ) :
SP := SP UNION X ;SQL too supports a WITH construct, with these differences:
SQL uses the keyword AS in place of Tutorial D’s assignment symbol (“:=”).
SQL doesn’t use the enclosing parentheses or colon separator.
WITH in Tutorial D can be used at any level of nesting. By contrast, WITH in SQL can be used only at the outermost level.
WITH in Tutorial D can be used in connection with expressions of any kind.[86] By contrast, WITH in SQL can be used only in connection with table expressions specifically.
As already noted, Tutorial D allows WITH clauses on statements as well as expressions. SQL doesn’t.
Also, in SQL, the name portion of a “name AS expression” specification can optionally be followed by a parenthesized column name commalist (much as in a range variable definition—see Chapter 12). However, it shouldn’t be necessary to exercise this option very often if other recommendations in this book are followed.
Here’s an SQL version of the example:
WITH T1 AS ( SELECT SNO AS SA , CITY
FROM S ) ,
T2 AS ( SELECT SNO AS SB , CITY
FROM S ) ,
T3 AS ( SELECT *
FROM T1 NATURAL JOIN T2 ) ,
T4 AS ( SELECT *
FROM T3
WHERE SA < SB )
SELECT SA , SB
FROM T4In closing this section, I should make it clear that WITH isn’t really an operator of the relational algebra as such—it’s just a syntactic device to help with the formulation of complicated expressions (especially ones involving common subexpressions). I’ll be making extensive use of it in the pages ahead.
[85] Note, incidentally, that the condition SA < SB wouldn’t be legal if supplier numbers were of some user defined type (SNO, say) and the operator “<” hadn’t been defined in connection with that type.
[86] Except that the expression in question mustn’t be such that it relies on (syntactic) context for its evaluation; in other words, it must be what’s called a closed expression. For example, “S WHERE STATUS = 20” is closed, but “STATUS = 20” isn’t. Of course, a similar rule applies in SQL also.