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 some circumstances (in a WHERE or HAVING clause, perhaps) to just:

     SELECT MAX ( WEIGHT )
     FROM   P
     WHERE  COLOR = 'Blue'

Perhaps more important, note that the recommendation unfortunately can’t be followed at all in the case of tables specified by means of VALUES expressions. However, workarounds are possible. For example, the following is legal:

     SELECT TEMP.*
     FROM ( VALUES ( 'S1' , 'Smith' , 20 , 'London' ) ,
                   ( 'S2' , 'Jones' , 10 , 'Paris'  ) ,
                   ( 'S3' , 'Blake' , 30 , 'Paris'  ) ,
                   ( 'S4' , 'Clark' , 20 , 'London' ) ,
                   ( 'S5' , 'Adams' , 30 , 'Athens' ) )
            AS TEMP ( SNO , SNAME , STATUS , CITY )

Explanation: I’ve enclosed the VALUES expression in parentheses (thereby making it a subquery), attached an AS clause, and specified column names as well as a “correlation name” within that AS clause (see Chapter 12).

Important note: The operators of the relational algebra rely on proper attribute naming in a variety of ways. For example, as we’ll see in Chapter 6, the relational UNION operator requires its operands to have the same heading (and hence the same attribute names), and the result then has the same heading as well. One advantage of this scheme is precisely that it avoids the complexities caused (in SQL) by reliance on ordinal position! In order to use SQL relationally, therefore, you should apply the same discipline to the SQL analogs of those relational operators. Strong recommendation: As a prerequisite to enforcing such a discipline, if two columns in SQL represent “the same kind of information,” give them the same name wherever possible. (That’s why, for example, the two supplier number columns in our running example, the suppliers-and-parts database, are both called SNO and not, say, SNO in one table and SNR in the other.) Conversely, if two columns represent different kinds of information, it’s usually a good idea to give them different names.

The only case where it’s impossible to follow the foregoing recommendation is when two columns in the same table both represent the same kind of information. For example, consider an SQL table EMP with columns representing employee number and manager number, respectively, where manager number is itself another employee number. These two columns will have to have different names, say ENO and MNO, respectively. As a consequence, some column renaming will sometimes have to be done, as in this example (note the specification “ENO AS MNO” in the third line):

     ( SELECT ENO , MNO FROM EMP ) AS TEMP1
       NATURAL JOIN
     ( SELECT ENO AS MNO , ... FROM EMP ) AS TEMP2
     /* where "..." is EMP columns other than ENO and MNO */

Such renaming will also have to be done, if you want to use SQL relationally, if columns simply haven’t been named appropriately in the first place (e.g., if you’re confronted with a database that’s been defined by somebody else—doubtless a common state of affairs in practice). A strategy you might want to consider in such circumstances is the following:

Unfortunately, it’s impossible to ignore the fact 100 percent that columns have an ordinal position in SQL. (Of course, it’s precisely because of this fact that SQL is able to get away with its anonymous columns and duplicate column names.) Note in particular that columns still have an ordinal position in SQL even when they don’t need to (i.e., when they’re all properly named anyway); this observation applies to columns in base tables and views in particular. Strong recommendation: Never write SQL code that relies on such ordinal positioning. Examples of where SQL attaches significance to such positioning include (but probably aren’t limited to):