Isdistinct

Isdistinct is an operator used in SQL to compare two values in a NULL-safe way. It corresponds to the SQL standard operator IS DISTINCT FROM and is designed to yield a definite boolean result even when NULLs are involved. The operator returns true when the two expressions are not equal, or when exactly one of the operands is NULL; it returns false when the operands are equal or both are NULL. This behavior contrasts with the usual inequality operator (<>, !=), which can yield unknown (NULL) when either side is NULL, complicating predicate logic in WHERE clauses and JOIN conditions.

In practice, the semantics can be summarized as: a IS DISTINCT FROM b is true if a and b are different values or one is NULL and the other is not; it is false if a and b are the same non-null value or both are NULL. The opposite operator, IS NOT DISTINCT FROM, is true when a and b are equal or both NULL.

Examples:

- SELECT 5 IS DISTINCT FROM 5; returns false

- SELECT 5 IS DISTINCT FROM 7; returns true

- SELECT NULL IS DISTINCT FROM NULL; returns false

- SELECT NULL IS DISTINCT FROM 1; returns true

Emulation in systems that do not support IS DISTINCT FROM can be achieved with a composite predicate, such as (a IS NULL AND b IS NOT NULL) OR (a IS NOT NULL AND b IS NULL) OR (a IS NOT NULL AND b IS NOT NULL AND a <> b).

Support and usage vary by database system. PostgreSQL and other databases that adhere to the SQL standard provide IS DISTINCT FROM, while some systems may require manual expressions or alternative syntax. See also IS NULL, IS NOT NULL, and IS NOT DISTINCT FROM for related NULL-aware comparisons.

---