PostgreSQL - IS NULL operator

The PostgreSQL IS NULL operator is used to check whether a value is NULL. In the context of databases, NULL indicates that data is either missing or not applicable. Since NULL cannot be compared directly with any integer or string (as such comparisons result in NULL, meaning an unknown result), the IS NULL operator is crucial for identifying NULL values.

Syntax

Value IS NULL;

PostgreSQL IS NULL operator Examples

For the sake of this article, we will be using the sample DVD rental database, which is explained here and can be downloaded by clicking on this link in our examples.

Let us take a look at some of the examples of the IS NULL operator in PostgreSQL to better understand the concept.

Example 1: Find Customers Without Email Addresses

Here we will make a query to find all the 'first_name' and 'last_name' of customers that don't have an "email" in the "customer" table of our sample database.

Query:

SELECT
      first_name,
      last_name
FROM
      customer
WHERE
      email IS NULL;

Output:

Explanation: This query selects the 'first_name' and 'last_name' of customers whose email field is NULL. This is useful for identifying customers who haven't provided an email address.

Example 2: Find Films Without Release Year

Here we will make a query to find all the "title" of the films that don't have a "release_year" in the "film" table of our sample database.

Query:

SELECT
      title
FROM
      film
WHERE
      release_year IS NULL;

Output:

Explanation: This query retrieves the titles of films where the 'release_year' is NULL. This can help identify films that are missing release year information.

Important Points PostgreSQL IS NULL operator

• The IS NULL operator is used to check whether a value in a column is NULL.
• Avoid using equality operators (= or !=) with NULL. Always use IS NULL or IS NOT NULL.
• Indexes on columns containing 'NULL' values can affect query performance. Proper indexing strategies are crucial.
• In the context of databases, NULL represents missing, undefined, or not applicable data. It is different from an empty string or zero.