SQL joins explained with one small database

Most real questions need data from more than one table. Which students took which course? Which courses have nobody enrolled? Joins answer those questions. We will use three tables.

students(id, name)
courses(id, title)
enrollments(student_id, course_id, enrolled_on)

INNER JOIN: rows that match on both sides

SELECT s.name, c.title
FROM enrollments e
JOIN students s ON s.id = e.student_id
JOIN courses c ON c.id = e.course_id;

You get one row per enrollment. Students with no enrollments do not appear, and neither do courses with no students.

LEFT JOIN: keep every row from the left table

SELECT c.title, COUNT(e.student_id) AS students
FROM courses c
LEFT JOIN enrollments e ON e.course_id = c.id
GROUP BY c.id, c.title;

Courses with nobody enrolled now show up with a count of 0. Group by c.id as well as the title, or two courses with the same title get merged into one row. Use COUNT on a column from the right table. COUNT(*) would count the empty row as 1.

Find missing rows

To list courses with no enrollments, keep the LEFT JOIN and filter for NULL:

SELECT c.title
FROM courses c
LEFT JOIN enrollments e ON e.course_id = c.id
WHERE e.course_id IS NULL;

Self join: a table joined to itself

Add a referred_by column to students. To show each student with the name of the person who referred them, join students to students under two aliases:

SELECT s.name, r.name AS referred_by
FROM students s
LEFT JOIN students r ON r.id = s.referred_by;

Practice

Create these three tables in SQLite, add ten rows to each and write five questions about them before you write any SQL. Then answer each question with a query. Writing the questions first trains the skill you use at work.