Why does NULL = NULL evaluate to false in SQL server

Deep dive into NULL comparison

Decoding the ANSI SQL-92 standard's view on NULL

Established by ANSI SQL-92 standard, NULL is treated distinctly, representing an unknown value, hence can't be equated to itself. This rule underpins the concept that NULL signifies an undetermined value, which can't logically be the same as another NULL.

Parsing the effect of the ansi_nulls setting

In SQL Server, there exists a variable called ansi_nulls that configures how NULL comparisons are handled. If ansi_nulls is ON (which is standard in SQL Server), NULL = NULL will evaluate to NULL. This setting underscores the importance of using IS NULL or IS NOT NULL for effective null comparisons.

Understanding three-valued logic (3VL) in SQL

SQL Server employs a logic system known as three-valued logic (3VL), where comparisons involving NULL don't yield true or false, but NULL, suggesting an inconclusive state. This unique trait accentuates why using IS NULL is the reliable method for NULL checks.

Working with aggregate functions and NULL

When using aggregate functions, note that SQL Server generally ignores NULL values, but not in the case of COUNT(*), where NULLS are counted. This nuance greatly impacts the calculation results on datasets containing NULL values.

Best practices and tricks with NULL handling

SQL quirks and potential NULL gotchas

It's vital to understand that in the complex world of 3VL, x IS NOT NULL is not the same as NOT(x IS NULL), meaning a simple negation won't always give the expected result. A principle that bears repeating is to avoid using NULL values in SQL, as they can lead to confusion and potential errors.

PostgreSQL: taking a different route with NULL

Moving on to a different RDBMS, PostgreSQL offers a nifty operator IS DISTINCT FROM, providing a more intuitive solution for dealing with NULL values.

x == NULL   -- "Both missing class, coincidence?"

x IS NULL   -- "Yep, confirmed they are missing the class"


Here's what happens under the hood:

• IS DISTINCT FROM: Returns false when both values are NULL (like reconnecting with your high-school friend at a reunion), true if only one is NULL.

• IS NOT DISTINCT FROM: Returns true if both values are NULL, false if one is NULL (like realizing you and your friend were both trying to avoid the high-school reunion).

The aha moment in SQL development

Many developers first encounter the NULL comparison quagmire when they receive zero results using WHERE column = NULL. Such experiences underscore the necessity of understanding and correctly using NULL comparisons to evade SQL anomalies.

Watch out for SQL Server's interpretation of 3VL

SQL Server's three-valued logic interpretation has a few quirks that can lead to unexpected results when dealing with NULL. Due to these peculiarities, careful use of NULL values in conditions and expressions is crucial.