Before this lesson

Understand primary keys, the idea of foreign keys and aggregation. Distinguish course entities from activity records.

What you will be able to do

Join courses to study records while retaining courses not yet started.

Understand the mechanism

One courses row represents a course and one sessions row represents a study session. A one-to-many join repeats a course across rows because of join grain, not necessarily duplicated source data.

INNER JOIN keeps matches. LEFT JOIN retains every left row and fills unmatched right columns with NULL. Count s.id rather than COUNT(*) to avoid counting a placeholder as a session.

A right-table condition in WHERE can remove unmatched rows. To retain all courses while matching only qualifying records, place that condition in ON and inspect the aggregates.

Runnable example

This course uses SQLite 3. Open a fresh in-memory database, for example with sqlite3 :memory:, and run the complete script. Each lesson supplies its own schema and sample data, independent of the website database.

CREATE TABLE courses(id INTEGER PRIMARY KEY,name TEXT);
CREATE TABLE sessions(id INTEGER PRIMARY KEY,course_id INTEGER,minutes INTEGER);
INSERT INTO courses VALUES(1,'Python'),(2,'SQL'),(3,'Java');
INSERT INTO sessions VALUES(1,1,25),(2,1,35),(3,2,20);
SELECT c.name,COUNT(s.id) AS sessions,COALESCE(SUM(s.minutes),0) AS total
FROM courses c LEFT JOIN sessions s ON s.course_id=c.id
GROUP BY c.id,c.name ORDER BY c.id;

Trace and verify

Python has 2 sessions and 60 minutes, SQL 1 and 20, Java 0 and 0. Here zero deliberately represents no study records.

Common mistakes and boundaries

Joining two one-to-many detail tables can multiply rows and double-count totals. Aggregate each to course grain before joining summaries.

Practice challenge

Count only sessions of at least 30 minutes while still showing every course.

Reference solution and reasoning

Add AND s.minutes>=30 to ON. Python becomes 1 session and 35 minutes; SQL and Java remain with zero. WHERE would remove those two courses.

Lab: a WHERE condition can defeat a LEFT JOIN

A report must include courses with zero completions. Filtering the right table in WHERE after a LEFT JOIN can remove the generated NULL rows. Compare the predicate locations and count expressions in a fresh SQLite database.

CREATE TABLE course(id INTEGER PRIMARY KEY, title TEXT);
CREATE TABLE attempt(id INTEGER PRIMARY KEY, course_id INTEGER, status TEXT);
INSERT INTO course VALUES(1,'HTML'),(2,'CSS'),(3,'SQL');
INSERT INTO attempt VALUES(10,1,'done'),(11,1,'todo'),(12,2,'todo');
SELECT c.title, COUNT(a.id) AS completed
FROM course c LEFT JOIN attempt a
ON a.course_id = c.id AND a.status = 'done'
GROUP BY c.id, c.title ORDER BY c.id;
SELECT c.title, COUNT(a.id) AS completed
FROM course c LEFT JOIN attempt a ON a.course_id = c.id
WHERE a.status = 'done'
GROUP BY c.id, c.title ORDER BY c.id;

Expected observations

The first report returns HTML|1, CSS|0 and SQL|0. The second contains only HTML|1.

Why this happens and where it stops applying

ON selects eligible right-side matches while preserving unmatched courses. WHERE filters the resulting rows, and NULL = done is not TRUE. COUNT(a.id) ignores null-extended rows; COUNT(*) counts them. Adding another one-to-many join can multiply attempts further. Aggregate each fact table at the intended course grain before joining, rather than adding DISTINCT without understanding the multiplication.

Extension challenge

To display both total attempts and completed attempts, can ON still exclude all non-done attempts?

Reasoned solution

No; that would also reduce the total to completions. Join all attempts, count a.id for the total and sum CASE WHEN a.status = 'done' THEN 1 ELSE 0 END for completions. Verify both values are zero for a course with no attempts.

Further reading