Real-life NULL Handling Examples: Calculations, Filtering, Sorting

Working with NULL pops up in all sorts of scenarios: from dealing with missing data in reports to filtering and sorting. If you had to choose between having no value in a table and some weird number like 9999, most folks would pick NULL—yeah, it's not the most convenient, but at least it's honest. Let's check out a few typical cases.

Example: Sorting Products with Missing Prices

Imagine we're running an online store, and we've got a products table:

We want to sort products by price, but products without a price (NULL) should go at the end.

SELECT product_id, name, price
FROM products
ORDER BY price ASC NULLS LAST;

Result:

Notice the key part NULLS LAST. By default, PostgreSQL already puts NULL values at the end for ASC (NULLS LAST), and at the beginning for DESC (NULLS FIRST). Specifying NULLS LAST after ASC explicitly locks this behavior in and doesn't change the result — but it's useful when you want to make your intent explicit or override the default.

Example: Filtering Students Without a Birth Date

We've got a students table, and we want to select only those who don't have a birth date set.

Query:

SELECT student_id, name
FROM students
WHERE birth_date IS NULL;

Result:

We successfully pulled info about students whose birth date is unknown.

Examples of Using Functions to Handle NULL

Example: Calculating the Total Amount Considering Possible NULLs

The orders table stores order amounts. But sometimes the data isn't filled in, and we need to treat those cases as if the amount is 0.

Sample data:

Query:

SELECT SUM(COALESCE(order_amount, 0)) AS total_amount
FROM orders;

Result:

We use COALESCE(order_amount, 0) to swap NULL for 0 before summing. This helps avoid errors or wrong totals.

Example: Showing Text Instead of NULL

In the report, we need to show the text "Not specified" for all empty data instead of NULL.

SELECT
    customer_name, 
    COALESCE(order_amount::TEXT, 'Not specified') AS order_status
FROM orders;

Result:

COALESCE() lets you show whatever text you want if the value is NULL.

Trickier Scenarios with NULL

Our task is to sort orders so that orders with missing amounts come first, then by descending order from biggest to smallest amount.

SELECT customer_name, order_amount
FROM orders
ORDER BY order_amount DESC NULLS FIRST;

Result:

Here we used NULLS FIRST to put NULL values before everything else.

Example: Filtering Data with Replacing NULL Values

In some reports, you need to show only rows where the value is filled in or replace it with "Unknown" if it's NULL.

SELECT
    student_id, 
    name, 
    COALESCE(birth_date::TEXT, 'Unknown') AS birth_date_info
FROM students;

Result:

This is especially handy when making reports where it's important to show that data is missing.

Practical Tips

Working with NULL takes extra attention. Here are a few handy tips:

• Use IS NULL and COALESCE() to check for and replace missing values.
• Remember that aggregate functions ignore NULL, except for COUNT(*).
• For sorting, don't forget about the keywords NULLS FIRST and NULLS LAST.
• In reports, always say how you handle NULL so your teammates aren't confused.

Knowing this stuff will help you not just write solid queries, but also impress at interviews. Being able to handle real data is always valued more than just theory!

2

Task

SQL SELF, level 10, lesson 3

Locked

Filtering rows with NULL values

Filtering rows with NULL values

Open

2

Task

SQL SELF, level 10, lesson 3

Locked

Calculating the total amount with NULL handling

Calculating the total amount with NULL handling

Open