Simple SQL Statement giving me problems

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 2005 Forums

Transact-SQL (2005)

Simple SQL Statement giving me problems

Author

Topic

acesover
Starting Member

15 Posts

Posted - 2008-09-18 : 00:53:33

I'm getting an empty recordset back from this query

Select * from TableA where CommonFldID Not In (Select CommonFldID from TableB)

If I run the subquery on TableB I get back a number of records (about 16k). TableA has about 30k records. The IDs in TableB are a subset of those found in TableA. I don't know why this query doesn't return the difference. Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-18 : 01:50:10

is CommonFldID a primary key in both of the tables ?

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-18 : 01:52:30

check ig you're using correct tables. Also see if tables exists for different schemas and you're using one under correct schema.

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-09-18 : 08:03:00

Does your subquery return NULLs? Note that NOT IN always returns false if there's a NULL in the list.

Use NOT EXISTS instead:

select *
from TableA
where not exists(
    select CommonFldID
    from TableB
    where TableB.CommonFldID = TableA.CommonFldID
)

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-09-18 : 08:27:17

To be more precise: NOT IN returns NULL, which the WHERE-clause evaluates to false.

if 4 not in(5, NULL)
select 'There is definitely no four.'
else
select 'Uhh, not sure. Leave me alone.'

acesover
Starting Member

15 Posts

Posted - 2008-09-18 : 17:20:13

Thanks, that did the trick. I simply modified my sub query to exclude any nulls and it worked fine. Thanks again for the help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources