SQL and Relational Theory, 2nd Edition

FORMULATING EXPRESSIONS ONE STEP AT A TIME

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