SQL: JOIN Operations

Normalisation deliberately splits data across several relations to remove redundancy, so the data that a human wants to see together — a student's name and their course and their grade — now lives in three different tables. A JOIN recombines that data at query time by matching rows on a related column, almost always a foreign-key / primary-key pair. The join is therefore the direct counterpart of the foreign keys you created during E-R modelling and normalisation: those keys told the database how the tables relate; the JOIN is where you use that relationship. At A-Level the central operation is the INNER JOIN across two or three tables, and this lesson works through it carefully — with the result table shown for every query — before covering the outer joins for completeness.

We continue with the same Student / Course / Enrolment schema introduced in the previous lesson.

---

Spec Mapping

This lesson covers the AQA A-Level Computer Science (7517) Fundamentals of databases requirement to use SQL to retrieve data across multiple tables: the INNER JOIN on key equality, joining two and three tables, table aliases, and relating joins back to the foreign-key structure produced by normalisation. Outer joins and the Cartesian product are covered as supporting context.

---

The Sample Schema

Student

StudentID	FirstName	Surname	FormGroup
1	Alice	Chen	13A
2	Bob	Patel	13A
3	Carol	Smith	13B
4	Dev	Lin	13B

Course

CourseID	CourseName	TeacherID
C1	Computer Science	T1
C2	Mathematics	T2
C3	Physics	T1

Enrolment (junction table; composite PK (StudentID, CourseID); both columns are foreign keys)

StudentID	CourseID	Grade
1	C1	85
1	C2	78
2	C1	91
3	C2	64

Note: Dev Lin (StudentID 4) has no enrolment, and Physics (C3) has no enrolled students. These "unmatched" rows let us see exactly how the different join types behave.

---

Why Do We Need Joins?

Suppose we want a readable list of who is studying what, and with what grade. The names live in Student, the course titles live in Course, and the link plus grade lives in Enrolment. No single table has all three, so we must join them on their keys: Student.StudentID = Enrolment.StudentID and Enrolment.CourseID = Course.CourseID. The join condition is always "this table's foreign key equals that table's primary key".

---

INNER JOIN

An INNER JOIN returns only the rows where the join condition is satisfied in both tables. Rows with no match on either side are excluded.

SELECT Student.FirstName, Student.Surname, Enrolment.Grade
FROM Student
INNER JOIN Enrolment ON Student.StudentID = Enrolment.StudentID;

Result:

FirstName	Surname	Grade
Alice	Chen	85
Alice	Chen	78
Bob	Patel	91
Carol	Smith	64

• Alice (StudentID 1) appears twice because she has two enrolments — the join produces one output row per matching pair.
• Dev (StudentID 4) is excluded because he has no matching enrolment row.

Using table aliases

Long table names make multi-table queries verbose. An alias (a short name declared after the table) keeps them readable:

SELECT s.FirstName, s.Surname, e.Grade
FROM Student s
INNER JOIN Enrolment e ON s.StudentID = e.StudentID;

This produces exactly the same result as above. The aliases s and e stand for Student and Enrolment throughout the query. When two tables share a column name (here both have StudentID), you must qualify it with the table or alias (s.StudentID) to avoid ambiguity.

---

Joining Three Tables

To bring in the course name, chain a second INNER JOIN to the Course table:

SELECT s.FirstName, s.Surname, c.CourseName, e.Grade
FROM Student s
INNER JOIN Enrolment e ON s.StudentID = e.StudentID
INNER JOIN Course c ON e.CourseID = c.CourseID
ORDER BY s.Surname, c.CourseName;

Result:

FirstName	Surname	CourseName	Grade
Alice	Chen	Computer Science	85
Alice	Chen	Mathematics	78
Bob	Patel	Computer Science	91
Carol	Smith	Mathematics	64

The query walks the relationships Student → Enrolment → Course, exactly mirroring the foreign keys. Adding a WHERE clause filters the joined result:

SELECT s.FirstName, c.CourseName, e.Grade
FROM Student s
INNER JOIN Enrolment e ON s.StudentID = e.StudentID
INNER JOIN Course c ON e.CourseID = c.CourseID
WHERE e.Grade >= 80
ORDER BY e.Grade DESC;

Result:

FirstName	CourseName	Grade
Bob	Computer Science	91
Alice	Computer Science	85

Only enrolments with a grade of 80 or more survive the WHERE filter.

---

INNER JOIN vs. Listing Two Tables (the old comma syntax)

You may see joins written by listing tables in FROM and putting the match in WHERE:

SELECT s.FirstName, e.Grade
FROM Student s, Enrolment e
WHERE s.StudentID = e.StudentID;

This produces the same result as the INNER JOIN above. Internally the DBMS forms the Cartesian product of the two tables and then keeps only the rows satisfying the WHERE condition. The modern INNER JOIN … ON … form is strongly preferred because the join logic (ON) is kept separate from the row filtering (WHERE), making the query clearer and far less error-prone — forgetting the WHERE line in the comma form silently returns the full Cartesian product (here 4 × 4 = 16 rows), a classic bug.

---

Outer Joins (Supporting Context)

Sometimes you want to keep rows that have no match. Outer joins do this, filling the missing side with NULL.

LEFT JOIN

Returns all rows from the left table, plus matches from the right; unmatched left rows get NULL on the right.

SELECT s.FirstName, s.Surname, e.CourseID, e.Grade
FROM Student s
LEFT JOIN Enrolment e ON s.StudentID = e.StudentID;

Result:

FirstName	Surname	CourseID	Grade
Alice	Chen	C1	85
Alice	Chen	C2	78
Bob	Patel	C1	91
Carol	Smith	C2	64
Dev	Lin	NULL	NULL

Dev now appears (because he is in the left table) with NULL for course and grade. Use a LEFT JOIN whenever a question says "list all students, including those with no enrolments".

RIGHT JOIN

The mirror image: all rows from the right table, with NULL for unmatched left rows. To see its effect we join Course (right) to Enrolment, since Physics has no enrolments:

SELECT e.StudentID, e.Grade, c.CourseName
FROM Enrolment e
RIGHT JOIN Course c ON e.CourseID = c.CourseID
ORDER BY c.CourseName;

Result:

StudentID	Grade	CourseName
1	85	Computer Science
2	91	Computer Science
1	78	Mathematics
NULL	NULL	Physics

Physics appears with NULLs because it is in the right table (Course) but has no enrolments. Any RIGHT JOIN can be rewritten as a LEFT JOIN simply by swapping the table order.

---

CROSS JOIN (the Cartesian Product)

A CROSS JOIN has no ON condition and returns every possible pairing of rows — the Cartesian product.

SELECT s.FirstName, c.CourseName
FROM Student s
CROSS JOIN Course c;

With 4 students and 3 courses the result has 4 × 3 = 12 rows (every student paired with every course). Cross joins are rarely intended deliberately; their importance is conceptual — they explain what the comma syntax produces before a WHERE filter is applied.

---

Comparison of JOIN Types

JOIN type	Returns	Unmatched left rows	Unmatched right rows
INNER JOIN	Only matched pairs	Excluded	Excluded
LEFT JOIN	All left + matched right	Included (NULLs on right)	Excluded
RIGHT JOIN	All right + matched left	Excluded	Included (NULLs on left)
CROSS JOIN	Every combination	N/A	N/A

---

Self Join

A self join joins a table to itself, which is how a hierarchy held in one table is unravelled. Consider staff where each row stores its manager's ID:

Employee

EmpID	Name	ManagerID
1	Alice	NULL
2	Bob	1
3	Carol	1
4	Dave	2

SELECT e.Name AS Employee, m.Name AS Manager
FROM Employee e
LEFT JOIN Employee m ON e.ManagerID = m.EmpID;

Result:

Employee	Manager
Alice	NULL
Bob	Alice
Carol	Alice
Dave	Bob

Two aliases (e for the employee, m for the same table viewed as managers) are essential — without them the DBMS cannot tell the two roles apart. A LEFT JOIN keeps Alice, whose ManagerID is NULL.

---

Worked Example — A Library Database

Scenario: Member(MemberID, Name), Book(BookID, Title, Author), Loan(LoanID, MemberID*, BookID*, LoanDate, ReturnDate) — Loan's starred columns are foreign keys.

Task: List every member alongside the title of any book they currently have on loan (i.e. ReturnDate IS NULL), including members who currently have nothing out.

SELECT m.Name, b.Title, l.LoanDate
FROM Member m
LEFT JOIN Loan l ON m.MemberID = l.MemberID AND l.ReturnDate IS NULL
LEFT JOIN Book b ON l.BookID = b.BookID
ORDER BY m.Name;

Two subtleties make this a strong answer: (1) the "current loan" test l.ReturnDate IS NULL is placed in the ON clause, not a later WHERE — putting it in WHERE would discard members with no current loan and silently turn the outer join back into an inner one; (2) the first join is LEFT so every member is retained, and the second is LEFT so that a member kept by the first join is not dropped when there is consequently no book to match.

---

How a Join is Evaluated — Tracing it By Hand

Exam questions often ask you to give the output of a join over small sample tables, so it is worth understanding the underlying process. Conceptually, an inner join proceeds in two steps:

1. Form the Cartesian product of the two tables (every row of one paired with every row of the other).
2. Keep only the paired rows for which the ON condition is true.

Take the two-table inner join of Student and Enrolment on StudentID. The Cartesian product would be 4 × 4 = 16 candidate pairs; the ON condition Student.StudentID = Enrolment.StudentID keeps just the four where the IDs match:

Student row	Enrolment row	StudentID match?	Kept?
1 Alice	(1, C1, 85)	1 = 1 ✓	Yes
1 Alice	(1, C2, 78)	1 = 1 ✓	Yes
2 Bob	(2, C1, 91)	2 = 2 ✓	Yes
3 Carol	(3, C2, 64)	3 = 3 ✓	Yes
4 Dev	(any enrolment)	4 ≠ 1,2,3 ✗	No

This trace explains why Dev disappears (no enrolment row carries StudentID 4) and why Alice appears twice (two enrolment rows carry StudentID 1). A real DBMS does not literally build the full product — its optimiser uses indexes and smarter join algorithms — but the logical result is exactly as if it had. When answering by hand, write out the matches row by row exactly like the table above.

---

A Further Three-Table Worked Query

Task: List the surname of each student, the courses they take and the grade, but only for the Computer Science course (C1), ordered by grade descending.

SELECT s.Surname, c.CourseName, e.Grade
FROM Student s
INNER JOIN Enrolment e ON s.StudentID = e.StudentID
INNER JOIN Course c ON e.CourseID = c.CourseID
WHERE c.CourseName = 'Computer Science'
ORDER BY e.Grade DESC;

Result:

Surname	CourseName	Grade
Patel	Computer Science	91
Shah*	Computer Science	88
Chen	Computer Science	85

(Shah is StudentID 5 in the SELECT lesson's fuller dataset; with the four-student dataset used in this lesson, the result is Patel 91 and Chen 85.) The point of interest is that the WHERE filter references a column from the third table (c.CourseName), which is only available because we joined Course in. You can filter, sort and select on any column from any joined table.

Choosing the driving table and reading order

When chaining joins, write them in the order that makes the relationships easiest to read — typically following the foreign keys from the "main" entity outward (Student → Enrolment → Course). The result of an inner join is identical regardless of the order you list the tables, because matching is symmetric; readability, not correctness, drives the choice. (For outer joins the order does matter, since LEFT and RIGHT are direction-specific.)

---

Aliases Recap and When They Are Mandatory

Aliases serve three purposes: