Joining Tables

Relational data is split across tables to avoid repetition. Joins put it back together for a query.

INNER JOIN

An inner join returns rows that match in both tables. Rows without a match disappear.

SELECT students.name, courses.title
FROM students
INNER JOIN enrolments ON enrolments.student_id = students.id
INNER JOIN courses ON courses.id = enrolments.course_id;

LEFT JOIN

A left join keeps every row from the left table, even when there is no match. Unmatched columns come back as NULL. This is how you find students with no enrolment.

SELECT students.name, courses.title
FROM students
LEFT JOIN enrolments ON enrolments.student_id = students.id
LEFT JOIN courses ON courses.id = enrolments.course_id;

Finding Missing Rows

Add a WHERE clause that checks for NULL on the right table.

SELECT students.name
FROM students
LEFT JOIN enrolments ON enrolments.student_id = students.id
WHERE enrolments.id IS NULL;

Aliases

Short aliases such as s and c keep joins readable. Always qualify columns when two tables share a name.

Practice

1. List every student with the course they are enrolled in.
2. Find courses with no students.
3. Join three tables and return a readable report.

Hands-on exercise

Using students, enrolments and courses, write a query that returns student name, course title and enrolment date. Include students who have not enrolled yet.

Recap

INNER JOIN keeps matches, LEFT JOIN keeps everything on the left. Aliases and clear ON conditions make joins readable.