IS [ NOT ]

On this page

Tests whether two values are equal or unequal, treating NULL as equal to NULL.

Syntax

<left> IS <right>
<left> IS NOT <right>

Arguments

ArgumentTypeDescription
<left>Any typeFirst value to compare.
<right>Compatible with <left>Second value to compare. Either side may be NULL.

Returns

Returns boolean, never NULL.

  • Both inputs are NULL: IS returns TRUE; IS NOT returns FALSE.
  • Only one input is NULL: IS returns FALSE; IS NOT returns TRUE.
  • Neither input is NULL: IS works like =; IS NOT works like !=.

Unlike IS, ordinary equality (=) returns NULL if either operand is NULL.

Examples

Check whether each checkout event has a campaign value:

VALUES ('evt-17', 'spring'), ('evt-42', NULL), ('evt-63', 'loyalty')
SELECT $0 AS event_id, $1 AS campaign
SELECT event_id, campaign, campaign IS NULL AS missing, campaign IS NOT NULL AS present
+----------+----------+---------+---------+
| event_id | campaign | missing | present |
+----------+----------+---------+---------+
| evt-17   | spring   | false   | true    |
| evt-42   | NULL     | true    | false   |
| evt-63   | loyalty  | false   | true    |
+----------+----------+---------+---------+