SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Bhuvnesh / Play with NULLIF / Latest Posts

RE: Play with NULLIF

Dhruvesh Shah — Sun, 07 Nov 2010 16:46:58 GMT

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

RE: Play with NULLIF

psharma-1140604 — Tue, 22 Jun 2010 04:18:33 GMT

When you cast(blank('') as int), it returns 0. so when you compare 0 with 0 nullif returns NULL.declare @a smallintset @a= 0select nullif(@a,0)Ans: NULL

RE: Play with NULLIF

thecosmictrickster@gmail.com — Thu, 06 May 2010 14:46:42 GMT

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.

RE: Play with NULLIF

webrunner — Tue, 04 May 2010 07:36:33 GMT

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

RE: Play with NULLIF

sknox — Tue, 04 May 2010 07:24:39 GMT

[quote][b]Paul White NZ (5/3/2010)[/b][hr]@sknox:I laughed when I saw Hugo's :Whistling: reply.Paul[/quote]So did I ... AFTER I'd already posted my reply. Oh, the wonders of asynchronous operations!

RE: Play with NULLIF

Bhuvnesh — Tue, 04 May 2010 00:03:40 GMT

[quote][b]Adam Haines (5/3/2010)[/b][hr]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.[code]DECLARE @t TABLE(fl int);INSERT INTO @t VALUES (0);DECLARE @var CHAR(1), @var2 DATETIMESET @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))[/code][/quote] thats called having red meat with cold beer on beach....PERFECT fruit for this thread.

RE: Play with NULLIF

Paul White — Mon, 03 May 2010 19:58:16 GMT

@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

RE: Play with NULLIF

mtillman-921105 — Mon, 03 May 2010 14:30:46 GMT

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?

RE: Play with NULLIF

Adam Haines — Mon, 03 May 2010 11:08:57 GMT

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.[code]DECLARE @t TABLE(fl int);INSERT INTO @t VALUES (0);DECLARE @var CHAR(1), @var2 DATETIMESET @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))[/code]

RE: Play with NULLIF

sknox — Mon, 03 May 2010 10:15:27 GMT

[quote][b]Paul White NZ (5/3/2010)[/b][hr][quote][b]Hugo Kornelis (5/3/2010)[/b][hr]Using ISNULL can work around this - although the result is admittedly not trivial to understand:AND NULLIF(Column1, Column2) IS NULLAND NULLIF(Column2, Column1) IS NULL[/quote]:w00t: Hugo!!! Yuk! :sick:[/quote]Think I have to go with Paul on this one. For me, [code="sql"]WHERE Column18 > 27AND (Column1 = Column2 OR (Column1 IS NULL AND Column2 IS NULL))[/code]is easier to write, to read, and to understand quickly (and should perform as well or better) than:[code="sql"]WHERE Column18 > 27AND NULLIF(Column1, Column2) IS NULLAND NULLIF(Column2, Column1) IS NULL[/code]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

RE: Play with NULLIF

Hugo Kornelis — Mon, 03 May 2010 10:09:39 GMT

RE: Play with NULLIF

Paul White — Mon, 03 May 2010 09:55:38 GMT

[quote][b]Hugo Kornelis (5/3/2010)[/b][hr]Using ISNULL can work around this - although the result is admittedly not trivial to understand:AND NULLIF(Column1, Column2) IS NULLAND NULLIF(Column2, Column1) IS NULL[/quote]:w00t: Hugo!!! Yuk! :sick:

RE: Play with NULLIF

SQLRNNR — Mon, 03 May 2010 09:48:44 GMT

Nice question - great explanation Hugo.

RE: Play with NULLIF

rjv_rnjn — Mon, 03 May 2010 09:28:53 GMT

Good question. Thanks Hugo for the detailed explanation.

RE: Play with NULLIF

Trey Staker — Mon, 03 May 2010 08:47:03 GMT

Good question, Thanks. I also learned a lot from the discussion, thanks Hugo for the explanation.

RE: Play with NULLIF

Cliff Jones — Mon, 03 May 2010 08:42:05 GMT

Thanks Hugo for the information, very useful.

RE: Play with NULLIF

Koen Verbeeck — Mon, 03 May 2010 07:31:21 GMT

Great question. Learned two new things today:* nullif* how SQL converts an empty string to a numeric datatype.Nice one!

RE: Play with NULLIF

Daniel Bowlin — Mon, 03 May 2010 07:30:28 GMT

Great question, thanks.Hugo, thanks for the additional information and insight.

RE: Play with NULLIF

Niths — Mon, 03 May 2010 05:17:52 GMT

Thanks a lot Bhuvnesh!! :)

RE: Play with NULLIF

Hugo Kornelis — Mon, 03 May 2010 03:20:30 GMT

[quote][b]Scott Duncan-251680 (5/2/2010)[/b][hr]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?[/quote]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 NULLThe 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.

RE: Play with NULLIF

vitesh.bhana — Mon, 03 May 2010 01:20:19 GMT

Didn't even know such a function existed. You learn something new everyday. Now I can go home :-D

RE: Play with NULLIF

sharath.chalamgari — Mon, 03 May 2010 00:55:50 GMT

i think mostly we will be using this to avoid divide by ZERO error

RE: Play with NULLIF

Bhuvnesh — Mon, 03 May 2010 00:41:33 GMT

[quote][b]Niths (5/2/2010)[/b][hr] 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? [/quote] in this question ' ' (blank) will be treated as INT type means 0 .So 0 equal to 0 will give expected result

RE: Play with NULLIF

Tao Klerks — Mon, 03 May 2010 00:20:19 GMT

[quote][b]Scott Duncan-251680 (5/2/2010)[/b][hr]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.[/quote]The most common use case, in my experience, is avoiding division by 0 errors when calculating percentages, proportions, etc:[code="sql"]SELECT SomeAmount * 100.0 / NullIf(SomeTotalAmount, 0) AS SomePercentage [/code]

RE: Play with NULLIF

Niths — Sun, 02 May 2010 23:15:12 GMT

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?

RE: Play with NULLIF

thecosmictrickster@gmail.com — Sun, 02 May 2010 21:55:33 GMT

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?

RE: Play with NULLIF

Oleg Netchaev — Sat, 01 May 2010 17:45:09 GMT

Cool question! Thank you. I think that nullif is one of the useful functions which is somewhat overlooked by many.Oleg

Play with NULLIF

Bhuvnesh — Sat, 01 May 2010 17:21:37 GMT

Comments posted to this topic are about the item [B]Play with NULLIF[/B]