SQL Server Subquery Quirk

Here's an interesting quirk of SQL Server 2005 that bit me today.

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 [pageID] column.

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.