SQL IS NULL and IS NOT NULL: Find Missing Values

NULL is a marker for "no value here". It is different from zero and from an empty string: a salary of 0 is a known number, while a NULL salary has not been recorded. In the Playground, an employee with no manager has NULL in manager_id. Find those employees with IS NULL:

SELECT first_name, last_name, title
FROM employees
WHERE manager_id IS NULL
ORDER BY last_name, first_name;

▶ Run this query in the SQL Playground

10 of the 120 employees have no manager recorded.

WHERE column_name IS NULL      -- value is missing
WHERE column_name IS NOT NULL  -- value is present

It is tempting to write manager_id = NULL. The query runs without an error, but it never finds a row:

SELECT COUNT(*) AS rows_found
FROM employees
WHERE manager_id = NULL;

▶ Run this query in the SQL Playground

The count is 0. Comparing anything with NULL gives the result unknown, not true, and WHERE only keeps rows where the condition is true. This applies to =, <>, < and > alike.

An order that has not shipped yet has NULL in shipped_date. COUNT(*) counts rows, while COUNT(column) counts only the rows where that column is not NULL, so one query shows both groups:

SELECT COUNT(*) AS all_orders,
       COUNT(shipped_date) AS shipped,
       COUNT(*) - COUNT(shipped_date) AS not_shipped
FROM orders;

▶ Run this query in the SQL Playground

To list the shipped orders themselves, filter with IS NOT NULL:

SELECT id, status, shipped_date
FROM orders
WHERE shipped_date IS NOT NULL
ORDER BY id
LIMIT 5;

▶ Run this query in the SQL Playground

Because a comparison with NULL is never true, rows with NULL drop out of filters that look as if they should cover everything. Split the employees by whether their manager is employee 3:

SELECT SUM(CASE WHEN manager_id = 3 THEN 1 ELSE 0 END) AS manager_is_3,
       SUM(CASE WHEN manager_id <> 3 THEN 1 ELSE 0 END) AS manager_is_not_3,
       SUM(CASE WHEN manager_id IS NULL THEN 1 ELSE 0 END) AS manager_is_null,
       COUNT(*) AS all_employees
FROM employees;

▶ Run this query in the SQL Playground

"Equal to 3" and "not equal to 3" together do not add up to 120. The 10 employees with no manager belong to neither group. To include them, say so: WHERE manager_id <> 3 OR manager_id IS NULL.

IS NULL is also how you find rows that have no partner in another table. A LEFT JOIN keeps every customer and fills the order columns with NULL when there is no order; filtering on that NULL leaves the customers who have never ordered:

SELECT u.id, u.first_name, u.last_name
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL
ORDER BY u.id
LIMIT 5;

▶ Run this query in the SQL Playground

PostgreSQL, Oracle and SQLite let you choose with ORDER BY column NULLS FIRST or NULLS LAST. To show a replacement value instead of NULL, use COALESCE.

Three errors that produce wrong results without any error message.

SELECT *
FROM employees
WHERE manager_id = NULL;

SELECT *
FROM employees
WHERE manager_id IS NULL;

SELECT *
FROM employees
WHERE manager_id <> 3;

SELECT *
FROM employees
WHERE manager_id <> 3
   OR manager_id IS NULL;

SELECT COUNT(shipped_date)
FROM orders;

SELECT COUNT(*)
FROM orders;

4 exercises on the real Playground tables.

Write each query yourself first. Every solution has been run against the Playground database.

1. 1Easy How many orders have not been shipped yet? Table: orders

   Show solution

   SQLite · runs in the Playground

   SELECT COUNT(*) AS not_shipped
   FROM orders
   WHERE shipped_date IS NULL;

   ▶ Run this query in the SQL Playground

   IS NULL keeps the orders with no shipped date.

2. 2Easy List the departments of employees who have no manager, with the number of such employees in each. Table: employees

   Show solution

   SQLite · runs in the Playground

   SELECT department, COUNT(*) AS employees
   FROM employees
   WHERE manager_id IS NULL
   GROUP BY department
   ORDER BY employees DESC, department;

   ▶ Run this query in the SQL Playground

   Filter with IS NULL first, then group the remaining rows by department.

3. 3Medium For each order status, show the number of orders and how many of them have a shipped date. Table: orders

   Show solution

   SQLite · runs in the Playground

   SELECT status,
          COUNT(*) AS orders,
          COUNT(shipped_date) AS with_shipped_date
   FROM orders
   GROUP BY status
   ORDER BY orders DESC;

   ▶ Run this query in the SQL Playground

   COUNT(*) counts all orders in the group; COUNT(shipped_date) counts only those where the date is present.

4. 4Medium Find five products that have no reviews. Table: products, reviews

   Show solution

   SQLite · runs in the Playground

   SELECT p.id, p.name
   FROM products p
   LEFT JOIN reviews r ON r.product_id = p.id
   WHERE r.id IS NULL
   ORDER BY p.id
   LIMIT 5;

   ▶ Run this query in the SQL Playground

   The LEFT JOIN keeps every product; rows where the review columns are NULL are products without a review.