SQL and Relational Theory, 2nd Edition

COLUMN NAMING IN SQL

In the relational model, (a) every attribute of every relation has a name (i.e., anonymous attributes are prohibited), and (b) such names are unique within the relevant relation (i.e., duplicate attribute names are prohibited). In SQL, analogous rules are enforced sometimes, but not always. To be specific, they’re enforced for the tables that happen to be the current values of table variables—defined via CREATE TABLE or CREATE VIEW—but not for the tables that result from evaluation of some table expression.^[48] Strong recommendation: Use AS clauses whenever necessary (and possible) to give proper column names to columns that otherwise (a) wouldn’t have a name at all or (b) would have a name that wasn’t unique. Here are some examples:

     SELECT DISTINCT SNAME , 'Supplier' AS TAG
     FROM   S
     SELECT DISTINCT SNAME , 2 * STATUS AS DOUBLE_STATUS
     FROM   S
     SELECT MAX ( WEIGHT ) AS MBW
     FROM   P
     WHERE  COLOR = 'Blue'
     CREATE VIEW SDS AS
          ( SELECT DISTINCT SNAME , 2 * STATUS AS DOUBLE_STATUS
            FROM   S ) ;
     SELECT DISTINCT S.CITY AS SCITY , P.CITY AS PCITY
     FROM   S , SP , P
     WHERE  S.SNO = SP.SNO
     AND    SP.PNO = P.PNO
     SELECT TEMP.*
     FROM ( SELECT * FROM S JOIN P ON S.CITY > P.CITY ) AS TEMP
          ( SNO , SNAME , STATUS , SCITY ,
            PNO , PNAME , COLOR , WEIGHT , PCITY )

Of course, the foregoing recommendation can safely be ignored if there’s no subsequent need to reference the otherwise anonymous or nonuniquely named columns. For example, the third of the foregoing examples could safely be abbreviated in ...