SQL: JOIN Operations

When data is split across multiple normalised tables, you use JOINs to combine rows from two or more tables based on a related column (usually a foreign key relationship). Joins are one of the most powerful features of SQL and a core topic at A-Level.

---

Why Do We Need Joins?

After normalisation, data that logically belongs together is stored in separate tables to avoid redundancy. To view this data together, we must join the tables.

Example Tables:

Students

StudentID	FirstName	Surname	FormGroup
1	Alice	Chen	10A
2	Bob	Patel	10B
3	Carol	Smith	10A

Grades

GradeID	StudentID	Subject	Score
1	1	Maths	85
2	1	English	72
3	2	Maths	91
4	4	Science	68

Note that GradeID 4 references StudentID 4, who does not exist in the Students table (this will illustrate differences between join types).

---

INNER JOIN

An INNER JOIN returns only the rows where there is a match in both tables.

SELECT Students.FirstName, Students.Surname, Grades.Subject, Grades.Score
FROM Students
INNER JOIN Grades ON Students.StudentID = Grades.StudentID;

Result:

FirstName	Surname	Subject	Score
Alice	Chen	Maths	85
Alice	Chen	English	72
Bob	Patel	Maths	91

• Carol (StudentID 3) is excluded because she has no matching row in Grades.
• GradeID 4 (StudentID 4) is excluded because there is no matching student.

Using Table Aliases

SELECT s.FirstName, s.Surname, g.Subject, g.Score
FROM Students s
INNER JOIN Grades g ON s.StudentID = g.StudentID;

Using short aliases (s, g) makes queries more readable.

---

LEFT JOIN (LEFT OUTER JOIN)

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

SELECT s.FirstName, s.Surname, g.Subject, g.Score
FROM Students s
LEFT JOIN Grades g ON s.StudentID = g.StudentID;

Result:

FirstName	Surname	Subject	Score
Alice	Chen	Maths	85
Alice	Chen	English	72
Bob	Patel	Maths	91
Carol	Smith	NULL	NULL

Carol appears in the result because she is in the left table (Students), but her Subject and Score are NULL because she has no grades.

---

RIGHT JOIN (RIGHT OUTER JOIN)

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

SELECT s.FirstName, s.Surname, g.Subject, g.Score
FROM Students s
RIGHT JOIN Grades g ON s.StudentID = g.StudentID;

Result: