Using ‘IS NULL’ and ‘IS NOT NULL’ in PostgreSQL

Overview

With PostgreSQL, checking for NULL values is a common part of querying. Through ‘IS NULL’ and ‘IS NOT NULL’ operators, this tutorial will guide you on managing null values in your database, ensuring data integrity and informed decision-making.

Understanding NULL in PostgreSQL

In PostgreSQL, a NULL value signifies that the value is unknown or missing. NULL is not equivalent to zero or an empty string; it’s a marker for the absence of a value. Distinguishing between NULL and actual values is crucial for accurate data handling.

Simple ‘IS NULL’ Example

Let’s begin with a straightforward example where we want to find records with a NULL value in a certain column:

SELECT *
FROM products
WHERE price IS NULL;

This query fetches all rows from the ‘products’ table where the ‘price’ column has no assigned value.

Simple ‘IS NOT NULL’ Example

Conversely, the ‘IS NOT NULL’ operator is used to find rows where a column has a non-NULL value:

SELECT *
FROM products
WHERE price IS NOT NULL;

Working with Joins and NULL Values

When joining tables, you may encounter NULL values. To handle this, ensure the join condition includes checking for NULL values. Consider two tables: ‘orders’ and ‘customers’, where some orders may not be linked to a customer account:

SELECT o.order_id, c.name
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL;

This query will return orders that do not have an associated customer in the ‘customers’ table.

Using ‘IS NULL’ with Conditional Logic

PostgreSQL’s CASE statement can be combined with ‘IS NULL’ to handle conditional logic:

SELECT id, title,
CASE
    WHEN description IS NULL THEN 'No description available'
    ELSE description
END AS description
FROM books;

This SELECT query provides a default text if the ‘description’ column contains a NULL value.

Filtering Aggregated Data with NULL Values

Aggregating data often involves ignoring NULL values. This is because aggregate functions typically skip NULL. However, if you want to consider NULL as zero or another specific value, the COALESCE function can come in handy:

SELECT category, COUNT(*) AS total,
SUM(COALESCE(price, 0)) AS total_sales
FROM products
GROUP BY category;

Here, COALESCE is used to treat NULL price values as zero when summing up total sales.

Indexing Columns with NULL Values

Indexes in PostgreSQL by default do not include NULL values. If you regularly search for NULL values, consider creating a partial index:

CREATE INDEX idx_price_null ON products (price)
WHERE price IS NULL;

This index optimizes queries that specifically look for products with a NULL price value.

Using ‘NOT EXISTS’ with NULL Checks

Another approach for dealing with NULL values involves using the ‘NOT EXISTS’ clause:

SELECT *
FROM products p
WHERE NOT EXISTS (
    SELECT 1
    FROM inventory i
    WHERE i.product_id = p.id
    AND i.quantity IS NULL
);

This example finds all products that do not have an inventory record with a NULL quantity.

Advanced NULL Check with Subqueries

Subqueries can be used to perform more complex NULL value checks. An example would be to find customers who have never placed an order:

SELECT *
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

The NOT EXISTS subquery checks for the absence of any related orders for each customer.

NULL Values in Array Comparisons

When comparing array elements with potential NULL values, you may need additional checks due to the way PostgreSQL handles arrays and NULLs:

SELECT *
FROM teams
WHERE NOT array[NULL] <@ team_members;

This query utilizes the ‘is not contained by’ array operator to exclude teams that have NULL as a member.

NULL Handling in The ‘FILTER’ Clause for Aggregate Functions

The ‘FILTER’ clause can refine aggregate functions by specifying conditions to include or exclude certain rows. Here’s how you can use it to count only non-NULL values:

SELECT COUNT(*) FILTER (WHERE price IS NOT NULL) AS non_null_prices
FROM products;

This query provides a count of all non-null prices in the ‘products’ table.

Conclusion

Mastering ‘IS NULL’ and ‘IS NOT NULL’ in PostgreSQL allows you to write more expressive and accurate queries, particularly in the handling of absent data. By incorporating these statements into your SQL strategy, you’ll enhance data analysis and ensure better data management practices.