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.

Hmmm Good question. Especially when
its considered that I hate people blogging
abt what breakfast they had and what dress they
wear.

I want to blog because, I believe in learning something
new everyday and I always try to do. As I am currently
focusing on The RDBMS side, I'll try to post my SQL Server
experiments as and when possible, not necessarily on a
daly basis.