SQL and Relational Theory, 2nd Edition

NAME QUALIFICATION

Column names in SQL can usually be dot qualified by the name of the applicable range variable (see the next section). However, SQL allows that qualifier to be omitted in many situations, in which case an implicit qualifier is assumed by default. But:

• The SQL rules regarding implicit qualification aren’t always easy to understand. As a result, it isn’t always obvious what a particular unqualified name refers to.

• What’s unambiguous today might be ambiguous tomorrow (e.g., if new columns are added to an existing table).

• In Chapter 3 I recommended, strongly, that columns that represent the same kind of information be given the same name whenever possible. If that recommendation is followed, then unqualified names will often be ambiguous anyway, and dot qualification will therefore be required.

So a good general rule is: When in doubt, qualify. Unfortunately, however, there are certain contexts in which qualification isn’t allowed. The contexts in question are, loosely, ones in which the name serves as a reference to the column per se, rather than to the data contained in that column. Here’s a partial list of such contexts (note the last two in particular):

• A column definition within a base table definition

• A key or foreign key specification

• The column name commalist, if specified (but it shouldn’t be—see Chapter 8), in CREATE VIEW

• The column name commalist, if specified (but it usually shouldn’t be—see the next section), following the definition of a range variable

• The column ...