Qualifying Column Names in Subqueries

In the following example, the pub_id column in the WHERE clause of the outer query is implicitly qualified by the table name in the outer query's FROM clause, publishers. The reference to pub_id in the select list of the subquery is qualified by the subquery's FROM clause, that is, by the titles table.

USE pubs
SELECT pub_name
FROM publishers
WHERE pub_id NOT IN
   (SELECT pub_id
   FROM titles
   WHERE type = 'business'

The general rule is that column names in a statement are implicitly qualified by the table referenced in the FROM clause at the same level.

Here's what the query looks like with these implicit assumptions specified:

USE pubs
SELECT pub_name
FROM publishers
WHERE publishers.pub_id NOT IN
   (SELECT titles.pub_id
   FROM titles
   WHERE type = 'business')

It is never wrong to state the table name explicitly, and it is always possible to override implicit assumptions about table names with explicit qualifications.

See Also

FROM

WHERE