Export (0) Print
Expand All

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 AdventureWorks2008R2;
GO
SELECT Name
FROM Sales.Store
WHERE BusinessEntityID 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 AdventureWorks2008R2;
GO
SELECT Name
FROM Sales.Store
WHERE Sales.Store.BusinessEntityID 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.

Caution noteCaution

If a column is referenced in a subquery that does not exist in the table referenced by the subquery's FROM clause, but exists in a table referenced by the outer query's FROM clause, the query executes without error. SQL Server implicitly qualifies the column in the subquery with the table name in the outer query.

Community Additions

ADD
Show:
© 2014 Microsoft