Consider this fictional query:
Select pageID, givenName From phy.Contacts Where pageID In ( Select pageID From ( Select id From oth.Pages Where [Hidden] = 0) As [subquery])
Here there are two levels of subqueries here. The innermost level returns a set of
[id]values. The next level up is asking the innermost level for a set of
[pageID]values. Alas, the innermost query does not contain a
However, this query executes anyway, without any errors, and appears to return decent results. This surprised me: the middle-level query is looking for a column that doesn't exist. I would have expected an error message in this case, not a result set.
This behavior is explained in a tiny "Caution" box in MSDN:
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.
So apparently, the middle-level query is pulling the pageID column from the OUTER query instead. Fascinating.