website logo
⌘K
šŸ Homepage
šŸ‘‹Introduction
Key Concepts & Architecture
šŸš€Run Oxla in 2 minutes
šŸƒā€ā™‚ļøRun Oxla on S3
šŸ›«Multi-node Deployment
šŸ‘Øā€šŸ’»SQL Reference
SQL Queries
SQL Clauses
SQL Data Types
SQL Functions
Schema
Comment Support
🚨Error Handling
šŸ†šDifferences Between Oxla vs. PostgreSQL
šŸ“ˆPublic Metrics
Docs powered by
Archbee
SQL Reference
...
SQL Functions
Boolean Function

IS NOT DISTINCT FROM Operator

15min

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:

Pgsql
|
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

Pgsql
|
SELECT 45 IS NOT DISTINCT FROM 45 AS "Result";  


The above query will return the following output:

Pgsql
|
 Result 
--------
 t


Example 2

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


The above query will return the following output:

Pgsql
|
 Result 
--------
 f


Example 3

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


The above query will return the following output:

Pgsql
|
 Result 
--------
 t


Case #2: Comparing NULL Values

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

Example 1

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


The above query will return the following output:

Pgsql
|
 Result 
--------
 f


Example 2

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


The above query will return the following output:

Pgsql
|
 Result 
--------
 f


Case #3: Analyzing Data Completeness

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

Pgsql
|
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.

Pgsql
|
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.

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




Updated 11 Sep 2023
Did this page help you?
PREVIOUS
IS DISTINCT FROM Operator
NEXT
JSON Functions
Docs powered by
Archbee
TABLE OF CONTENTS
Overview
Syntax
Examples
Case #1: Basic Usage
Case #2: Comparing NULL Values
Case #3: Analyzing Data Completeness
Docs powered by
Archbee

Dedicated to data analytics that provides 10x faster execution of analytical queries than current state of the art solutions. We are launching SOON! Join the waiting list for more info.




©2023 Oxla