Issue using the "in Sub query"

Question

question

Will 1 asked Mar 29, '10

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.

t-sql query sub-query

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-03-29T08:35:40Z

Grant Fritchey answered Mar 29, '10

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.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2010-03-29T08:36:27Z

Matt Whitfield answered Mar 29, '10

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)

6

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey ♦♦ commented · Mar 29, 2010 at 09:06 AM

+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.

1 ·

Fatherjack ♦♦ commented · Mar 29, 2010 at 09:11 AM

+1 - to you and Grant - I would have gone for the LEFT JOIN option too. Interesting why the NOT IN is quicker ...

0 ·

Will 1 commented · Mar 29, 2010 at 10:35 AM

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.

0 ·

Matt Whitfield ♦♦ commented · Mar 29, 2010 at 10:42 AM

@Will - do you want to post up some sample data / DDL? We can probably then help further.

0 ·

Grant Fritchey ♦♦ commented · Mar 29, 2010 at 01:53 PM

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

0 ·

Show more comments

Answer 3 · 2010-03-29T22:53:00Z

Rob Farley answered Mar 29, '10

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

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Will 1 commented · Mar 31, 2010 at 11:41 AM

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

0 ·

question