Cloud

IS DISTINCT FROM

The IS DISTINCT FROM operator compares two values and returns TRUE if they are different. NULL`s are treated as comparable, so two `NULL`s are not distinct (returns `FALSE).

Syntax

The syntax for this function is:

value1 IS DISTINCT FROM value2

Where:

  • value1 is the first value for comparison.

  • value2 is the second value for comparison.

Examples

Basic usage

Consider this example, which compares two values:

Example 1

SELECT NULL IS DISTINCT FROM NULL AS "Result";

The preceding query returns the output:

 Result
--------
 f

Example 2

SELECT 10 IS DISTINCT FROM 20 AS "Result";

The preceding query returns the output:

 Result
--------
 t

Example 3

SELECT 10 IS DISTINCT FROM 10 AS "Result";

The preceding query returns the output:

 Result
--------
 f

Compare NULL values

In this example, NULL values are compared using the IS DISTINCT FROM operator:

Example 1

SELECT NULL IS DISTINCT FROM 10 AS "Result";

The preceding query returns the output:

 Result
--------
 t

Example 2

SELECT 10 IS DISTINCT FROM NULL AS "Result";

The preceding query returns the output:

 Result
--------
 t

Track inventory variations

Suppose there is a table named inventory_changes that tracks changes in the quantities of products in a warehouse. The table has the structure:

CREATE TABLE inventory_changes (
  product_id INT,
  change_date DATE,
  change_quantity INT
);

INSERT INTO inventory_changes VALUES
(101, '2023-08-01', 50),
(102, '2023-08-01', 0),
(101, '2023-08-02', -15),
(103, '2023-08-03', 30),
(102, '2023-08-04', 0);

To retrieve records where the change quantity is distinct from zero, use the IS DISTINCT FROM operator.

SELECT *
FROM inventory_changes
WHERE change_quantity IS DISTINCT FROM 0;

The result of the query does not include the 0 values:

 product_id | change_date | change_quantity
------------+-------------+-----------------
        101 | 2023-08-01  |              50
        101 | 2023-08-02  |             -15
        103 | 2023-08-03  |              30