Cloud

IS NOT DISTINCT FROM

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 this function is:

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

The preceding query returns the output:

 Result
--------
 t

Example 2

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

The preceding query returns:

 Result
--------
 f

Example 3

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

The preceding query returns:

 Result
--------
 t

Compare NULL values

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

Example 1

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

The preceding query returns:

 Result
--------
 f

Example 2

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

The preceding query returns:

 Result
--------
 f

Analyze data completeness

Suppose there is 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, 'john@example.com', NULL),
(102, NULL, '+1234567890'),
(103, 'jane@example.com', '+9876543210'),
(104, NULL, NULL),
(105, 'alex@example.com', '+5555555555');

The 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;

This query retrieves all rows from the customer_contacts table where the email and phone are NULL. The result shows that the customer with customer_id 104 has no phone number or email address.

 customer_id | email | phone
-------------+-------+-------
         104 |       |