Strange behaviour when using ''in''

Question

Post reply

Strange behaviour when using ''in''

jonwolds

Right there with Babe

Points: 718
More actions
March 15, 2007 at 5:49 am

#172462

Hi,
Please see the example below. I would have expected this to error because I am referencing the wrong column name in the sub query. Can anyone explain why this does not error and why you would ever want to do it?
Thanks
Jon
--drop table #a
--drop table #b
create table #a
(
Table_a_Column varchar(10)
)
create table #b
(
Table_b_Column varchar(10)
)
select
'x'
from
#a
where
Table_a_Column not in (select
Table_a_Column
from
#b)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

That's the way correlated subqueries work. This doesn't fail because this query has to be valid :

Select * from dbo.SysColumns C where exists (SELECT * from dbo.SysObjects O where O.id = C.id)

The O.id has to be allowed for this query to be valid. That's why it also have to be allowed in your context. It doesn't seem to make much sens because you are not correlating anything between the 2 tables but that is still a valid expression.

jonwolds Right there with Babe Points: 718 More actions · Answer 2

Hi,

I thought that might be the reason. I knew (and understood) why it worked when using 'exists' but I thought it should fail when using 'in'.

Thanks

Jon

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Let's just say that this opens up a whole new whelm of possibilities :

SELECT * FROM dbo.SysColumns C1 WHERE Colid IN (SELECT MIN(C2.Colid) FROM dbo.SysColumns C2 where C1.id = C2.id)

Still correlated, but no exists this time.

dbajunior SSCrazy Points: 2307 More actions · Answer 4

Since we are talking about stange behavior regarding 'in', learned this the hard way about 'not in':

select count(*) from table1 where fieldA not in (select fieldA from table2)

can yield incorrect (zero) results when in fact, you know that there should be a count > 0. Found that the following corrected this:

select count(*) from table1 where fieldA is not null and fieldA not in (select fieldA from table2 where fieldA is not null)

peterhe SSChampion Points: 11469 More actions · Answer 5

Check out this:

http://p-sql.spaces.live.com/?_c11_blogpart_blogpart=blogview&_c=blogpart&partqs=amonth%3d10%26ayear%3d2006

If the result of the subquery contains NULL value and ANSI NULL is set ON, the query result of IN + NOT IN does not equal to the statement without IN/NOT IN expression.