Why Does NULL = NULL Return False in SQL Server?

Understanding Why NULL = NULL is False in SQL Server

In SQL Server, a WHERE clause condition like nullParam = NULL evaluates to false. This might seem counterintuitive, given that NULL often signifies missing data. The key is understanding SQL Server's interpretation of NULL.

NULL doesn't simply represent missing data; it represents an unknown or non-existent value. Therefore, comparing two NULL values is inherently indeterminate. We can't definitively say whether two unknowns are equal or unequal. This uncertainty results in a false evaluation.

This aligns with the ANSI SQL-92 standard. By default, SQL Server operates with ANSI_NULLS ON, enforcing this behavior. However, setting ANSI_NULLS OFF changes the outcome of the comparison to true.

Consider this example:

SET ANSI_NULLS OFF;

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

SET ANSI_NULLS ON;

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

With ANSI_NULLS OFF, the output is 'true'. With ANSI_NULLS ON (the default), the output is 'false', demonstrating how this setting controls SQL Server's handling of NULL comparisons. Using IS NULL is the correct approach for checking for null values.

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