SQL Joins

This lesson covers SQL JOIN operations for the OCR A-Level Computer Science (H446) specification. JOINs allow you to combine data from multiple tables based on related columns, which is essential for working with normalised relational databases.

---

Why Use JOINs?

In a normalised database, data is split across multiple tables. JOINs allow you to combine related data from different tables in a single query.

Example tables:

Students:

StudentID	Name	Age
1	Alice	17
2	Bob	18
3	Charlie	17

Enrolments:

StudentID	SubjectID
1	CS
1	Maths
2	CS

Subjects:

SubjectID	SubjectName	TeacherName
CS	Computer Science	Dr Smith
Maths	Mathematics	Ms Patel
English	English Literature	Mr Brown

---

INNER JOIN

An INNER JOIN returns only the rows where there is a match in both tables. Rows without a match in either table are excluded.

SELECT Students.Name, Subjects.SubjectName
FROM Students
INNER JOIN Enrolments ON Students.StudentID = Enrolments.StudentID
INNER JOIN Subjects ON Enrolments.SubjectID = Subjects.SubjectID;

Result:

Name	SubjectName
Alice	Computer Science
Alice	Mathematics
Bob	Computer Science

Charlie is excluded because he has no enrolments. English is excluded because no one is enrolled.

Exam Tip: INNER JOIN is the most common type of JOIN in OCR exam questions. Remember: it only returns rows with matches in BOTH tables. Draw a Venn diagram -- INNER JOIN returns the intersection.

---

LEFT JOIN

A LEFT JOIN returns all rows from the left table and the matching rows from the right table. If there is no match, NULL values are returned for the right table's columns.

SELECT Students.Name, Subjects.SubjectName
FROM Students
LEFT JOIN Enrolments ON Students.StudentID = Enrolments.StudentID
LEFT JOIN Subjects ON Enrolments.SubjectID = Subjects.SubjectID;

Result:

Name	SubjectName
Alice	Computer Science
Alice	Mathematics
Bob	Computer Science
Charlie	NULL

Charlie appears even though he has no enrolments -- his SubjectName is NULL.

---

RIGHT JOIN

A RIGHT JOIN returns all rows from the right table and matching rows from the left table. If there is no match, NULL values are returned for the left table's columns.

SELECT Students.Name, Subjects.SubjectName
FROM Students
RIGHT JOIN Enrolments ON Students.StudentID = Enrolments.StudentID
RIGHT JOIN Subjects ON Enrolments.SubjectID = Subjects.SubjectID;

This would include English Literature even if no students are enrolled.

---

Comparison of JOIN Types

JOIN Type	Returns
INNER JOIN	Only rows with matches in BOTH tables.
LEFT JOIN	All rows from the LEFT table + matches from the right (NULL if no match).
RIGHT JOIN	All rows from the RIGHT table + matches from the left (NULL if no match).

Venn Diagram Representation