CODEWITHSUNDEEP
sql-servert-sql
user2616821
user2616821

Reputation: 105

Why the output of select nullif(0,'') is NULL (0 is expected)?

Why the output of select nullif(0,'') is NULL? (0 is expected).

How come 0 is equal to ''?

When I use select nullif(convert(varchar,0),'') it actually returns the expected 0.

NULLIF official page says the function compares two scalar expressions. Is 0 not scalar? Please tell me what am I missing in my understanding?

Upvotes: 9

Views: 2919

Answers (2)

Corbin
Corbin

Reputation: 33467

Integers are higher up in the data type precedence, so the varchar is converted to an int. An empty string converted to an int is 0, and from there it's pretty obvious (0 == 0).

0 == 0, thus NULLIF(0, 0) => NULL (since NULLIF(a, b) returns NULL iff a == b)

When you do nullif(convert(varchar,0),''), you're just doing NULLIF('0', ''). Obviously a string containing only 0 and an empty string are not equal, thus you get 0.

A more in-depth explanation is that two different types cannot actually be compared. You can't compare a string an integer, or a string and a float, or an integer and a float, or, so on. This means that to do comparisons of different types, there must be some kind of implicit casting rule. In this case, it just so happens that if you compare a string (well, technically a varchar) and an int, the varchar is converted to an int implicitly. This is a lot easier to see if you consider the following:

SELECT CONVERT(INT, '');

IF '' = 0 SELECT 'Equal' ELSE SELECT 'Not equal';

As you'll see, the conversion yields an integer valued 0. Furthermore, this leads to the comparison between the two evaluating to true.

Upvotes: 11

Sir Crispalot
Sir Crispalot

Reputation: 4854

Is it possible that the nullif function is casting both values either to type bit or int, as implied by the first value?

If so, they would both evaluate to a value of 0, thus the nullif would return null as you are seeing.

This would explain why converting to varchar solves the problem.

Upvotes: 1

Related Questions