IS_NOT_DISTINCT_FROM

Return true if the two operands are equal, treating NULL as equal to NULL.

Category: miscReturns: BOOLEANDialect: PostgreSql

Syntax

IS_NOT_DISTINCT_FROM(a, b)

Description

## Overview Returns TRUE when the two operands are equal and FALSE when they differ, treating NULL as an ordinary value that equals only itself. This is the NULL-safe counterpart to the standard `=` operator; it never returns NULL, which makes it the right choice for join keys, MERGE predicates, and deduplication logic that must handle NULL consistently. This function is the inverse of IS_DISTINCT_FROM. ## Behavior - Always returns TRUE or FALSE; never returns NULL. - `IS_NOT_DISTINCT_FROM(NULL, NULL)` returns TRUE. - `IS_NOT_DISTINCT_FROM(NULL, x)` and `IS_NOT_DISTINCT_FROM(x, NULL)` return FALSE for any non-NULL x. - For two non-NULL operands, returns the same result as `a = b`. - Deterministic and side effect free. ## Compatibility - Equivalent to the standard SQL `IS NOT DISTINCT FROM` operator and the PG-compat function of the same name.

Parameters

Name	Type	Description
`a`		Specifies the first operand. Any scalar type is accepted as long as the two operands have a compatible comparison type.
`b`		Specifies the second operand. Must be type-compatible with the first operand.

Examples

-- Two equal values
SELECT IS_NOT_DISTINCT_FROM(1, 1);  -- true

-- Two different values
SELECT IS_NOT_DISTINCT_FROM(1, 2);  -- false

-- NULL is equal to NULL under this comparator
SELECT IS_NOT_DISTINCT_FROM(NULL, NULL);  -- true

-- NULL is not equal to a concrete value
SELECT IS_NOT_DISTINCT_FROM(NULL, 1);  -- false

-- Use in a MERGE predicate to avoid NULL mismatches
MERGE INTO crm.catalog.customers t
USING crm.stage.customer_updates s
  ON IS_NOT_DISTINCT_FROM(t.customer_id, s.customer_id)
WHEN MATCHED THEN UPDATE SET t.email = s.email;

-- Equivalent to `a = b OR (a IS NULL AND b IS NULL)` without the verbosity
SELECT order_id FROM sales.catalog.orders WHERE IS_NOT_DISTINCT_FROM(ship_state, bill_state);

Pitfalls

Join keys with `=` silently drop rows where either side is NULL. Use IS_NOT_DISTINCT_FROM (or the infix form) when NULL must match NULL on the join key.
The function performs a full comparison on both operands; it is not cheaper than `=`, and the optimizer may not rewrite it into a hash-join-friendly form. Test performance on large joins.
Implicit type coercion follows `=` semantics. Comparing mismatched types may raise a type error rather than returning FALSE.
The function does not support row-level structured equality when invoked as a function call; use `(a, b) IS NOT DISTINCT FROM (c, d)` if you need row-wise NULL-safe equality.