TOP 1 WITH TIES

Try the following query.

USE Pubs
GO
SELECT * FROM Authors
WHERE au_id IN
(SELECT au_id FROM Titles)

As we know there is no column called au_id
in the TITLES table, and the query returns
all the records in the Authors table, instead
of an invalid column name Error.

Amazed..? Actually I was amzed when I see
this behaviour first.

But this is the expected behaviour and thats
what makes the correlated subqueries work.

When SQL Server fails to find the au_id in the
TITLES table, it looks for the au_id column in
the Authors table.

So if our statement was
IN (SELECT T.au_id FROM Titles T)
the query will throw an Error.

Joe CELKO referred this behaviour as "fancy scoping"
You can read his original posting here.