0Pricing
SQL Academy · Lesson

NULLs in Aggregates and Joins

How NULL behaves in COUNT, SUM and JOINs.

NULLs Change the Math

Aggregate functions and joins both treat NULL in special ways. If you don't know the rules, your totals and counts can be quietly wrong.

This lesson shows how COUNT, SUM, AVG, GROUP BY, and outer joins all interact with missing values.

SELECT amount FROM payments;
-- amount
-- -------
--    100
--   NULL   <- missing
--    200

Aggregates Ignore NULLs

Most aggregates — SUM, AVG, MIN, MAX — simply skip NULL values. They aggregate only the rows that have data.

So a NULL amount doesn't break SUM; it's just left out of the total.

-- Using amounts 100, NULL, 200
SELECT
  SUM(amount) AS total,  -- 300 (NULL skipped)
  MIN(amount) AS lo,     -- 100
  MAX(amount) AS hi      -- 200
FROM payments;

All lessons in this course

  1. What NULL Really Means
  2. IS NULL and IS NOT NULL
  3. COALESCE and NULLIF
  4. NULLs in Aggregates and Joins
← Back to SQL Academy