Joins and Relationships in MySQL

MySQL does not store relationships inside a row. It stores ids and connects tables at query time with joins.

A Simple Join

Join on the matching id columns and select only the columns you need.

SELECT s.name, c.title
FROM students s
INNER JOIN enrolments e ON e.student_id = s.id
INNER JOIN courses c ON c.id = e.course_id
WHERE s.level = 'SS2';

LEFT JOIN

Use LEFT JOIN when the related row is optional. This report includes students with no enrolments.

SELECT s.name, COUNT(e.id) AS courses
FROM students s
LEFT JOIN enrolments e ON e.student_id = s.id
GROUP BY s.id, s.name
ORDER BY courses DESC;

Referential Integrity

A foreign key guarantees that an enrolment cannot reference a student that does not exist. Add indexes on foreign key columns for fast joins.

Practice

1. List courses with the number of enrolled students.
2. Find students with no enrolments.
3. Explain what happens when you delete a parent row with ON DELETE CASCADE.

Hands-on exercise

Write a query that returns each course title and the names of enrolled students, including courses with no students. Order by course title, then student name.

Recap

Joins combine tables on matching keys. LEFT JOIN keeps unmatched parents, and foreign keys keep references valid.