IS_DISTINCT_FROM

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

Category: miscReturns: BOOLEANDialect: PostgreSql

Syntax

IS_DISTINCT_FROM(a, b)

Description

## Overview Returns TRUE when the two operands differ and FALSE when they are equal, treating NULL as an ordinary value that equals only itself. Unlike the standard `<>` operator, IS_DISTINCT_FROM never returns NULL, which makes it the right choice for change detection in SCD-2 loads, MERGE predicates, and other workflows that must behave consistently when one side is NULL. This function is the inverse of IS_NOT_DISTINCT_FROM. ## Behavior - Always returns TRUE or FALSE; never returns NULL. - `IS_DISTINCT_FROM(NULL, NULL)` returns FALSE. - `IS_DISTINCT_FROM(NULL, x)` and `IS_DISTINCT_FROM(x, NULL)` return TRUE for any non-NULL x. - For two non-NULL operands, returns the same result as `a <> b`. - Operand types must be comparable; implicit coercions follow the same rules as the `=` operator. - Deterministic and side effect free. ## Compatibility - Equivalent to the standard SQL `IS 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 different integers
SELECT IS_DISTINCT_FROM(1, 2);  -- true

-- Two equal integers
SELECT IS_DISTINCT_FROM(1, 1);  -- false

-- NULL versus a concrete value is distinct
SELECT IS_DISTINCT_FROM(NULL, 1);  -- true

-- Two NULLs are considered equal (not distinct)
SELECT IS_DISTINCT_FROM(NULL, NULL);  -- false

-- Detect any column change in a slowly changing dimension
SELECT customer_id
FROM crm.catalog.customers_current c
JOIN crm.catalog.customers_staging s USING (customer_id)
WHERE IS_DISTINCT_FROM(c.email, s.email)
   OR IS_DISTINCT_FROM(c.phone, s.phone);

-- Use instead of <> to include NULL-handling
SELECT order_id FROM sales.catalog.orders WHERE IS_DISTINCT_FROM(shipped_at, delivered_at);

Pitfalls

The standard `<>` operator returns NULL when either operand is NULL, which silently excludes rows from filter predicates. Replace `<>` with IS_DISTINCT_FROM whenever NULL should be compared as a regular value.
IS_DISTINCT_FROM does not support row-wise comparison of records in function form; compare column by column or use the infix `(a, b) IS DISTINCT FROM (c, d)` syntax if row equality is needed.
Implicit type coercion follows `=` semantics, so comparing an INT and a STRING may raise a type error. Cast operands explicitly when mixing types.
The function is logically NULL-safe but does not reorder or short-circuit; both operands are evaluated.