How to compare values which may both be null in T-SQL

Enhanced T-SQL comparison techniques

Strive for comprehensive NULL comparison approaches to better handle complex scenarios. A series of effective practices for NULL-sensitive case handling includes the implementation of advanced functions and common duplicate checks.

INTERSECT for NULL-sensitive equality

The INTERSECT operator is an ace in the hole when considering NULL-sensitive equality. It returns matches in both Value1 and Value2, including those instances where both are NULL:

SELECT CASE 
         WHEN EXISTS(
              SELECT Value1 
              INTERSECT 
              SELECT Value2)
         THEN 'Exact Match' 
         ELSE 'No Match' 
       END as IntersectionResult
FROM YourTable


Be careful, INTERSECT might take your NULLs and run with them.

The role of UNIQUE constraints

Preserve data integrity through implementing a UNIQUE constraint on your nullable columns. This practice helps in preventing duplicate rows and maintains the quality of your data:

CREATE UNIQUE INDEX idx_your_column 
ON YourTable(YourColumn) 
WHERE YourColumn IS NOT NULL;


Always remember: duplicate data is about as useful as a chocolate teapot.

Inequality checks using NULLIF

In inequality checks, combining ISNULL with NULLIF can provide a concise way to express non-equivalence:

SELECT CASE
         WHEN NULLIF(Value1, Value2) IS NOT NULL OR NULLIF(Value2,Value1) IS NOT NULL
         THEN 'Not Strictly Equal' -- notice the homework-like phrasing
         ELSE 'Equal'
       END as InequalityResult
FROM YourTable


I'm not saying they're different; just they're not strictly the same.

Finer points and common pitfalls

Beware of ANSI_NULLS

Remember, the treatment of NULL values in T-SQL can be influenced by the ANSI_NULLS session setting. With this setting activated, NULL comparisons conform to the SQL-92 standard, which can help to avoid unintentionally incorrect results:

SET ANSI_NULLS ON;


Efficient indexing

For maximized efficiency in dealing with SQL indexes, alignment of your comparison logic with your indexing strategy is key. Construct composite indexes on columns often compared together and consider filtered indexes for columns with less populated values.

Complex data structures

When working with complex data structures or an extensive set of columns, abstract the comparison logic into user-defined functions or views. This not only standardizes NULL handling but also reduces redundancy across your queries.