Why NULLIF(NULL,1) =1 ?

Question

Why NULLIF(NULL,1) =1 ?

mssqlnoob

Ten Centuries

Points: 1245
More actions
October 28, 2013 at 9:42 am

#285252

from http://technet.microsoft.com/en-us/library/ms177562.aspx
Returns the same type as the first expression.
NULLIF returns the first expression if the two expressions are not equal. If the expressions are equal, NULLIF returns a null value of the type of the first expression.
SELECT ISNULL(NULL, 1)
1
in this case the first expression should be returned which in this case should be null, why is it 1?
thanks

Viewing 6 posts - 1 through 5 (of 5 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

You quoted BoL on NULLIF and then used ISNULL in an example. They do quite different things. Which are you interested in?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Sean Lange SSC Guru Points: 286573 More actions · Answer 2

mssqlnoob (10/28/2013)
SELECT ISNULL(NULL, 1)
1
in this case the first expression should be returned which in this case should be null, why is it 1?
thanks

It is returning 1 because that is EXACTLY what you asked for. This says if NULL is null then return 1.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

mssqlnoob Ten Centuries Points: 1245 More actions · Answer 3

sorry should be

SELECT ISNULL(NULL,1)

1

Why 1 look like the function's name is very misleading....thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Not really misleading. Function name ISNULL. It checks if the first parameter is null, if so it returns the second, otherwise returns the first. In this case the first parameter is indeed null, so it returns the second, i.e. 1

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

T.Ashish SSCarpal Tunnel Points: 4248 More actions · Answer 5

select NULLIF(1,NULL) = 1 (Both values are not same, so first value)

select NULLIF(NULL,2) = Error "first argument has to be known"

select NULLIF(1,1) = NULL (Both values are same, so null will be returned)

SELECT ISNULL(1,NULL) = 1 (First value is not null, so first value will be returned)

SELECT ISNULL(NULL,1) = 1 (First value is null, so second value will be returned)

SELECT ISNULL(2,1) = 2 (First value is not null, so first value will be returned)