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 ...
Get SQL and Relational Theory, 2nd Edition now with O’Reilly online learning.
O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.