IS [NOT] DISTINCT FROM

Data Types and SubtypesData Definition (DDL) Statements

`IS [NOT] DISTINCT FROM`

Syntax

<operand1> IS [NOT] DISTINCT FROM <operand2>

Two operands are considered DISTINCT (different) if they have a different value or if one of them is NULL and the other non-null.They are considered NOT DISTINCT (equal) if they have the same value or if both of them are NULL.

IS [NOT] DISTINCT FROM always returns TRUE or FALSE and never UNKNOWN (NULL) (unknown value).Operators ‘=’ and ‘<>’, conversely, will return UNKNOWN (NULL) if one or both operands are NULL.

Table 1. Results of Various Comparison Predicates
Operand values	Result of various predicates
Operand values	`=`	`IS NOT DISTINCT FROM`	`<>`	`IS DISTINCT FROM`
Same value	`TRUE`	`TRUE`	`FALSE`	`FALSE`
Different values	`FALSE`	`FALSE`	`TRUE`	`TRUE`
Both `NULL`	`UNKNOWN`	`TRUE`	`UNKNOWN`	`FALSE`
One `NULL`, one non-`NULL`	`UNKNOWN`	`FALSE`	`UNKNOWN`	`TRUE`

Examples

SELECT ID, NAME, TEACHER
FROM COURSES
WHERE START_DAY IS NOT DISTINCT FROM END_DAY;

-- PSQL fragment
IF (NEW.JOB IS DISTINCT FROM OLD.JOB)
THEN POST_EVENT 'JOB_CHANGED';

Boolean `IS [NOT]`

Syntax

<value> IS [NOT] { TRUE | FALSE | UNKNOWN }

The IS predicate with Boolean literal values checks if the expression on the left side matches the Boolean value on the right side.The expression on the left side must be of type BOOLEAN, otherwise an exception is raised.

The IS [NOT] UNKNOWN is equivalent to IS [NOT] NULL.

Note	The right side of the predicate only accepts the literals `TRUE`, `FALSE`, `UNKNOWN`, and `NULL`.It does not accept expressions.

Using the IS predicate with a Boolean data type

-- Checking FALSE value
SELECT * FROM TBOOL WHERE BVAL IS FALSE;

ID            BVAL
============= =======
2             <false>

-- Checking UNKNOWN value
SELECT * FROM TBOOL WHERE BVAL IS UNKNOWN;

ID            BVAL
============= =======
3             <null>

IS [NOT] DISTINCT FROM

IS [NOT] DISTINCT FROM

Boolean IS [NOT]

`IS [NOT] DISTINCT FROM`

Boolean `IS [NOT]`