​
Overview

The IS NOT DISTINCT FROM operator is a counterpart to IS DISTINCT FROM.

It compares two values, treating them as equal even when they are both NULL. This operator returns TRUE if the two values are the same, including the case where both values are NULL and FALSE if they are different.

​
Syntax

The syntax for the operator is as follows:

value1 IS NOT 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 45 IS NOT DISTINCT FROM 45 AS "Result";

The above query will return the following output:

 Result 
--------
 t

Example 2

SELECT 60 IS NOT DISTINCT FROM 30 AS "Result";

The above query will return the following output:

 Result 
--------
 f

Example 3

SELECT NULL IS NOT DISTINCT FROM NULL AS "Result";

The above query will return the following output:

 Result 
--------
 t

​
Case #2: Comparing NULL Values

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

Example 1

SELECT NULL IS NOT DISTINCT FROM 80 AS "Result";

The above query will return the following output:

 Result 
--------
 f

Example 2

SELECT 5 IS NOT DISTINCT FROM NULL AS "Result";

The above query will return the following output:

 Result 
--------
 f

​
Case #3: Analyzing Data Completeness

Suppose we have a table named customer_contacts that stores customer contact information. 

CREATE TABLE customer_contacts (
  customer_id INT,
  email TEXT,
  phone TEXT
);

INSERT INTO customer_contacts VALUES
(101, '[email protected]', NULL),
(102, NULL, '+1234567890'),
(103, '[email protected]', '+9876543210'),
(104, NULL, NULL),
(105, '[email protected]', '+5555555555');

Our objective is to retrieve records from this table where an email address or a phone number is available for contacting the customers.

SELECT *
FROM customer_contacts
WHERE email IS NOT DISTINCT FROM phone;

In this query, we retrieve all rows from the customer_contacts table where the email and phone are NULL. We can conclude that the customer with customer_id 104 has no phone number or email address.

 customer_id | email | phone 
-------------+-------+-------
         104 |       |
IS DISTINCT FROM OperatorOverview