Strange behavior in query results - "not in" vs "left join"

Question

Strange behavior in query results - "not in" vs "left join"

K Currie

SSCommitted

Points: 1662
More actions
April 15, 2012 at 8:48 pm

#256409

I'm seeing some strange behavior on a client's SQL 2005 server and wondered if anyone else has ever experienced anything like this.
The following query returns no results when run, however, in the past it always returned results:
select * from tablea
where field1 not in (select field1 from tableb)
If I rewrite the query using a "left join" instead of "not in" I get results:
select * from tablea
left join tableb on
tablea.field1 = tableb.field1
where tableb.field1 is null
I've been rewriting queries like this on other client servers because I've seen faster performance as a result, however, it makes completely no sense that the top query would no longer return any results on this client's server. (I only rewrote in this case because the first query stopped returning results.)
Has anyone else seen this before? I'm thinking that the two queries take separate execution plans and that perhaps I have some database corruption and the first query is using something that the second query is not. Is there any other explanation for this?
Server Stats:
Enterprise Edition (64-bit)
SP3
9.00.4053.00
Microsoft SQL Server 2005 - 9.00.4053.00 (X64) May 26 2009 14:13:01 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.0 (Build 6002: Service Pack 2)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

K Currie (4/15/2012)
I'm seeing some strange behavior on a client's SQL 2005 server and wondered if anyone else has ever experienced anything like this.
The following query returns no results when run, however, in the past it always returned results:
select * from tablea
where field1 not in (select field1 from tableb)
If I rewrite the query using a "left join" instead of "not in" I get results:
select * from tablea
left join tableb on
tablea.field1 = tableb.field1
where tableb.field1 is null
I've been rewriting queries like this on other client servers because I've seen faster performance as a result, however, it makes completely no sense that the top query would no longer return any results on this client's server. (I only rewrote in this case because the first query stopped returning results.)
Has anyone else seen this before? I'm thinking that the two queries take separate execution plans and that perhaps I have some database corruption and the first query is using something that the second query is not. Is there any other explanation for this?
Server Stats:
Enterprise Edition (64-bit)
SP3
9.00.4053.00
Microsoft SQL Server 2005 - 9.00.4053.00 (X64) May 26 2009 14:13:01 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.0 (Build 6002: Service Pack 2)

If you think about it you will realize why, you answer your own question using the left outer join with the where clause tableb.field1 is null. Your aren't getting any results from the NOT IN due to null values in field1 on table2.

adrian.facio SSCrazy Points: 2406 More actions · Answer 2

Hi,

I think the left join query is correct. Maybe the subquery (select field1 from tableb) returns some nulls, that can turned out your whole not in expression in UNKOWN instead of true or false, giving no result set.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

To explain further, when testing for null values you need to use IS NULL or IS NOT NULL. Using AColumn = NULL or AColumn <> NULL will not return the result you are expecting. The same occurs when using IN or NOT IN as these are essentially the later checks.

Rich Mechaber SSChampion Points: 11130 More actions · Answer 4

See http://www.simple-talk.com/content/print.aspx?article=783, Item #1, "NULLS and the NOT IN Predicate".

Rich

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

See http://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

K Currie SSCommitted Points: 1662 More actions · Answer 6

Thanks!

I did a quick check and it turns out I did have a row with a null value in field1 for tableb. Once I deleted this record (since it was null it wasn't necessary) my records all displayed in my query once again.

I think I'll make it so this field can't have null values in the future.