But usually SQL Server considers "nothing" as a NULL value.

Here is the example:

declare @tab table (a int)

select a from @tab

if (select a from @tab) is null

print 'null'

else

print 'not null'

The result is:

a

-----------

(0 row(s) affected)

null

As we can see, "nothing" = "null" in this case.

I think that the warning (or error, or exception, or whatever) plays its important role.

Here is the modified example:

declare @tab table (a int)

select a from @tab where 1 = 1/0

if (select a from @tab where 1 = 1/0) is null

print 'null'

else

print 'not null'

The result is:

a

-----------

Msg 8134, Level 16, State 1, Line 3

Divide by zero error encountered.

Msg 8134, Level 16, State 1, Line 5

Divide by zero error encountered.

not null

Does it look like "nothing" <> "null"? I don't think so, because of the error message. I think that SQL Server executes "select a from @tab where 1 = 1/0", gets an error, and does not even try to compare this with NULL. Since there's no TRY..CATCH block, batch execution continues and... steps into the ELSE block. But why is the ELSE block not skipped? Maybe it's "by design" 🙂