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
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

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

Hugo, thanks for the additional information and insight.
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28641 Visits: 13268
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
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4337 Visits: 3648
Thanks Hugo for the information, very useful.
Trey Staker
Trey Staker
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1292 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
SSChasing Mays
SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)

Group: General Forum Members
Points: 611 Visits: 430
Good question. Thanks Hugo for the detailed explanation.
SQLRNNR
SQLRNNR
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: 33934 Visits: 18560
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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16588 Visits: 11355
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
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11446 Visits: 12007
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
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2683 Visits: 2833
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
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

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