0Pricing
SQL Interview Prep · Lesson

NULLs in Aggregates, Joins and DISTINCT

How NULL behaves differently across grouping, joining, and uniqueness.

NULLs in Aggregates, Joins and DISTINCT is a free SQL Interview Prep 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 Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

NULL in Three Surprising Places

NULL does not behave the same everywhere. The final lesson covers the three contexts where its behavior surprises candidates the most: aggregates, joins, and DISTINCT / GROUP BY.

The recurring twist is that aggregates and filtering treat NULL as 'skip me', but grouping and DISTINCT treat NULL as 'a value that equals other NULLs'. That inconsistency is exactly what interviewers probe.

Master these and you have closed the loop on the most common NULL questions in SQL screens.

Aggregates Ignore NULL

The headline rule: aggregate functions skip NULLs. SUM, AVG, MIN, MAX, and COUNT(column) all ignore NULL inputs entirely rather than treating them as zero.

This is why AVG can return a different number than you expect. It divides the sum of non-NULL values by the count of non-NULL values, not by the total row count.

-- bonus values: 100, 200, NULL
SELECT
  SUM(bonus) AS total,   -- 300 (NULL ignored)
  AVG(bonus) AS average, -- 150 = 300 / 2, not / 3
  COUNT(bonus) AS cnt    -- 2 (NULL not counted)
FROM employees;

COUNT(*) vs COUNT(column)

The most-asked aggregate-NULL question. COUNT(*) counts rows, including those with NULLs. COUNT(column) counts only rows where that column is non-NULL.

So the difference between them is precisely the number of NULLs in that column. COUNT(DISTINCT column) goes further and also ignores NULL while removing duplicates.

SELECT
  COUNT(*)              AS rows_total,    -- all rows
  COUNT(bonus)          AS non_null_bonus, -- excludes NULLs
  COUNT(DISTINCT bonus) AS distinct_bonus, -- excludes NULLs + dups
  COUNT(*) - COUNT(bonus) AS null_bonus
FROM employees;

AVG vs SUM/COUNT(*): A Classic Trap

Interviewers ask: 'Is AVG(x) the same as SUM(x) / COUNT(*)?' The answer is no when NULLs are present.

AVG(x) equals SUM(x) / COUNT(x), dividing by the non-NULL count. Dividing by COUNT(*) instead treats NULLs as if they were zero, deflating the average.

If you actually want NULLs counted as zero, you must say so explicitly with COALESCE.

-- These differ when bonus has NULLs:
SELECT
  AVG(bonus)                       AS avg_ignoring_nulls,
  SUM(bonus) * 1.0 / COUNT(*)      AS avg_nulls_as_zero,
  AVG(COALESCE(bonus, 0))          AS explicit_nulls_as_zero
FROM employees;

The All-NULL Aggregate Edge Case

What does an aggregate return when every input is NULL, or there are no rows? A precise distinction interviewers like:

  • SUM, AVG, MIN, MAX over all-NULL (or zero) rows return NULL.
  • COUNT always returns 0, never NULL.

So if a report shows blank totals, an all-NULL SUM is a likely cause. Wrap it in COALESCE to show 0.

-- No matching rows or all bonuses NULL:
SELECT SUM(bonus) FROM employees WHERE 1 = 0;  -- NULL
SELECT COUNT(bonus) FROM employees WHERE 1 = 0; -- 0

-- Present a clean zero:
SELECT COALESCE(SUM(bonus), 0) FROM employees;

NULL in JOIN Conditions

In a join's ON clause, NULL = NULL is still UNKNOWN, so NULL keys never match in an equi-join. Two rows that both have a NULL join key will not be paired.

This catches people joining on optional foreign keys. If matching NULL-to-NULL is the intended behavior, you need a NULL-safe operator (IS NOT DISTINCT FROM or <=>) from the earlier lesson.

-- Rows with region IS NULL on both sides do NOT match
SELECT *
FROM a JOIN b ON a.region = b.region;

-- To match NULL-to-NULL (ANSI):
SELECT *
FROM a JOIN b ON a.region IS NOT DISTINCT FROM b.region;

NULLs Produced by Outer Joins

Outer joins generate NULLs for unmatched rows. After a LEFT JOIN, every right-side column is NULL for left rows that found no match.

This is the foundation of the anti-join pattern: filter WHERE right_table.key IS NULL to find rows with no match, such as customers with no orders.

Be careful, though: filtering an outer-joined column in WHERE can accidentally convert it back to an inner join, the topic of the next scene.

-- Find customers who have never ordered (anti-join)
SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

The WHERE-on-Outer-Join NULL Trap

A favorite gotcha. You LEFT JOIN orders, then add WHERE o.status = 'shipped'. Suddenly customers with no orders disappear, turning your outer join into an effective inner join.

Why? For unmatched rows o.status is NULL, and NULL = 'shipped' is UNKNOWN, so WHERE drops them. To preserve unmatched rows, move the condition into the ON clause instead.

-- Accidental inner join: drops customers with no orders
SELECT c.name, o.status
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'shipped';

-- Correct: keep unmatched customers
SELECT c.name, o.status
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.id AND o.status = 'shipped';

DISTINCT Treats All NULLs as Equal

Here is the inconsistency that surprises everyone. Aggregates skip NULL, but DISTINCT keeps exactly one NULL, treating all NULLs as duplicates of each other.

So SELECT DISTINCT bonus over values 100, 100, NULL, NULL returns three rows: 100, NULL, and that's it. The two NULLs collapse into one, even though NULL = NULL is UNKNOWN elsewhere.

-- bonus: 100, 100, NULL, NULL, 200
SELECT DISTINCT bonus FROM employees;
-- Returns: 100, 200, NULL  (the two NULLs become one row)

GROUP BY Folds NULLs Into One Group

GROUP BY follows the same rule as DISTINCT: all NULL keys are gathered into a single group. This is opposite to comparison logic, where NULLs never equal each other.

So grouping by a nullable column gives you one row representing all the NULL-keyed records, which is usually what you want for reporting. Mention this contrast (grouping vs comparison) to show depth.

-- All employees with NULL department form ONE group
SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department;
-- A single row where department is NULL totals all of them

Interview Talking Points

The unifying summary that impresses interviewers:

  • Aggregates ignore NULL; AVG divides by COUNT(column), not COUNT(*).
  • COUNT(*) counts rows; COUNT(col) and COUNT(DISTINCT col) skip NULL.
  • SUM/AVG/MIN/MAX over no rows return NULL; COUNT returns 0.
  • In joins, NULL keys never match; filtering an outer-joined column in WHERE silently becomes an inner join.
  • DISTINCT and GROUP BY treat all NULLs as equal, the opposite of comparison logic.

The one-liner: 'NULL is ignored when aggregating and comparing, but grouped together when deduplicating.'

Quick Check

Test the grouping-vs-aggregation contrast.

Recap

You have completed NULL handling for interviews:

  • Aggregates skip NULL; AVG divides by the non-NULL count, and an all-NULL SUM is NULL while COUNT is 0.
  • COUNT(*) includes NULL rows; COUNT(col) does not, and the gap equals the NULL count.
  • Join keys that are NULL never match; filtering outer-joined columns in WHERE can collapse to an inner join.
  • DISTINCT and GROUP BY fold all NULLs into one, the reverse of comparison logic.

Remember the mantra: NULL is ignored when aggregating and comparing, but grouped together when deduplicating. That single insight answers most NULL interview questions.

Frequently asked questions

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

Yes — the full text of “NULLs in Aggregates, Joins and DISTINCT” is free to read here on the web, and the SQL Interview Prep 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 Interview Prep course, upgrade to CoddyKit PRO.

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

How NULL behaves differently across grouping, joining, and uniqueness. You practise SQL Interview Prep 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 Interview Prep?

No prior experience is required. SQL Interview Prep 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, Joins and DISTINCT” 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 Interview Prep lesson?

Yes. Every SQL Interview Prep 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. Three-Valued Logic and UNKNOWN
  2. IS NULL, IS NOT NULL and NULL-Safe Equality
  3. COALESCE, NULLIF and ISNULL
  4. NULLs in Aggregates, Joins and DISTINCT
← Back to SQL Interview Prep