SQL JOIN Queries Explained for GCSE Computer Science

A SQL JOIN query combines rows from two or more tables based on a shared column, letting you retrieve data that is spread across multiple related tables in a single query. In a relational database, data is deliberately split into separate tables to avoid repetition — JOIN is the mechanism that brings it back together when you need it.

Why do relational databases need JOIN?

Imagine a school database with two tables: Students (StudentID, Name, Year) and Grades (StudentID, Subject, Mark). Student names are not stored in the Grades table — that would mean duplicating names for every exam result, wasting space and risking inconsistency if a name changes.

Instead, both tables share the StudentID column. A JOIN uses this shared column to link matching rows from both tables, letting you ask: "Show me each student's name alongside their exam mark."

This is the fundamental promise of a relational database — separate tables, linked by keys, reassembled by queries.

What is an INNER JOIN and how does it work?

An INNER JOIN returns only the rows where a matching value exists in both tables. Rows in either table that have no match in the other table are excluded from the result.

Syntax:

SELECT column1, column2, ...
FROM Table1
INNER JOIN Table2
ON Table1.shared_column = Table2.shared_column;

The ON clause specifies which columns in each table must match. This is typically the primary key of one table matching a foreign key in the other.

Worked example: joining Students and Grades

Table: Students

Table: Grades

Query:

SELECT Students.Name, Grades.Subject, Grades.Mark
FROM Students
INNER JOIN Grades
ON Students.StudentID = Grades.StudentID;

Result:

Notice: Carla (StudentID 3) does not appear — she has no rows in Grades. The row with StudentID 4 in Grades does not appear — there is no matching student in Students. INNER JOIN excludes both.

What is the ON clause doing?

The ON clause defines the join condition — the rule the database uses to decide which row from Table 1 is paired with which row from Table 2.

ON Students.StudentID = Grades.StudentID means: "for each row in Students, find every row in Grades where the StudentID value is the same, and combine those rows."

You must qualify column names with the table name (Students.StudentID) when the same column name exists in both tables — otherwise the database does not know which table's column you mean.

How do you combine JOIN with a WHERE clause?

Adding WHERE filters the joined result further. For example, to find only the Maths marks from Year 10 students:

SELECT Students.Name, Grades.Mark
FROM Students
INNER JOIN Grades
ON Students.StudentID = Grades.StudentID
WHERE Grades.Subject = 'Maths'
AND Students.Year = 10;

Result:

The SQL engine performs the JOIN first, producing the combined table, then applies the WHERE filter to that combined result.

SQL clause order for a JOIN query

For GCSE exam answers, follow this structure precisely:

SELECT   -- columns to display
FROM     -- first table
INNER JOIN  -- second table
ON          -- matching condition
WHERE    -- filter rows (optional)
ORDER BY -- sort result (optional);

Frequently asked questions

What is the difference between INNER JOIN and WHERE with two tables?

In older SQL style, you could write FROM Students, Grades WHERE Students.StudentID = Grades.StudentID, which produces the same result as INNER JOIN … ON. The INNER JOIN syntax is preferred because it is clearer: the join condition is explicitly in the ON clause, and filtering conditions stay in WHERE, making the query easier to read and less error-prone. GCSE specifications generally expect the explicit INNER JOIN syntax.

Do I need to know LEFT JOIN for GCSE Computer Science?

AQA GCSE specification 8525 focuses on INNER JOIN. LEFT JOIN (which includes all rows from the left table even if there is no match in the right table) may appear in extension questions or in some Edexcel content, but you should confirm this against your specific exam board's specification. For the majority of GCSE questions, INNER JOIN is sufficient.

Why must I use the table name prefix (e.g. Students.StudentID) in JOIN queries?

When two tables in a JOIN share the same column name — as StudentID appears in both Students and Grades — the database cannot tell which one you mean unless you prefix it with the table name. The format is TableName.ColumnName. Without the prefix, the database raises an "ambiguous column name" error. It is good practice to always prefix column names in JOIN queries, even when they are not ambiguous, as it makes the query far easier to read.

How does JOIN relate to foreign keys?

A foreign key in one table is a column whose values match the primary key of another table. JOIN exploits this relationship: you join on ON Table2.ForeignKey = Table1.PrimaryKey. The database does not enforce that you join on a key — you can technically join on any matching column — but joining on the designated key relationship is almost always what you intend, and it reflects the design intention captured in the entity-relationship diagram drawn when the database was planned.

Professor Turing walks you through SQL JOIN queries and all GCSE database topics with worked examples and Socratic questioning at aitutors.me.