Problem
As SQL Server developers, we frequently encounter NULLs, the unknowns of our data. Unlike other SQL dialects (like SparkSQL or PostgreSQL’s <=> operator), T-SQL treats NULL comparisons uniquely:
- NULL = NULL doesn’t evaluate to TRUE, nor does NULL != NULL evaluate to FALSE; both yield UNKNOWN.
This behavior creates a persistent challenge when comparing two values, ensuring they are considered “equal” if both are NULL, and “not equal” if one is NULL and the other is not. Many SQL Server developers attempt to build custom NULL-safe logic, but often introduce critical flaws that lead to incorrect data filtering, joins, or application behavior (especially while implementing change data capture).
Solution
Correctly implementing NULL-safe equality in SQL Server requires understanding UNKNOWN and leveraging the right combination of logical operators. We will demonstrate the correct pattern and then unmask a common, flawed alternative that often catches developers off guard.
Desired NULL-Safe Equal Behavior
We want A = B to be TRUE if:
- A equals B (and neither is NULL).
- A is NULL AND B is NULL.
Otherwise, we want A = B to be FALSE.
Embracing the Correct Pattern (and Spotting the Silent Flaw)
Let’s deep dive into understanding the correct pattern for NULL-safe equality and spot the silent flaws that are really hard to identify while doing NULL-to-NULL data comparison.
Correct T-SQL Pattern for NULL-Safe Equal
This pattern reliably achieves the desired NULL-safe behavior:
(Value1 = Value2 OR (Value1 IS NULL AND Value2 IS NULL))Why It Works
- Value1 = Value2: This part handles all cases where both Value1 and Value2 are non-NULL and are genuinely equal. If either is NULL, this comparison alone returns UNKNOWN, so the OR clause becomes crucial.
- (Value1 IS NULL AND Value2 IS NULL): This explicitly captures the case where both values are NULL, causing the entire OR condition to evaluate to TRUE (because TRUE OR UNKNOWN or TRUE OR FALSE is TRUE).
Common, Flawed Pattern (and Why It Fails)
A frequent mistake involves trying to use != (not equals) in the first part of the OR condition, like this:
(Value1 != Value2 OR (Value1 IS NULL AND Value2 IS NULL))The intention might be to catch cases where they are not different, but the semantics of != with NULLs and UNKNOWN fundamentally break this logic for non-NULL comparisons.
Let’s see the T-SQL demonstration side-by-side:
--MSSQLTips.com (SQL Server) -- Demonstrate NULL-safe equality: Correct vs. Flawed Implementations
-- 1. Create a temporary table with sample data.
CREATE TABLE #NullSafeEqualsDemo (
TestID INT IDENTITY(1,1) PRIMARY KEY,
Value1 INT,
Value2 INT
);
-- 2. Insert various combinations of NULL and non-NULL values.
INSERT INTO #NullSafeEqualsDemo (Value1, Value2) VALUES
(1, 1), -- Case A: Both equal, non-NULL
(1, 2), -- Case B: Both unequal, non-NULL
(1, NULL), -- Case C: One non-NULL, one NULL
(NULL, 2), -- Case D: One NULL, one non-NULL
(NULL, NULL); -- Case E: Both NULL
-- 3. Select and apply both logic patterns for direct comparison.
SELECT
TestID,
Value1,
Value2,
-- Correct NULL-safe equality check
CASE
WHEN (Value1 = Value2 OR (Value1 IS NULL AND Value2 IS NULL))
THEN 'CORRECT: Values are NULL-Safe EQUAL'
ELSE 'CORRECT: Values are NULL-Safe NOT EQUAL'
END AS CorrectNullSafeCheck,
-- Common, FLAWED NULL-safe equality check (using !=)
CASE
WHEN (Value1 != Value2 OR (Value1 IS NULL AND Value2 IS NULL))
THEN 'FLAWED: Users Logic says EQUAL (Mistake)'
ELSE 'FLAWED: Users Logic says NOT EQUAL'
END AS FlawedNullSafeCheck
FROM
#NullSafeEqualsDemo;
-- 4. Clean up the temporary table
DROP TABLE #NullSafeEqualsDemo; 
Why the Pattern Fails
- TestID#1 (1, 1):
- 1 != 1 is FALSE. The second part (IS NULL AND IS NULL) is also FALSE. FALSE OR FALSE is FALSE. The CASE statement incorrectly evaluates this as “NOT EQUAL”.
- TestID#2 (1, 2):
- 1 != 2 is TRUE. The second part is FALSE. TRUE OR FALSE is TRUE. The CASE statement incorrectly evaluates this as “EQUAL”.
- TestID#3&4 (1, NULL) & (NULL, 2):
- Value1 != Value2 (e.g., 1 != NULL) evaluates to UNKNOWN. (UNKNOWN OR FALSE) also evaluates to UNKNOWN, leading to the ELSE branch. This happens to give the correct “NOT EQUAL” result for these specific cases, but it’s not because the logic is sound.
- TestID#5 (NULL, NULL):
- NULL != NULL is UNKNOWN. But (NULL IS NULL AND NULL IS NULL) is TRUE. UNKNOWN OR TRUE evaluates to TRUE. This specific case is correctly evaluated as “EQUAL”, but it’s the only one among the non-NULL situations that works as intended.
The core issue is that != operates under the same UNKNOWN rules as =, making its use in this context unreliable for achieving NULL-safe comparisons of non-NULL values.
Things to Consider
Use the following when implementing NULL-safe equality in SQL Server.
Adopt the Correct Pattern
Make (ColumnA = ColumnB OR (ColumnA IS NULL AND ColumnB IS NULL)) your go-to pattern for NULL-safe equality in SQL Server.
Review Existing Code
Actively search your existing stored procedures, functions, and views for custom NULL-handling logic. Also, pay close attention to CASE statements or WHERE clauses that attempt NULL-safe comparisons, especially those using != or IS NOT NULL in unexpected ways.
Understand UNKNOWN
Deepen your understanding of UNKNOWN in SQL. It’s a common source of bugs that silently filter out rows you expect to see.
Note: WHERE UNKNOWN never returns rows.
Test Thoroughly
Always include test cases with NULLs when developing or reviewing comparison logic. This reveals subtle flaws that might go unnoticed with only non-NULL data.
IS DISTINCT FROM (SQL Server 2022+)
While not exactly NULL-safe EQUAL, SQL Server 2022 introduced IS DISTINCT FROM and IS NOT DISTINCT FROM.
- Value1 IS NOT DISTINCT FROM Value2 behaves like NULL-safe EQUAL (TRUE if both are equal OR both are NULL). This is the cleanest syntax for modern SQL Server versions.
- Value1 IS DISTINCT FROM Value2 behaves like NULL-safe NOT EQUAL. These operators simplify the logic considerably for the newer environments.
Next Steps
