IS DISTINCT FROM Operator

15min
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:
Pgsql
|
value1 IS DISTINCT FROM value2
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
Pgsql
|
SELECT NULL IS DISTINCT FROM NULL AS "Result";
SELECT NULL IS DISTINCT FROM NULL AS "Result";
﻿
The above query will return the following output:
Pgsql
|
 Result 
--------
 f
 Result 
--------
 f
﻿
Example 2
Pgsql
|
SELECT 10 IS DISTINCT FROM 20 AS "Result";
SELECT 10 IS DISTINCT FROM 20 AS "Result";
﻿
The above query will return the following output:
Pgsql
|
 Result 
--------
 t
 Result 
--------
 t
﻿
Example 3
Pgsql
|
SELECT 10 IS DISTINCT FROM 10 AS "Result";    
SELECT 10 IS DISTINCT FROM 10 AS "Result";    
﻿
The above query will return the following output:
Pgsql
|
 Result 
--------
 f
 Result 
--------
 f
﻿
Case #2: Comparing NULL Values
In this example, we'll compare NULL values using the IS DISTINCT FROM operator:
Example 1
Pgsql
|
SELECT NULL IS DISTINCT FROM 10 AS "Result";  
SELECT NULL IS DISTINCT FROM 10 AS "Result";  
﻿
The above query will return the following output:
Pgsql
|
 Result 
--------
 t
 Result 
--------
 t
﻿
Example 2
Pgsql
|
SELECT 10 IS DISTINCT FROM NULL AS "Result";
SELECT 10 IS DISTINCT FROM NULL AS "Result";
﻿
The above query will return the following output:
Pgsql
|
 Result 
--------
 t
 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:
Pgsql
|
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);
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.
Pgsql
|
SELECT *
FROM inventory_changes
WHERE change_quantity IS DISTINCT FROM 0;
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:
Pgsql
|
 product_id | change_date | change_quantity 
------------+-------------+-----------------
        101 | 2023-08-01  |              50
        101 | 2023-08-02  |             -15
        103 | 2023-08-03  |              30
 product_id | change_date | change_quantity 
------------+-------------+-----------------
        101 | 2023-08-01  |              50
        101 | 2023-08-02  |             -15
        103 | 2023-08-03  |              30
﻿
﻿
Updated 11 Sep 2023
Did this page help you?
Boolean Function
IS NOT DISTINCT FROM Operator
Docs powered by
Archbee
TABLE OF CONTENTS
Case #2: Comparing NULL Values
Case #3: Tracking Inventory Variations