IS DISTINCT FROM Operator

Overview

The IS DISTINCT FROM operator compares two values, considering them distinct even when both are NULL. It returns TRUE if the two values are different and FALSE if they are the same, including the case where both values are NULL.

Syntax

The syntax for the operator is as follows:

value1 IS DISTINCT FROM value2

Where:

• value1 is the first value for comparison.
• value2 is the second value for comparison.

Examples

Case #1: Basic Usage

Consider the following example where we compare two values:

Example 1

SELECT NULL IS DISTINCT FROM NULL AS "Result";

The above query will return the following output:

 Result 
--------
 f

Example 2

SELECT 10 IS DISTINCT FROM 20 AS "Result";

The above query will return the following output:

 Result 
--------
 t

Example 3

SELECT 10 IS DISTINCT FROM 10 AS "Result";    

The above query will return the following output:

 Result 
--------
 f

Case #2: Comparing NULL Values

In this example, we’ll compare NULL values using the IS DISTINCT FROM operator:

Example 1

SELECT NULL IS DISTINCT FROM 10 AS "Result";  

The above query will return the following output:

 Result 
--------
 t

Example 2

SELECT 10 IS DISTINCT FROM NULL AS "Result";

The above query will return the following output:

 Result 
--------
 t

Case #3: Tracking Inventory Variations

Suppose we have a table named inventory_changes that tracks changes in the quantities of products in a warehouse. The table has the following 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);

We want to retrieve records where the change quantity is distinct from zero. In this scenario, the IS DISTINCT FROM operator can be used.

SELECT *
FROM inventory_changes
WHERE change_quantity IS DISTINCT FROM 0;

The result of the query will not include the 0 values as shown below:

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