<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Bhuvnesh
»
Play with NULLIF
28 posts, Page 1 of 3
1
2
3
»
»»
Play with NULLIF
Rate Topic
Display Mode
Topic Options
Author
Message
Bhuvnesh
Bhuvnesh
Posted Saturday, May 01, 2010 5:21 PM
SSCrazy
Group: General Forum Members
Last Login: Thursday, June 13, 2013 7:03 AM
Points: 2,562,
Visits: 3,453
Comments posted to this topic are about the item
Play with NULLIF
-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post
Post #914283
Oleg Netchaev
Oleg Netchaev
Posted Saturday, May 01, 2010 5:45 PM
SSCommitted
Group: General Forum Members
Last Login: Friday, June 07, 2013 2:42 PM
Points: 1,662,
Visits: 1,710
Cool question! Thank you. I think that nullif is one of the useful functions which is somewhat overlooked by many.
Oleg
Post #914286
thecosmictrickster@gmail.com
thecosmictrickster@gmail.com
Posted Sunday, May 02, 2010 9:55 PM
SSCrazy
Group: General Forum Members
Last Login: Thursday, June 06, 2013 8:37 PM
Points: 2,609,
Visits: 768
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
Post #914448
Niths
Niths
Posted Sunday, May 02, 2010 11:15 PM
SSC Rookie
Group: General Forum Members
Last Login: Tuesday, June 08, 2010 7:47 AM
Points: 26,
Visits: 42
Really challenging question Bhuvanesh.. :) 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
Post #914465
Tao Klerks
Tao Klerks
Posted Monday, May 03, 2010 12:20 AM
Ten Centuries
Group: General Forum Members
Last Login: Sunday, June 16, 2013 6:28 AM
Points: 1,383,
Visits: 1,214
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.
Post #914474
Bhuvnesh
Bhuvnesh
Posted Monday, May 03, 2010 12:41 AM
SSCrazy
Group: General Forum Members
Last Login: Thursday, June 13, 2013 7:03 AM
Points: 2,562,
Visits: 3,453
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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post
Post #914480
sharath.chalamgari
sharath.chalamgari
Posted Monday, May 03, 2010 12:55 AM
Ten Centuries
Group: General Forum Members
Last Login: Yesterday @ 5:37 AM
Points: 1,054,
Visits: 687
i think mostly we will be using this to avoid divide by ZERO error
Post #914484
vitesh.bhana
vitesh.bhana
Posted Monday, May 03, 2010 1:20 AM
Old Hand
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 4:31 AM
Points: 382,
Visits: 196
Didn't even know such a function existed.
You learn something new everyday. Now I can go home
Post #914500
Hugo Kornelis
Hugo Kornelis
Posted Monday, May 03, 2010 3:20 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 2:44 PM
Points: 5,297,
Visits: 7,239
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
Post #914540
Niths
Niths
Posted Monday, May 03, 2010 5:17 AM
SSC Rookie
Group: General Forum Members
Last Login: Tuesday, June 08, 2010 7:47 AM
Points: 26,
Visits: 42
Thanks a lot Bhuvnesh!! :)
------------------------
~Niths~
Hard Work never Fails
Post #914583
« Prev Topic
|
Next Topic »
28 posts, Page 1 of 3
1
2
3
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
