Issue using the "in Sub query"

Question

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.

Answer 1

0

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.

answered Mar 29 '10 at 08:35 AM

Grant Fritchey ♦♦
64.9k ● 13 ● 20 ● 66

+1 for being 47 seconds faster :)

Mar 29 '10 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 '10 at 10:24 AM Blackhawk-17

I see, thanks Grant.

Mar 29 '10 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 '10 at 10:55 AM Grant Fritchey ♦♦

(comments are locked)

Answer 2

0

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)

answered Mar 29 '10 at 08:36 AM

Matt Whitfield ♦♦
29.2k ● 56 ● 63 ● 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 '10 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 '10 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 '10 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 '10 at 10:42 AM Matt Whitfield ♦♦

@Matt congrats on 9k. Well done. Only a little while to the big fat round number.

Mar 29 '10 at 01:53 PM Grant Fritchey ♦♦

(comments are locked)

Answer 3

0

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
                 ) 
;

answered Mar 29 '10 at 10:53 PM

Rob Farley
5.7k ● 14 ● 17 ● 19

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 '10 at 11:41 AM Will 1

(comments are locked)

Issue using the "in Sub query"

Follow this question

Related Questions