August 1, 2023
Summary: in this tutorial, you will learn how to use the PostgreSQL IS NULL
operator to check if a value is NULL or not.
Table of Contents
Introduction to NULL
and IS NULL
operator
In the database world, NULL means missing information or not applicable. NULL is not a value, therefore, you cannot compare it with any other values like numbers or strings. The comparison of NULL with a value will always result in NULL, which means an unknown result.
In addition, NULL is not equal to NULL so the following expression returns NULL:
NULL = NULL
Assuming that you have a contacts
table that stores the first name, last name, email, and phone number of contacts. At the time of recording the contact, you may not know the contact’s phone number.
To deal with this, you define the phone
column as a nullable column and insert NULL into the phone
column when you save the contact information.
CREATE TABLE contacts(
id INT GENERATED BY DEFAULT AS IDENTITY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(255) NOT NULL,
phone VARCHAR(15),
PRIMARY KEY (id)
);
Note that you will learn how to create a new table in the subsequent tutorial. For now, you just need to execute the above statement to create the
contacts
table.
If you get an error while executing the CREATE TABLE
statement, your PostgreSQL version may not support the identity column syntax. In this case, you can use the following statement:
CREATE TABLE contacts(
id SERIAL,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(255) NOT NULL,
phone VARCHAR(15),
PRIMARY KEY (id)
);
The following statement inserts two contacts, one has a phone number and the other does not:
INSERT INTO contacts(first_name, last_name, email, phone)
VALUES ('John','Doe','john.doe@rockdata.net',NULL),
('Lily','Bush','lily.bush@rockdata.net','(408-234-2764)');
To find the contact who does not have a phone number you may come up with the following statement:
SELECT
id,
first_name,
last_name,
email,
phone
FROM
contacts
WHERE
phone = NULL;
The statement returns no row. This is because the expression phone = NULL
in the WHERE
clause always returns false.
Even though there is a NULL in the phone column, the expression NULL = NULL
returns false. This is because NULL is not equal to any value even itself.
To check whether a value is NULL or not, you use the IS NULL
operator instead:
value IS NULL
The expression returns true if the value is NULL or false if it is not.
So to get the contact who does not have any phone number stored in the phone column, you use the following statement instead:
SELECT
id,
first_name,
last_name,
email,
phone
FROM
contacts
WHERE
phone IS NULL;
Here is the output:
PostgreSQL IS NOT NULL
operator
To check if a value is not NULL, you use the IS NOT NULL
operator:
value IS NOT NULL
The expression returns true if the value is not NULL or false if the value is NULL.
For example, to find the contact who does have a phone number, you use the following statement:
SELECT
id,
first_name,
last_name,
email,
phone
FROM
contacts
WHERE
phone IS NOT NULL;
The output is:
In this tutorial, you have learned how to use the PostgreSQL IS NULL
operator to check whether a value is NULL or not.