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

Question

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?

Answer 1

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.