Why Does `NULL = NULL` Return False in SQL Server (and How Does `ansi_nulls` Affect It)?

Understanding Why NULL = NULL Returns False in SQL Server

SQL Server's handling of NULL values often leads to unexpected results. The = operator, when comparing nullable columns, returns <code>false</code> because NULL signifies an unknown or missing value.

A WHERE clause condition like nullParam = NULL always evaluates to <code>false</code>. This is because it's impossible to definitively confirm equality between two unknown quantities.

The SQL-92 standard dictates this behavior: NULL = NULL is neither true nor false; it's undefined.

The Impact of the ansi_nulls Setting

The behavior changes dramatically depending on the ansi_nulls setting. With ansi_nulls set to OFF, NULL = NULL surprisingly evaluates to <code>true</code>. This is demonstrated below:

Code Example:

<code class="language-sql">SET ANSI_NULLS OFF;

IF NULL = NULL
    PRINT 'true';
ELSE
    PRINT 'false';

SET ANSI_NULLS ON;

IF NULL = NULL
    PRINT 'true';
ELSE
    PRINT 'false';</code>

Output:

With ansi_nulls OFF:

<code>true</code>

With ansi_nulls ON:

<code>false</code>

Best Practices for Handling NULLs

Due to this variability, it's crucial to be aware of the ansi_nulls setting when working with NULL comparisons. To avoid ambiguity, always use IS NULL and IS NOT NULL when checking for NULL values. This ensures consistent and predictable behavior regardless of the ansi_nulls setting. This is the recommended approach for reliable SQL code.

The above is the detailed content of Why Does `NULL = NULL` Return False in SQL Server (and How Does `ansi_nulls` Affect It)?. For more information, please follow other related articles on the PHP Chinese website!