FirebirdSQL logo
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

=

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>