SQL NULL Handling and COALESCE Explained

Three-valued logic

SQL conditions can be true, false or unknown. manager_id <> 1 is unknown for an employee whose manager_id is NULL, and WHERE keeps only rows where the condition is true. So rows with NULLs disappear from both = 1 and <> 1.

To include them, say so explicitly: manager_id <> 1 OR manager_id IS NULL.

Replacing NULLs

COALESCE takes any number of arguments and returns the first non-NULL one. It is the portable choice; MySQL also has IFNULL(a, b), which only takes two arguments and does not exist in PostgreSQL.

NULLIF(a, b) does the opposite: it returns NULL when a = b. Dividing by NULLIF(count, 0) returns NULL instead of an error when the count is zero.

The example tables

Every example on this page uses these tables. They are small on purpose, so you can check each result by eye.

Examples

SELECT e.name, COALESCE(d.dept_name, 'Unassigned') AS department
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
ORDER BY e.emp_id;

SELECT COUNT(*) AS all_rows,
       COUNT(manager_id) AS with_manager,
       COUNT(DISTINCT manager_id) AS managers
FROM employees;

SELECT name, manager_id
FROM employees
WHERE manager_id <> 1
ORDER BY emp_id;

SELECT name, manager_id
FROM employees
WHERE manager_id <> 1 OR manager_id IS NULL
ORDER BY emp_id;

Common mistakes

• Comparing with = NULL. Always use IS NULL or IS NOT NULL.
• NOT IN with a NULL in the list. x NOT IN (1, NULL) is never true, so the query returns nothing. Use NOT EXISTS, or filter NULLs out of the subquery.
• Averages that skip NULLs. AVG(bonus) divides by the number of non-NULL bonuses. If missing should count as zero, use AVG(COALESCE(bonus, 0)).

Practise NULL & COALESCE

Interview questions that use this concept, easiest first. Each one has the step-by-step visualizer and answer checking.

1. 1

   Inactive 2024 Signups

   List customers who registered in 2024 but have never made a purchase.

   Easy✓ Solved
2. 2

   Second Highest Delivery Fee

   Return the second-highest distinct delivery fee, or NULL if there isn't one.

   Easy✓ Solved
3. 3

   Authors With Five Published Books

   Find authors who have at least five published books in the catalog.

   Easy✓ Solved
4. 4

   Units Sold per Department

   Report total units sold for every department, including departments with no sales.

   Easy✓ Solved
5. 5

   Listing Booking Windows

   Count each listing's bookings in the last 90 days, the last 365 days and all time.

   Easy✓ Solved
6. 6

   Monthly Listing Ratings

   Average star rating for each listing in each month.

   Easy✓ Solved

Want a full set? Work through the SQL Basics study plan.

Questions people ask

Is COALESCE the same as IFNULL?

For two arguments they behave alike, but COALESCE accepts any number of arguments and works in both MySQL and PostgreSQL; IFNULL is MySQL only.

Are NULLs equal in GROUP BY and DISTINCT?

Yes. Although NULL = NULL is unknown in a WHERE clause, GROUP BY and DISTINCT treat all NULLs as one group.

Related concepts

• SQL WHERE, ORDER BY and LIMIT Explained
• SQL INNER JOIN vs LEFT JOIN Explained