Loading published curriculum…

08 · SQL and JDBC / LESSON 30 OF 102

Joins, aggregation, and NULL semantics

A join combines related rows; its type determines which unmatched rows survive.

Before this lesson: Tables, keys, constraints, and normalization

Understand the topic

INNER JOIN keeps matches. LEFT JOIN preserves the left row and supplies NULL for missing right-side values. Putting a right-side predicate in WHERE can accidentally remove those unmatched rows; move the predicate into ON when preservation is intended.

COUNT(*) counts output rows, while COUNT(column) skips NULL values. SQL uses three-valued logic: compare nullable values with IS NULL, not = NULL. Group by the entity key when aggregating per owner.

EXAMPLE · SQL

owner-progress.sql

SELECT l.id, count(w.id) AS completed
FROM learners l
LEFT JOIN work_items w ON w.owner_id = l.id AND w.done = true
GROUP BY l.id
ORDER BY l.id;

What to expect

Every learner appears, including learners with zero completed tasks.

YOUR TURN

Put it into practice

  1. 01Insert an owner with no tasks and another with mixed task states.
  2. 02Move the done predicate from ON to WHERE and explain the changed result.
  3. 03Verify: The final progress report preserves owners with zero completed tasks.

Pass a quiz or complete a lab to check today’s streak. Each lesson counts once per day, including when you review it later.

UNDER THE HOOD

Build the mental model

A join combines related rows; its type determines which unmatched rows survive.

  1. 01INNER JOIN keeps matches. LEFT JOIN preserves the left row and supplies NULL for missing right-side values. Putting a right-side predicate in WHERE can accidentally remove those unmatched rows; move the predicate into ON when preservation is intended.
  2. 02COUNT(*) counts output rows, while COUNT(column) skips NULL values. SQL uses three-valued logic: compare nullable values with IS NULL, not = NULL. Group by the entity key when aggregating per owner.
  3. 03Trace the example: Every learner appears, including learners with zero completed tasks.

A mistake worth avoiding

Learners with no tasks disappear from the report.

DEBUGGING ROOM

Something broke. Where do you look?

Learners with no tasks disappear from the report.

Before revealing the diagnosis, write down your first investigation step.

CHECK YOUR UNDERSTANDING

Does COUNT(w.id) count NULL values introduced by a left join?

Choose one answer

BUILD IT YOURSELF

Insert an owner with no tasks and another with mixed task states.

  1. 1.Insert an owner with no tasks and another with mixed task states.
  2. 2.Move the done predicate from ON to WHERE and explain the changed result.
  3. 3.Reproduce this failure, then repair it: Learners with no tasks disappear from the report.

Acceptance checklist

Lab completion is your self-report. Knowledge checks do not certify project quality.

RETRIEVE IT FROM MEMORY

Does COUNT(w.id) count NULL values introduced by a left join?

Revisit this lesson in three days after a successful check or lab.

Ready for the next step?

The final progress report preserves owners with zero completed tasks.

Reading is separate from passing a knowledge check or completing a lab.

Go deeper · official references