SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Play with NULLIF


Play with NULLIF

Author
Message
Daniel Bowlin
Daniel Bowlin
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17362 Visits: 2629
Great question, thanks.

Hugo, thanks for the additional information and insight.
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)

Group: General Forum Members
Points: 145094 Visits: 13349
Great question. Learned two new things today:

* nullif
* how SQL converts an empty string to a numeric datatype.

Nice one!


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Cliff Jones
Cliff Jones
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6771 Visits: 3648
Thanks Hugo for the information, very useful.
Trey Staker
Trey Staker
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2252 Visits: 2788
Good question, Thanks. I also learned a lot from the discussion, thanks Hugo for the explanation.

---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
rjv_rnjn
rjv_rnjn
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1745 Visits: 431
Good question. Thanks Hugo for the detailed explanation.
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)

Group: General Forum Members
Points: 143755 Visits: 18651
Nice question - great explanation Hugo.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw
Learn Extended Events

Paul White
Paul White
SSC Guru
SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)

Group: General Forum Members
Points: 79392 Visits: 11400
Hugo Kornelis (5/3/2010)
Using ISNULL can work around this - although the result is admittedly not trivial to understand:
AND NULLIF(Column1, Column2) IS NULL
AND NULLIF(Column2, Column1) IS NULL

w00t Hugo!!! Yuk! Sick



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Hugo Kornelis
Hugo Kornelis
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34221 Visits: 13109
Paul White NZ (5/3/2010)
Hugo Kornelis (5/3/2010)
Using ISNULL can work around this - although the result is admittedly not trivial to understand:
AND NULLIF(Column1, Column2) IS NULL
AND NULLIF(Column2, Column1) IS NULL

w00t Hugo!!! Yuk! Sick
Whistling


Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
sknox
sknox
SSCertifiable
SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)

Group: General Forum Members
Points: 6919 Visits: 3158
Paul White NZ (5/3/2010)
Hugo Kornelis (5/3/2010)
Using ISNULL can work around this - although the result is admittedly not trivial to understand:
AND NULLIF(Column1, Column2) IS NULL
AND NULLIF(Column2, Column1) IS NULL

w00t Hugo!!! Yuk! Sick


Think I have to go with Paul on this one. For me,


WHERE Column18 > 27
AND (Column1 = Column2 OR (Column1 IS NULL AND Column2 IS NULL))



is easier to write, to read, and to understand quickly (and should perform as well or better) than:


WHERE Column18 > 27
AND NULLIF(Column1, Column2) IS NULL
AND NULLIF(Column2, Column1) IS NULL



So I don't see any value to NULLIF in this scenario.

As for me, I read the question, understood the logic, did the process, decided on NULL, then clicked on 0. Steve, when are you going to get that module that scores us on what we meant to choose, rather than what we actually chose...? :-P
Adam Haines
Adam Haines
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13440 Visits: 3135
I think it is also important to understand what is going on underneath the hood, when NULLIF is used. Unbeknownst to some, NULLIF is actually a case expression under the hood, which means it is subject to data type precedence. The second value is not always implicitly converted to the data type of the first value. Whichever side has the less data type precedence will be converted to the other data type. This can cause implict conversion errors, if the columns cannot be converted to the higher data type.

e.g.


DECLARE @t TABLE(fl int);

INSERT INTO @t VALUES (0);

DECLARE @var CHAR(1),
@var2 DATETIME
SET @var = ''
SET @var2 = GETDATE()

select nullif(fl, @var)
FROM @t
--Compute Scalar(DEFINESad[Expr1004]=CASE WHEN [fl]=CONVERT_IMPLICIT(int,[@var],0) THEN NULL ELSE [fl] END))

select nullif(fl, @var2)
FROM @t
--Compute Scalar(DEFINESad[Expr1004]=CASE WHEN CONVERT_IMPLICIT(datetime,[fl],0)=[@var2] THEN NULL ELSE [fl] END))






My blog: http://jahaines.blogspot.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search