Null Fun

Question

Null Fun

irobertson

Hall of Fame

Points: 3695
More actions
August 4, 2010 at 6:20 am

#387499

Hi,
More of a gotcha than a question, although I'm curious as to what is happening in the situation below:
declare @t1 table(
Key_Col float null
)
declare @t2 table(
Key_Col float null
)
insert @t1 values (1),(2)
insert @t2 values (1),(3),(null)
select * from @t1 where Key_Col not in (select Key_Col from @t2)
/*
Key_Col
----------------------
(0 row(s) affected)
*/
select * from @t1 where Key_Col not in (select Key_Col from @t2 where Key_Col is not null)
/*
Key_Col
----------------------
2
(1 row(s) affected)
*/
tl;dr: nulls in subqueried columns will cause you to incorrectly return no results unless you specifically exclude them...
Cheers, Iain

Viewing 10 posts - 1 through 9 (of 9 total)

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

The behavior of your select statements using "not in" is well expected due to "undeterministic" nature of null value comparison.

That is why is not good idea to use it for what you're are trying to do.

The right options whould be:

-- My preferred way:

select t1.* from @t1 t1

left join @t2 t2 on t2.Key_Col = t1.Key_Col

where t2.Key_Col is null

-- OR

select t1.*

from @t1 t1

where not exists( select 1 from @t2 t2 where t2.Key_Col = t1.Key_Col)

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

irobertson Hall of Fame Points: 3695 More actions · Answer 2

Agreed on all counts. Interesting behaviour though.

Lamprey13 SSCertifiable Points: 6210 More actions · Answer 3

Here are a coupel of links that might help:

http://sqlinthewild.co.za/index.php/2009/08/17/exists-vs-in/[/url]

http://sqlinthewild.co.za/index.php/2010/01/12/in-vs-inner-join/[/url]

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 4

irobertson (8/4/2010)
tl;dr: nulls in subqueried columns will cause you to incorrectly return no results unless you specifically exclude them...
Cheers, Iain

Nulls cannot be compared to anything, not even other Nulls. NULL = NULL returns false. NOT IN is like saying NULL <> NULL and even that doesn't work...

SELECT 1 WHERE NULL <> NULL

SELECT 2 WHERE NULL <> 1

SELECT 3 WHERE NULL <> 'A'

Just to be clear for other folks that may read this, it wasn't the NULLs that caused the query to operate incorrectly.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Cadavre SSC-Forever Points: 41690 More actions · Answer 5

Jeff Moden (8/5/2010)
Nulls cannot be compared to anything, not even other Nulls. NULL = NULL returns false. NOT IN is like saying NULL <> NULL and even that doesn't work...

Wow. Not having been involved with SQL until about 6 months ago, this has suprised me. I didn't actually believe you until I ran this: -

SELECT CASE

WHEN NULL = NULL THEN 'TRUE'

ELSE 'FALSE'

END,

CASE

WHEN NULL <> NULL THEN 'TRUE'

ELSE 'FALSE'

END

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 6

Note that you can do this:

SELECT 1 WHERE NULL is NULL

Converting oxygen into carbon dioxide, since 1955.

Cadavre SSC-Forever Points: 41690 More actions · Answer 7

Steve Cullen (8/5/2010)
Note that you can do this:
SELECT 1 WHERE NULL is NULL

Indeed. Or even

SELECT CASE

WHEN NULL IS NULL THEN 'TRUE'

ELSE 'FALSE'

END

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 8

skcadavre (8/5/2010)
Jeff Moden (8/5/2010)
Nulls cannot be compared to anything, not even other Nulls. NULL = NULL returns false. NOT IN is like saying NULL <> NULL and even that doesn't work...
Wow. Not having been involved with SQL until about 6 months ago, this has suprised me. I didn't actually believe you until I ran this: -
SELECT CASE
WHEN NULL = NULL THEN 'TRUE'
ELSE 'FALSE'
END,
CASE
WHEN NULL <> NULL THEN 'TRUE'
ELSE 'FALSE'
END

I actually wish more people would take on that attitude. It helps prevent myths, old wives tales, bum dope, bad information, and code assassinations from occurring. Like I frequently say at work, "One good test is worth a thousand expert opinions."

Good demonstrative test. Thanks for posting it. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 9

skcadavre (8/5/2010)
Steve Cullen (8/5/2010)
Note that you can do this:
SELECT 1 WHERE NULL is NULL
Indeed. Or even
SELECT CASE
WHEN NULL IS NULL THEN 'TRUE'
ELSE 'FALSE'
END

True enough in both cases. The important part to understand here is that you aren't actually comparing two values of NULL. You are testing one value to see if it has the "null attribute". A lot of people don't understand the semantical difference there, though.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)