New developers often get confused between NULL, empty and zero values. However, it is not that complicated. In this tutorial, we will be learning what is the NULL value and what is the IS NULL operator in PostgreSQL. So, let’s get started!
Introduction to PostgreSQL IS NULL Operator
In any programming language, the NULL value represents the non-existent value. It is totally different from the zero or the empty value.
In SQL, when you don’t assign any value to the column, it becomes NULL. Simply, the value that doesn’t exist is the NULL.
In PostgreSQL, when we don’t assign any value to the column, it becomes NULL. Now, your table may or may not contain the NULL values depending on the table schema.
Note that, you can assign the NULL value intentionally as well.
The IS NULL operator in PostgreSQL checks if there is any record that doesn’t have any value against a particular column.
Note that, because the NULL is not considered a value in the traditional sense. It simply means the value is not present or the value is unknown. Therefore, you can not compare the NULL with the records to find the null column values. Therefore SQL provides us with the IS NULL operator.
Now let’s see how to use the IS NULL operator.
Syntax of PostgreSQL IS NULL Operator
The following syntax shows how you can use the IS NULL operator.
col | expression IS NULL;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Here, you can specify the column name or an expression to check if it is null. You can use the IS NULL operator with SELECT, UPDATE or DELETE statements.
The above syntax will return all the records that don’t have value in the table for the specified column.
PostgreSQL IS NOT NULL Operator:
On the contrary, PostgreSQL provides us with the IS NOT NULL operator which checks if the value is not null. Check the below syntax.
col | expression IS NOT NULL;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)This syntax will return all the records which have the values present in the specified column. All other records will be skipped.
If you check any value against the NULL using the equality operator (=), PostgreSQL will return no rows, because comparing any value with the NULL using the equality operator will result in null result.
Let’s now see some practical examples of using the IS NULL operator.
Examples of PostgreSQL IS NULL Operator
Before moving forward, we will need a table first. So, let’s create it.
-- Create the "customers" table
DROP TABLE IF EXISTS customers;
CREATE TABLE customers (
id serial PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
birth_date DATE,
address TEXT
);
-- Insert five values into the "customers" table
INSERT INTO customers (first_name, last_name, birth_date, address)
VALUES
('John', 'Doe', '1990-05-15', '123 Main St'),
('Alice', 'Smith', '1985-12-10', NULL), -- Leave address empty (NULL)
('Bob', 'Johnson', '1992-08-22', NULL), -- Leave address empty (NULL)
('Eva', 'Brown', '1988-04-30', '456 Elm St'),
('Michael', 'Wilson', '1995-07-17', '789 Oak St');
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
Perfect! Let’s now see some examples.
Comparing Values Using Equality Operator (=)
As we have discussed earlier, if you compare the expression with the equal operator, the result will be null. Let’s try and see the result.
SELECT * FROM customers
WHERE address = NULL;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
As you can see here, we get zero rows even though there are records present with the NULL values. This simply means, that comparing the values with the NULL using the equal operator will always return a null result.
Also Read: PostgreSQL SELECT Statement
Using IS NULL Operator in the SELECT Statement
Let’s now fetch all the customers that don’t have addresses present in the table.
SELECT * FROM customers
WHERE address IS NULL;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
Using IS NULL Operator in the UPDATE Statement
Now we will use the IS NULL operator with the update statement to find and update the records having null values in the table.
UPDATE customers
SET address='73 Jane St'
WHERE first_name='Alice' AND address IS NULL;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
Using IS NOT NULL Operator
Using the IS NOT NULL operator is exactly the same as the IS NULL operator. It returns all the records having non-null values. Let’s take an example.
We want to see all the customers who have addresses set in the table.
SELECT * FROM customers
WHERE address IS NOT NULL;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
As you can see, all the records in the result have the address value set in the table.
Read More: PostgreSQL DROP TABLE
Conclusion
In this tutorial, we have learned what is a NULL value, and how it is different from empty and zero values along with the IS NULL and IS NOT NULL operators. This small-looking operator is indeed a very powerful and useful tool in PostgreSQL and you will be using it a lot in any real-time application. You can make maximum use of it by combining multiple conditions using the AND and OR operators. However, be careful while using it with the DML statements.