In some SQL dialects, how can you handle NULL-safe equality checks in predicates?

In some SQL dialects, how can you handle NULL-safe equality checks in predicates?

• A. Use (column IS NULL OR column = value) to cover NULLs in a predicate.
• B. Use IS NULL to compare any value.
• C. Use COALESCE(column, value) = value;
• D. Use NULLIF(column, value) IS NULL;

Answer: (A)

When you compare a column to a value, a NULL in that column doesn’t behave like any real value—column = value yields UNKNOWN for NULLs, so those rows are filtered out. The way to handle NULLs in a predicate is to explicitly cover both possibilities: the column equals the value, or the column is NULL. Writing it as (column IS NULL OR column = value) ensures you select rows where the column has the exact value or is NULL, making the check NULL-safe in a clear, portable way. Other patterns exist but are less straightforward. IS NULL alone only matches NULLs and misses rows where the column equals the value. Using COALESCE(column, value) = value can work in some cases but can be surprising and may behave differently if value is NULL or affect index usage. NULLIF(column, value) IS NULL is a clever trick but harder to read and understand at a glance. The explicit OR pattern is the most direct and reliable for NULL-safe equality in predicates.

When you compare a column to a value, a NULL in that column doesn’t behave like any real value—column = value yields UNKNOWN for NULLs, so those rows are filtered out. The way to handle NULLs in a predicate is to explicitly cover both possibilities: the column equals the value, or the column is NULL. Writing it as (column IS NULL OR column = value) ensures you select rows where the column has the exact value or is NULL, making the check NULL-safe in a clear, portable way.

Other patterns exist but are less straightforward. IS NULL alone only matches NULLs and misses rows where the column equals the value. Using COALESCE(column, value) = value can work in some cases but can be surprising and may behave differently if value is NULL or affect index usage. NULLIF(column, value) IS NULL is a clever trick but harder to read and understand at a glance. The explicit OR pattern is the most direct and reliable for NULL-safe equality in predicates.