SQL and Relational Theory, 2nd Edition

EXPLICIT TABLES

An explicit table in SQL is an expression of the form TABLE T, where T is the name of a base table or view or an “introduced name” (see the discussion of WITH in Chapter 6). It’s logically equivalent to the following:

     ( SELECT * FROM T )

Here’s a fairly complicated example that makes use of explicit tables (“Get all parts—but if the city is London, show it as Oslo and show the weight as double”):

     WITH T1 AS ( SELECT PNO , PNAME , COLOR , WEIGHT , CITY
                  FROM   P
                  WHERE  CITY = 'London' ) ,
          T2 AS ( SELECT PNO , PNAME , COLOR , WEIGHT , CITY ,
                         2 * WEIGHT AS NEW_WEIGHT , 'Oslo' AS NEW_CITY
                  FROM T1 ) ,
          T3 AS ( SELECT PNO , PNAME , COLOR ,
                         NEW_WEIGHT AS WEIGHT , NEW_CITY AS CITY
                  FROM   T2 ) ,
          T4 AS ( TABLE P EXCEPT CORRESPONDING TABLE T1 )

     TABLE T4 UNION CORRESPONDING TABLE T3