December 4, 2008 at 8:54 am
Now I am not a big fan of using IN statements. I prefer to use a join whenever possible. However, I came across something interesting the other day that I cannot explain but seems to reinforce my not liking IN.
Run the following:
CREATE TABLE a (a_id INT)
CREATE TABLE b (b_id INT)
GO
INSERT dbo.a VALUES(1)
INSERT dbo.a VALUES(2)
INSERT dbo.a VALUES(3)
INSERT dbo.a VALUES(4)
INSERT dbo.a VALUES(5)
INSERT dbo.b VALUES(3)
INSERT dbo.b VALUES(4)
INSERT dbo.b VALUES(5)
INSERT dbo.b VALUES(6)
INSERT dbo.b VALUES(7)
Now, if you run this query, it returns all records from table dbo.a. Now this is not only incorrect, but I received no error telling me that column a_id is not valid in table dbo.b. basically, it does not appear to be parsing the "IN" clause.
SELECT a_id FROM dbo.a WHERE a_id IN(SELECT a_id FROM dbo.b)
Now if I rewrite this as a join, keeping the invalid column name, I receive the error I would expect to see.
SELECT
a_id
FROM dbo.a INNER JOIN dbo.b ON a.a_id = b.a_id
And, if I use the proper column name in the join, I return the data I should see.
SELECT
a_id
FROM dbo.a INNER JOIN dbo.b ON a.a_id = b.b_id
So, I presume this is a known issue and I simply have not seen it before given my avoidance of using "IN" statements. But, why is this happening. Why is it now parsing the IN statement? This could have pretty serious implications I would think.
December 4, 2008 at 9:01 am
SELECT a_id FROM dbo.a WHERE a_id IN (SELECT a_id FROM dbo.b)
In the above statement, the table dbo.a and thus the column a_id is in scope and accessible in the sub-query. This is how Correlated Subqueries work.
December 4, 2008 at 2:57 pm
SELECT a_id FROM dbo.a WHERE a_id IN (SELECT a_id FROM dbo.b)
This is yet another reason why it is a good practice to always qualify your column names to be specific about the table in which you expect to find them.
The following would have thrown an error like you expected:
select a.a_id from dbo.a where a.a_id in (select b.a_id from dbo.b)
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply