SQL INNER JOIN and LEFT JOIN: include learners with no enrolments

Connect tables through an explicit relationship

students describes learners, courses describes courses and enrollments connects a learner to a course. Asha has two enrolments, while Kiran and Kabir have none. Joining students directly to courses without the relationship table would not tell you which learner chose which course. State the matching keys in ON rather than assuming similarly positioned rows belong together.

Aliases such as s and e shorten column references and make the source of a field visible. A join can multiply rows: a student with two matching enrolments appears twice before grouping. That behaviour is correct for an enrolment-level result. It becomes a bug when a report expects one row per student but silently treats the joined rows as distinct students.

Preserve unmatched rows when the question requires them

An INNER JOIN keeps matching combinations. A LEFT JOIN keeps every row from its left input, adding null-valued right-side columns when no match exists. The example starts from students and left-joins enrollments because the report must include students who have no enrolments. Grouping by student ID and name then restores one result row per student.

COUNT(e.course_id) counts actual non-null matching course IDs. COUNT(*) would count the null-extended row for an unmatched student and incorrectly display one enrolment for Kiran and Kabir. The setup requires a real enrolment's course_id to be non-null, so this column is suitable for distinguishing a match.

Be careful with conditions after a left join

A WHERE condition that requires a right-side value can discard the null-extended rows and undo the intended preservation. If the question asks for all students and only certain matching enrolments, decide whether the condition belongs in ON. First write the expected answer for an unmatched student, then verify that the query still produces it.

For a multi-table report, check the intended grain at every step: student, enrolment or course. Add a unique key to a selected row when names can repeat. In this dataset the display names are distinct, but grouping only by name would merge different real learners with the same name in a larger database.

Run the worked example

PRAGMA foreign_keys = ON;
CREATE TABLE students (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    city TEXT,
    score INTEGER NOT NULL CHECK (score BETWEEN 0 AND 100),
    cohort TEXT NOT NULL
);
CREATE TABLE courses (id INTEGER PRIMARY KEY, title TEXT NOT NULL UNIQUE);
CREATE TABLE enrollments (
    student_id INTEGER NOT NULL REFERENCES students(id),
    course_id INTEGER NOT NULL REFERENCES courses(id),
    PRIMARY KEY (student_id, course_id)
);
INSERT INTO students VALUES
    (1, 'Asha', 'Jaipur', 82, 'Java'),
    (2, 'Aman', 'Ajmer', 66, 'Java'),
    (3, 'Jaya', 'Jaipur', 94, 'SQL'),
    (4, 'Kiran', NULL, 48, 'SQL'),
    (5, 'Neha', 'Kota', 82, 'SQL'),
    (6, 'Kabir', 'Jaipur', 40, 'Java');
INSERT INTO courses VALUES (10, 'Java'), (20, 'SQL'), (30, 'Python');
INSERT INTO enrollments VALUES (1, 10), (1, 20), (2, 10), (3, 20), (5, 20);

Download setup.sql

SELECT s.name, COUNT(e.course_id) AS enrolled_courses
FROM students AS s
LEFT JOIN enrollments AS e ON e.student_id = s.id
GROUP BY s.id, s.name
ORDER BY s.id;

Expected result

1. Asha contributes two matching enrolment rows.
2. Kiran and Kabir remain in the result with null right-side values.
3. Counting the non-null course ID produces zero for those unmatched students.

Try it yourself

List the names of students enrolled in the SQL course. Join all three tables and order by student ID.

SELECT s.name
FROM students AS s
JOIN enrollments AS e ON e.student_id = s.id
JOIN courses AS c ON c.id = e.course_id
WHERE c.title = 'SQL'
ORDER BY s.id;

Quick check

Choose an answer, then check the explanation. Your answer stays in this page and is not saved as a student record.

Keep learning

Explain the result in your own words, change one input and predict the new output. For additional structured assignments, explore the workbook sample before requesting the full pack.