Monday, June 28, 2004

 

Fancy Scoping

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.




Comments:
SELECT * FROM Authors
WHERE au_id IN
(SELECT au_id FROM Titles)

returns all records from Authors!! True if "SELECT au_id FROM Titles" returns at least 1 record. However, if "SELECT au_id FROM Titles" returns no records, then u will not get any o/p result set from the above query.
-------
RKM
 
RKM,
SELECT au_id FROM Titles will only throw a syntax error because there is no column named au_id.

I suggest you read the post again and try it for yourself.
 
This wont work if you write your query like this

SELECT * FROM Authors
WHERE au_id IN
(SELECT Titles.au_id FROM Titles)

Thanks
Baiju
 
Free WordPress Themes from templatedesk.com- Site Templates and CMS Themes.
 
Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?