Play with NULLIF

Question

Play with NULLIF

Viewing 13 posts - 16 through 27 (of 27 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

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[/url]
Learn Extended Events

Paul White SSC Guru Points: 150467 More actions · Answer 2

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
SQLkiwi blog
@SQL_Kiwi

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 3

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: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

sknox SSChampion Points: 12388 More actions · Answer 4

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...? 😛

Adam Haines SSC-Insane Points: 23217 More actions · Answer 5

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(DEFINE:([Expr1004]=CASE WHEN [fl]=CONVERT_IMPLICIT(int,[@var],0) THEN NULL ELSE [fl] END))

select nullif(fl, @var2)

FROM @t

--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN CONVERT_IMPLICIT(datetime,[fl],0)=[@var2] THEN NULL ELSE [fl] END))

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

mtillman-921105 SSCertifiable Points: 7156 More actions · Answer 6

Interesting question - thanks.

The explanation led me to wonder what other character CAST() would return with 0. I've found two:

select cast('+' as smallint)

select cast('-' as smallint)

But why should a negative or positive sign return a zero?

The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking

Paul White SSC Guru Points: 150467 More actions · Answer 7

@sknox:

I'm pretty sure Hugo posted the NULLIF 'solution' as a bit of fun - though the idea behind it is very clever and worth posting just for that.

My comment was intended to be humourous as much as anything else - I laughed when I saw Hugo's :Whistling: reply.

(I don't disagree with anything you wrote by the way)

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 8

Adam Haines (5/3/2010)
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(DEFINE:([Expr1004]=CASE WHEN [fl]=CONVERT_IMPLICIT(int,[@var],0) THEN NULL ELSE [fl] END))
select nullif(fl, @var2)
FROM @t
--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN CONVERT_IMPLICIT(datetime,[fl],0)=[@var2] THEN NULL ELSE [fl] END))

thats called having red meat with cold beer on beach....PERFECT fruit for this thread.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

sknox SSChampion Points: 12388 More actions · Answer 9

Paul White NZ (5/3/2010)
@sknox:
I laughed when I saw Hugo's :Whistling: reply.
Paul

So did I ... AFTER I'd already posted my reply. Oh, the wonders of asynchronous operations!

webrunner SSC-Dedicated Points: 31728 More actions · Answer 10

Great question. I learned that I should read more carefully. This question was about NULLIF (which I had never learned), not ISNULL! :blush:

Thanks,

webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

thecosmictrickster@gmail.com SSChampion Points: 10386 More actions · Answer 11

Thanks for the info Hugo. I can now see where it would be useful in a couple of queries I have for some reports - where I was getting a potential division by zero, depending on the input data. I seem to remember putting a rather inelegant solution (in comparison) to handle it. Will have to revisit.

Scott Duncan

MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare

psharma-1140604 SSC Journeyman Points: 88 More actions · Answer 12

When you cast(blank('') as int), it returns 0. so when you compare 0 with 0 nullif returns NULL.

declare @a smallint

set @a= 0

select nullif(@a,0)

Ans: NULL

Dhruvesh Shah SSCrazy Points: 2303 More actions · Answer 13

Awesome question. didn't know nullIf can be these much tricky...