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 name commalist in JOIN ... USING
The column name commalist, if specified (and it should be—see Chapter 5), on INSERT
The left side of a SET assignment on UPDATE
It might help to note that most of the contexts listed above are ones in which no range variable, as such, is available for dot qualification use anyway. The point is, however, that an unsuspecting user might expect to be able to use table names as qualifiers in these contexts, on the grounds—I suppose—that SQL often uses table names as if they were range variable names anyway, as explained in the next section.