x

Issue using the "in Sub query"

You got two tables, both are lists of employees identified by a number, I want to find every one in [MyCompany] that isn't in the [OtherCompany] table. Why does this work:-

select MyCompany.staffno  
from MyCompany  
where staffno not in (select OtherCompany.EmpNumber  
    from OtherCompany 
    where EmpNumber = MyCompany.staffno)  

But this doesn't:-

select MyCompany.staffno 
from MyCompany 
where staffno not in (select OtherCompany.EmpNumber from OtherCompany)

The only difference is the 'where' clause in the subquery, however the second query returns zero rows indicating that all rows in [MyCompany] already exist in the [OtherCompany] table which is not the case.

more ▼

asked Mar 29, 2010 at 08:24 AM in Default

Will 1 gravatar image

Will 1
73 1 1 1

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

Basically what's going on is that the optimizer is turning your NOT IN query into a join and then looking for null values. When you don't supply a filtering mechanism it's attempting to match on NULL and you can't actually do a match on NULL, so no values are returned because nothing matches.

more ▼

answered Mar 29, 2010 at 08:35 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
101k 19 21 74

+1 for being 47 seconds faster :)
Mar 29, 2010 at 08:36 AM Matt Whitfield ♦♦
That is one of those little things that make you scratch your head while coding, but once you get that A-HA! moment... Keeps life interesting :)
Mar 29, 2010 at 10:24 AM Blackhawk-17
I see, thanks Grant.
Mar 29, 2010 at 10:40 AM Will 1
I should have mentioned it in my post, but take a look at the execution plans for the two queries. That will give you a lot of information as to what each is doing to resolve the problem you presented.
Mar 29, 2010 at 10:55 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

This is because one of the values from the query

select OtherCompany.EmpNumber from OtherCompany

Is returning NULL. When using IN clauses, NULL means 'match anything' - but it doesn't on a JOIN, this is why you're seeing the JOIN in your IN query return OK. This will probably work fine for you:

select MyCompany.staffno  from MyCompany  where staffno not in  (select EmpNumber  from OtherCompany  WHERE EmpNumber IS NOT NULL) 
more ▼

answered Mar 29, 2010 at 08:36 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

+1 for having a more complete answer. I still do these things using left join & looking for the NOT NULL, but in this case, the NOT IN is actually faster.
Mar 29, 2010 at 09:06 AM Grant Fritchey ♦♦
+1 - to you and Grant - I would have gone for the LEFT JOIN option too. Interesting why the NOT IN is quicker ...
Mar 29, 2010 at 09:11 AM Fatherjack ♦♦
Thanks Matt, checking for Null on the EmpNumber made no differene, the query still returned zero rows even though there are rows in the [MyCompany] table that are not in the [OtherComapny] table.
Mar 29, 2010 at 10:35 AM Will 1
@Will - do you want to post up some sample data / DDL? We can probably then help further.
Mar 29, 2010 at 10:42 AM Matt Whitfield ♦♦
@Matt congrats on 9k. Well done. Only a little while to the big fat round number.
Mar 29, 2010 at 01:53 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

Can I suggest you use NOT EXISTS instead of NOT IN, as this will handle NULLs much better.

select mc.staffno
from MyCompany as mc where NOT EXISTS (select * from OtherCompany as oc where oc.EmpNumber = mc.staffno ) ;
more ▼

answered Mar 29, 2010 at 10:53 PM

Rob Farley gravatar image

Rob Farley
5.7k 16 18 20

Hi Rob well yes, but I had tried that and it made no difference to the hadling of the null values. The key here was adding the 'where' clause (and therefore a refernce to the MyCompany outer table) to the sub-select. Don't ask me why though :-)
Mar 31, 2010 at 11:41 AM Will 1
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x986
x371
x46

asked: Mar 29, 2010 at 08:24 AM

Seen: 1324 times

Last Updated: Mar 29, 2010 at 08:27 AM