The famous NULL comparisons (Three-valued logic – SQL Server)

Wim Kok send me these funny comparisons that involve a NULL-value:

Queries:
NULL-comparisons-query

Results:
NULL-comparisons-results

Code:

PRINT 'Comparison: NULL = NULL'
IF NULL = NULL
PRINT 'Answer: I agree :)'
ELSE
PRINT 'Answer: I disagree :|';

PRINT ''

PRINT 'Comparison: NULL != NULL'
IF NULL != NULL
PRINT 'Answer: I agree :)'
ELSE
PRINT 'Answer: I disagree :|';

PRINT ''

PRINT 'Comparison: NULL != 4'
IF NULL != 4
PRINT 'Answer: I agree :)'
ELSE
PRINT 'Answer: I disagree :|';

About
NULL as a value equals UNKNOWN in three-valued logic (3VL), more info about that:
http://en.wikipedia.org/wiki/Three-valued_logic

It appears that the earlier version of SQL Server had it wrong 🙂

SET ANSI_NULLS { ON | OFF }

Some of you may have seen existing code where a predicate such as Column = NULL is used, and does actually return rows. This is probably legacy code that has not been maintained in a long time (and if it isn’t, it has to be written by a legacy developer who has not maintained his knowledge in a long time).

Very early versions of SQL Server were released before agreement was reached in the ANSI committee on all features. As a result, some features were implemented in a different way than what the ANSI committee turned out to describe, facing Microsoft developers with the challenge to upgrade to ANSI compliant behaviour without breaking existing code.

Behaviour of NULL comparisons in predicates is one such example where the original version of SQL Server “got it wrong”. So when SQL Server was changed to return Unknown on a comparison with NULL, the SET ANSI_NULL OFF option was introduced to force the “old” behaviour that would return False on NULL vs. non-NULL comparison, and True on NULL vs. NULL comparison.

The SET ANSI_NULL option has been marked as deprecated in SQL Server 2005, so if you find code that still relies on this setting, you’d better change it ASAP. This would of course be very easy if you only had to look for “= NULL” and “ NULL”, and change them to “IS NULL” and “IS NOT NULL” – but unfortunately, expressions of the form “= expression” where expression can be NULL are also affected by the ANSI_NULL setting; these are much harder to identify or fix!

Click here for Hugo Kornelis‘s full blogpost and more info about NULL comparisons and their behaviors.

Want to know more about NULL? Click here.

Advertisements

One thought on “The famous NULL comparisons (Three-valued logic – SQL Server)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s