Qualifying Column Names in Subqueries

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

USE AdventureWorks;
GO
SELECT Name
FROM Sales.Store
WHERE CustomerID NOT IN
    (SELECT CustomerID
     FROM Sales.Customer
     WHERE TerritoryID = 5)

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. If a column does not exist in the table referenced in the FROM clause of a subquery, it is implicitly qualified by the table referenced in the FROM clause of the outer query.

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

USE AdventureWorks;
GO
SELECT Name
FROM Sales.Store
WHERE Sales.Store.CustomerID NOT IN
    (SELECT Sales.Customer.CustomerID
     FROM Sales.Customer
     WHERE TerritoryID = 5)

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.