"Actually comparisons of a NULL (or a non-NULL value) to a NULL results in UNKNOWN.  Your point is taken though:  The WHERE clause only returns results for rows comparisons that evaluate to TRUE.  Comparisons that evaluate to FALSE or UNKNOWN are dropped from the results."

Well now we're splitting hairs! 

Actually UNKNOWN represents a possible result of a logical comparison while NULL represents a missing/unknown value.  There can be some confusion with the terminology, but NULL is actually not the same thing as UNKNOWN.  Part of the confusion pops up because the word "unknown" is so often used in the definition of NULLs.

As an example (and to add to the confusion) consider the ANSI SQL:1999 standard which added a BOOLEAN data type to SQL.  The BOOLEAN data type can hold one of three possible values:  TRUE, FALSE and UNKNOWN:  SQL three-valued logic and all, so far so good.

However it also has to be nullable, and to be consistent with the rest of the SQL model, NULL would have to indicate that you don't know whether the BOOLEAN column or variable is TRUE, FALSE or UNKNOWN.  I'll give a quick example.  Before someone points it out, I know this won't actually run on T-SQL... this is "pseudo-SQL" to illustrate what it might look like if ANSI BOOLEANs were implemented in T-SQL:

DECLARE @x BOOLEAN;

DECLARE @y BOOLEAN;

DECLARE @z BOOLEAN;

SELECT @x = UNKNOWN;     -- UNKNOWN

SELECT @y = (10 < NULL); -- evaluates to UNKNOWN

SELECT @z = NULL;        -- NULL value

According to this example @x is equal to @y (they are both UNKNOWN).  However, is @x equal to @z?  We don't know the value of @z (is it TRUE, FALSE or UNKNOWN?), but we do know that @x is UNKNOWN.  So is UNKNOWN the same as NULL?  According to the SQL logical model the result of this comparison should be UNKNOWN, since we don't know if @z is TRUE, FALSE or UNKNOWN.  According to SQL:1999 the answer could be TRUE since they say to treat UNKNOWN as equivalent to NULL.

To implement this correctly you actually have to re-work the SQL logical model from three-valued logic to four-valued logic everywhere   The ANSI standard just says to treat UNKNOWNs the same as NULLs, which means you're starting to introduce inconsistent exceptions into the logical model rather than expanding it to accomodate four-valued logic.

As for the ANSI_NULLS setting, I highly recommend leaving it set to the ON position and using the IS NULL and IS NOT NULL operators (or CASE and COALESCE) to check for NULLs.