SQL Joins: INNER JOIN and LEFT JOIN with Practice

Use a join when information lives in more than one table. Imagine a students table with names and a sessions table with completed study minutes. The common key is students.id = sessions.student_id.

Start with a small dataset

INNER JOIN: show matching records

SELECT students.name, sessions.minutes
FROM students
INNER JOIN sessions ON sessions.student_id = students.id
ORDER BY students.id, sessions.minutes;

Result: Amina appears twice (30 and 45), Bilal once (20), and Chen does not appear because Chen has no session. A join can return more than one row for the same student.

LEFT JOIN: keep every student

SELECT students.name, sessions.minutes
FROM students
LEFT JOIN sessions ON sessions.student_id = students.id
ORDER BY students.id, sessions.minutes;

Chen now appears with NULL minutes. The left table supplies a row even where the right table has no match.

Practice before checking the answer

Write a query that returns the name and number of sessions for every student, including Chen with zero sessions. Avoid COUNT(*), which would count Chen's unmatched left-join row.

SELECT students.name, COUNT(sessions.student_id) AS session_count
FROM students
LEFT JOIN sessions ON sessions.student_id = students.id
GROUP BY students.id, students.name
ORDER BY students.id;

Quick review

• Which join includes Chen?
• Why does Amina occur twice in the first result?
• Which column should the count use to leave Chen at zero?

Reference: PostgreSQL tutorial: joins between tables.