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. 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 the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.