In SQL Server, why is it that NULL does not equal empty string AND doesn't not equal empty string?

Question

This is obviously not going to return a row...

select 1 where null = ''

But why does this also not return a row?

select 1 where null <> ''

How can both of those WHEREs be "false"?

Answer 1

"How can both of those WHEREs be "false"?"

It's not! The answer is not "true" either! The answer is "we don't know".

Think of NULL as a value you don't know yet.

Would you bet it's '' ?

Would you bet it's not '' ?

So, safer is to declare you don't know yet. The answer to both questions, therefore, is not false but I don't know, e.g. NULL in SQL.

Answer 2

Because this is an instance of SQL Server conforming to ANSI SQL ;-)

NULL in SQL is somewhat similar to IEEE NaN in comparison rules: NaN != NaN and NaN == NaN are both false. It takes a special operator IS NULL in SQL (or "IsNaN" for IEEE FP) to detect these special values. (There are actually multiple ways to detect these special values: IS NULL/"IsNaN" are just clean and simple methods.)

However, NULL = x goes one step further: the result of NULL =/<> x is not false. Rather, the result of the expression is itself NULL UNKNOWN. So NOT(NULL = '') is also NULL UNKNOWN (or "false" in a where context -- see comment). Welcome to the world of SQL tri-state logic ;-)

Since the question is about SQL Server, then for completeness: If running run with "SET ANSI_NULLS OFF" -- but see remarks/warnings at top -- then the "original" TSQL behavior can be attained.

"Original" behavior (this is deprecated):

SET ANSI_NULLS OFF;
select 'eq' where null = '';       -- no output
select 'ne' where null <> '';      -- output: ne
select 'not' where not(null = ''); -- output: not; null = '' -> False, not(False) -> True

ANSI-NULLs behavior (default in anything recent, please use):

SET ANSI_NULLS ON;
select 'eq' where null = '';       -- no output
select 'ne' where null <> '';      -- no output
select 'not' where not(null = ''); -- no output; null = '' -> Unknown, not(Unknown) -> Unknown

Happy coding.