0Pricing
SQL Academy · Lesson

NULLs in Aggregates and Joins

How NULL behaves in COUNT, SUM and JOINs.

NULLs in Aggregates and Joins is a free SQL Academy lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

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;

AVG Skips NULLs Too

AVG divides the sum of non-NULL values by the count of non-NULL values. NULLs are excluded from both.

This matters: an average over {100, NULL, 200} is 150, not 100 — the NULL is not counted as a zero.

-- (100 + 200) / 2 = 150, the NULL row is ignored
SELECT AVG(amount) AS avg_amount FROM payments;

-- If you WANT NULLs counted as 0, COALESCE first:
SELECT AVG(COALESCE(amount, 0)) AS avg_with_zeros FROM payments; -- 100

COUNT(*) vs COUNT(column)

This distinction trips up many people:

  • COUNT(*) counts rows, including those with NULLs.
  • COUNT(column) counts only rows where that column is not NULL.
-- 3 rows total, but only 2 have a non-NULL amount
SELECT
  COUNT(*)       AS row_count,    -- 3
  COUNT(amount)  AS has_amount    -- 2
FROM payments;

COUNT(DISTINCT) and NULL

COUNT(DISTINCT col) counts the number of distinct non-NULL values. NULLs are excluded entirely — they never add to the distinct count.

Keep this in mind when measuring "how many unique X".

-- statuses: 'paid', NULL, 'paid', 'void'
SELECT COUNT(DISTINCT status) AS distinct_statuses
FROM payments;
-- 2  (paid, void) -- NULL not counted

Empty Set Aggregates

When an aggregate runs over zero rows, the result depends on the function:

  • COUNT(...) returns 0.
  • SUM, AVG, MIN, MAX return NULL.

Use COALESCE to turn a NULL sum into 0 when appropriate.

-- No rows match -> SUM is NULL, not 0
SELECT COALESCE(SUM(amount), 0) AS total
FROM payments
WHERE status = 'refunded';  -- no such rows

GROUP BY Groups NULLs Together

Although NULL = NULL is unknown elsewhere, GROUP BY places all NULLs into a single group.

So a NULL category becomes its own bucket in the results, letting you summarize the missing-data rows together.

SELECT category, COUNT(*) AS n
FROM products
GROUP BY category;

-- category | n
-- ---------+---
-- books    | 5
-- toys     | 3
-- NULL     | 2   <- all NULL categories in one group

NULLs from Outer Joins

Outer joins are a major source of NULLs. A LEFT JOIN keeps every left row; where there's no match on the right, the right-side columns become NULL.

Those NULLs mean "no matching row", not "a stored NULL value".

SELECT c.name, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

-- name  | order_id
-- ------+---------
-- Alice | 10
-- Bob   | NULL     <- Bob has no orders

Counting Matches After a LEFT JOIN

To count only real matches after a LEFT JOIN, count a non-null column from the right table, not COUNT(*).

COUNT(o.id) ignores the NULL rows produced by unmatched left rows, giving the true number of orders.

SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;

-- Bob shows 0, not 1×NULL

Filtering Out the Non-Matches

A subtle gotcha: putting a condition on the right table in WHERE after a LEFT JOIN turns it into an inner join, because NULL = value is unknown and gets filtered out.

If you want to keep unmatched rows, put the condition in the ON clause or check for NULL explicitly.

-- Accidentally drops Bob (his o.status is NULL)
SELECT c.name FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'open';

-- Keep unmatched rows: move the test into ON
SELECT c.name FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'open';

Rules of Thumb

Carry these rules into every aggregate-and-join query:

  • Aggregates ignore NULLs (except COUNT(*)).
  • COUNT(col) < COUNT(*) when col has NULLs.
  • Empty-set SUM/AVG is NULL — wrap in COALESCE.
  • LEFT JOIN produces NULLs for non-matches; count a right-side key.
  • Right-table filters belong in ON, not WHERE.
SELECT c.name,
  COALESCE(SUM(o.amount), 0) AS spent,
  COUNT(o.id)                AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;

Quick Check

A column amount has values 100, NULL, and 200 across three rows. What do COUNT(*) and COUNT(amount) return?

Recap

You learned how NULLs flow through aggregation and joins: aggregates skip NULLs, COUNT(*) counts rows while COUNT(col) counts non-NULLs, empty-set sums are NULL, and GROUP BY bundles NULLs into one group.

You also saw that outer joins generate NULLs for non-matches, and why right-table filters belong in ON. That completes the Working with NULLs course — you can now handle missing data confidently.

-- A NULL-safe summary query
SELECT c.name,
  COUNT(o.id)                AS orders,
  COALESCE(SUM(o.amount), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name
ORDER BY total_spent DESC;

Frequently asked questions

Is the “NULLs in Aggregates and Joins” lesson free?

Yes — the full text of “NULLs in Aggregates and Joins” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “NULLs in Aggregates and Joins”?

How NULL behaves in COUNT, SUM and JOINs. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “NULLs in Aggregates and Joins” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

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