Select statement returns puzzling results

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Select statement returns puzzling results

Author

Topic

nelsont
Starting Member

25 Posts

Posted - 2012-11-21 : 10:21:19

Two tables, t1 and t2.

t1 has 400 rows, t2 has 1000.

Select * from t1 where ID in (select ID from t2)
This returns 200 rows.

Select * from t1 where ID NOT in (select ID from t2)
This returns 0 rows.

Why don't I see the 200 rows not returned by the first select statement when I run the second select statement?

The ID field in both tables is varchar(20).

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-11-21 : 10:29:12

Could the 200 IDs in t1 be NULL ?

Be One with the Optimizer
TG

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-21 : 10:29:21

Probably because there are nulls in ID column in t2. Change your second query to:

Select * from t1 where ID NOT in (select t2.ID from t2 WHERE t2.ID IS NOT NULL)
OR t1.id IS NULL

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-21 : 10:30:42

Probably because of nulls and how SQL interprets them. To make your query even simpler,
try
Select * from t1 where 'c' in ('a','b',null)
Select * from t1 where 'c' not in ('a','b',null)

Neither one produces a result set.

It is always safer to use EXISTS and NOT EXISTS

SELECT *
FROM t1
WHERE EXISTS (select * from t2 where t1.ID = t2.ID)

SELECT *
FROM t1
WHERE NOT EXISTS (select * from t2 where t1.ID = t2.ID)

Jim

Everyday I learn something that somebody else already knew

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-11-21 : 10:31:48

I usually use this:

select t1.*
from t1
left outer join t2 on t2.id = t1.id
where t2.id is null

Be One with the Optimizer
TG

nelsont
Starting Member

25 Posts

Posted - 2012-11-21 : 10:38:00

That was it - nulls in T2. Thanks for the EXISTS / NOT EXISTS tip Jim.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-21 : 11:10:22

You're welcome.

Jim

Everyday I learn something that somebody else already knew