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
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

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

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

Group: General Forum Members
Points: 59071 Visits: 13297
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 (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5129 Visits: 3648
Thanks Hugo for the information, very useful.
Trey Staker
Trey Staker
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1582 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
SSC Eights!
SSC Eights! (911 reputation)SSC Eights! (911 reputation)SSC Eights! (911 reputation)SSC Eights! (911 reputation)SSC Eights! (911 reputation)SSC Eights! (911 reputation)SSC Eights! (911 reputation)SSC Eights! (911 reputation)

Group: General Forum Members
Points: 911 Visits: 430
Good question. Thanks Hugo for the detailed explanation.
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63609 Visits: 18570
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

Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33664 Visits: 11359
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
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18215 Visits: 12426
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
sknox
sknox
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3898 Visits: 2920
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
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 6018 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