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.
| FROM | WHERE |