Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Play with NULLIF


Play with NULLIF

Author
Message
Bhuvnesh
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2924 Visits: 4076
Comments posted to this topic are about the item Play with NULLIF

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Oleg Netchaev
Oleg Netchaev
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: 1693 Visits: 1807
Cool question! Thank you. I think that nullif is one of the useful functions which is somewhat overlooked by many.

Oleg
thecosmictrickster@gmail.com
thecosmictrickster@gmail.com
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: 3336 Visits: 932
Good question but just raises another for me - when would you use NULLIF? It may be useful, but I am struggling to see where you would use it. Why would you want something to return NULL if two values are equal? Is it just easier than using a CASE statement?



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

Niths
Niths
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 42
Really challenging question Bhuvanesh.. Smile But still am not clear with the output.. u are comparing 2 expressions in ur query... one is int with value 0 and the other is null... both are of different value.. then how the output is null? null can be output only if the 2 values taken for comparison are equal rite??!!! can u plz explain this?

------------------------
~Niths~
Hard Work never Fails :-)
Tao Klerks
Tao Klerks
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1393 Visits: 1249
Scott Duncan-251680 (5/2/2010)
Good question but just raises another for me - when would you use NULLIF? It may be useful, but I am struggling to see where you would use it.

The most common use case, in my experience, is avoiding division by 0 errors when calculating percentages, proportions, etc:

SELECT SomeAmount * 100.0 / NullIf(SomeTotalAmount, 0) AS SomePercentage 



http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
Bhuvnesh
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2924 Visits: 4076
Niths (5/2/2010)
But still am not clear with the output.. u are comparing 2 expressions in ur query... one is int with value 0 and the other is null... both are of different value.. then how the output is null?
in this question ' ' (blank) will be treated as INT type means 0
.So 0 equal to 0 will give expected result

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
sharath.chalamgari
sharath.chalamgari
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: 1254 Visits: 798
i think mostly we will be using this to avoid divide by ZERO error
iceman26
iceman26
Old Hand
Old Hand (384 reputation)Old Hand (384 reputation)Old Hand (384 reputation)Old Hand (384 reputation)Old Hand (384 reputation)Old Hand (384 reputation)Old Hand (384 reputation)Old Hand (384 reputation)

Group: General Forum Members
Points: 384 Visits: 216
Didn't even know such a function existed.

You learn something new everyday. Now I can go home :-D
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8289 Visits: 11536
Scott Duncan-251680 (5/2/2010)
Good question but just raises another for me - when would you use NULLIF? It may be useful, but I am struggling to see where you would use it. Why would you want something to return NULL if two values are equal? Is it just easier than using a CASE statement?

Agreed - very good question. NULLIF is, in my opinion, one of the most under-apprecaited functions in SQL.

Others have already pointed out that you can use it to avoid divide by zero errors. Another use is when a character column that is nullable has erroneously been populated with a mixture of NULLL and blank strings (instead of NULL). Now, if a report should list 'n/a' to represent the missing strings, you can use
COALESCE(NULLIF(ColumnName, ''), 'n/a')
The NULLIF changes empty strings to NULL (and keeps existing NULLs as they are); the COALESCE then replaces them all with 'n/a'.

A third usse is for comparing string columns that are nullable with the requirement that two NULL values should be considered equal. WHERE Column1 = Column2 will miss the NULL pairs. The usual way to work around this is to use
WHERE Column1 = Column2 OR (Column1 IS NULL AND Column2 IS NULL)
This gets awkward if there are other requirements as well, because you need extra parentheses to seperate the OR from the AND, like this:
WHERE Column18 > 27
AND (Column1 = Column2 OR (Column1 IS NULL AND Column2 IS NULL))
Using ISNULL can work around this - although the result is admittedly not trivial to understand:
WHERE Column18 > 27
AND NULLIF(Column1, Column2) IS NULL
AND NULLIF(Column2, Column1) IS NULL

The first NULLIF will return NULL if both columns are equal or Column1 is NULL; the second is NULL if both are equal or Column2 IS NULL. So they are only both NULL if the columns are equal or both are NULL.


As to your last question - it's not just easier than a CASE expression (not statement!), it is in fact the same. NULLIF(expr1, expr2) is defined as shorthand for CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Niths
Niths
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 42
Thanks a lot Bhuvnesh!! Smile

------------------------
~Niths~
Hard Work never Fails :-)
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