Using Not In - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Using Not In

Author

Topic

josh2009
Yak Posting Veteran

81 Posts

Posted - 2010-06-08 : 17:00:11

Hi,

I am trying to use not in for a subquery but I can't seem to get it to work for me today. I have used it several times in the past with no problems but somehow it doesn't work for me with the fields I'm using today. Here is my query -

select referring_md from event_cath where referring_md is not null --1768
 and referring_md in (select name from dd_physician_lookup) --1480

As you can see, if I run it with an in, out of 1768 rows in event_cath table where referring_MD is filled in, there are 1480 row matches in dd_physician_lookup. Given that, I expect to get 288 rows which can not be found in dd_physicina_lookup table. But, now If I use not in, I get 0 rows using the ff query -

select referring_md from event_cath
where referring_md is not null 	and referring_md not in (select name from dd_physician_lookup)

Both fields are varchar(40). Any help will be greatly appreciated.

However, if I use this query -

select referring_md, name
from event_cath a
left outer join dd_physician_lookup b
	on referring_md = b.name
where referring_md is not null
	and name is null

I get all 288 rows I am looking for. So I just dont understand why the not in is not working for me.

Thanks

Kristen
Test

22859 Posts

Posted - 2010-06-08 : 17:59:04

Does

select name from dd_physician_lookup
WHERE name IS NULL

return anything?

josh2009
Yak Posting Veteran

81 Posts

Posted - 2010-06-11 : 09:48:32

Hi

Thanks for the reply and sorry for my late reply. I tried running your query and it returns 4 rows for me. Like I said I get by with using the joins but for some reason the not in just does not work for me on this query. I'm not sure if it has anything to do with the column defined by the vendor, our application is a purchased system, using name as the column name of dd_physician_lookup is a reserved word in SQL Server. If you have any other thoughts I would be more than happy to know about it. Thanks again.

Kristen
Test

22859 Posts

Posted - 2010-06-11 : 12:39:32

That means there are NULLs in your

and referring_md in (select name from dd_physician_lookup)

"IN" list, and that will muck up how it behaves. Changing that to

and referring_md in (select name from dd_physician_lookup WHERE name IS NOT NULL)

may be all you need to do (and change IN to NOT IN if that is what you want)

josh2009
Yak Posting Veteran

81 Posts

Posted - 2010-06-11 : 13:10:40

I see. Adding the where clause to check for nulls in my subquery worked perfect. Thanks a lot. I didn't realize that working with subqueries you still needed to check for nulls as well - lesson learned then. Thanks again for your help.

Kristen
Test

22859 Posts

Posted - 2010-06-11 : 14:04:14

Its not the sub query, per se, its the fact that NOT IN will NOT return true if the list contains NULLs, even if the list satisfies the query in other regards.

All these return zero rows


SELECT 'True'
WHERE 1 NOT IN (1, 2, NULL)

SELECT 'True'
WHERE 1 NOT IN (3, 2, NULL)

SELECT 'True'
WHERE 1 IN (3, 2, NULL)

but this DOES return a row


SELECT 'True'
WHERE 1 IN (1, 2, NULL)

josh2009
Yak Posting Veteran

81 Posts

Posted - 2010-06-11 : 15:15:43

Gotcha. Good stuff, Kristen. Thanks a bunch.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources