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

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.

Null has some very odd behaviour when comparing - the wikipedia article explains it quite well. In a nutshell, as well as true and false, there is an unknown value, which SQL returns when doing a comparison.

The SQL standard specifies that NULL = x is false for all x (even if x is itself NULL) and SQL Server is just following the standard. If you want to check if something is or is not NULL, then you have to use x IS NULL or x IS NOT NULL.

Databses have what is known as three valued logic. The values of true, false, and unknown.

Read this http://www.simple-talk.com/sql/learn-sql-server/sql-and-the-snare-of-three-valued-logic/

Because any comparison with NULL returns false (or to be precise: returns NULL)

As NULL is the absence of information you cannot tell whether is equal to or not equal to something.

Probably getting a little repetitive here, but my two cents anyway:

A. a_horse_with_no_name's example (comment, above) is a very good one!

B. In non=mathmatical terms, NULL is an unknown value. An empty String is a string with a length of zero - hence, a "known" value. This is why NULL does not compare equally or not equally to an empty string.

C. Since NULL represents unknown, it is impossible to compare two NULL values for equality. If you don't know the value of X, and you don't know the value of Y, then you don't know if they are equal or not.

See SQL-92 8.2 comparison predicate saying:

General Rules

1. Let X and Y be any two corresponding <row value constructor element>s. Let XV and YV be the values represented by X and Y, respectively.

   Case:

   a) If XV or YV is the null value, then "X <comp op> Y" is unknown.