SQL with two tables: the equi-join

National 5 Computing Science: Database design and development

A SELECT query using two tables comes up in the National 5 database section every year, often for 4 or 5 marks. The examiners' reports for 2023 and 2024 name the commonest error: leaving out the join.

Two linked tables

A football league keeps its teams and players in two tables. teamID is the primary key of Team, and a foreign key in Player.

Team
teamID	teamName	town
1	Ness Rovers	Inverness
2	Tay Athletic	Dundee
3	Glen Ladies	Inverness

Player
playerID	firstName	surname	position	teamID
11	Mairi	Ross	Forward	1
12	Callum	Fraser	Defender	2
13	Aisha	Khan	Midfield	3
14	Euan	Grant	Goalkeeper	1
15	Skye	Morrison	Forward	2

The query

List the first name, surname and team name of every player in a team from Inverness:

SELECT firstName, surname, teamName
FROM Player, Team
WHERE Player.teamID = Team.teamID AND town = "Inverness";

The result is Mairi Ross (Ness Rovers), Aisha Khan (Glen Ladies) and Euan Grant (Ness Rovers).

Where the marks are

SELECT exactly the fields asked for. Extra fields lose the mark.
FROM both tables, because the fields come from both.
The equi-join: Player.teamID = Team.teamID. It matches each player to their own team.
Each search condition, joined to the rest with AND. Text goes in quotes.

Why the join matters

Without Player.teamID = Team.teamID, the database pairs every player with every team from Inverness. Instead of 3 rows you get 10, and most of them are wrong: Callum Fraser plays for Tay Athletic, yet he would appear twice, once with each Inverness team.

Two more traps

Write the table name with a field that is in both tables. teamID is in both, so on its own it is ambiguous: write Team.teamID.
Every extra condition needs its own AND. WHERE Player.teamID = Team.teamID AND town = "Inverness" AND position = "Forward".

Try two exam-style questions

Choose an answer, say how sure you are, then press Check. Every wrong answer tells you why it is wrong.

The Query builder simulation — **In the course: Query builder.** Build SQL queries on real linked tables, run them, and see exactly which records come back.

The full course covers every topic like this. Every topic in National 5 Computing Science has a short note, a simulation to try and a practice quiz with feedback on every answer, plus coding lessons, a full mock exam and a progress report for parents.

Create an account Try more for free About the course

More guides

This site is independent: it is not connected to the SQA or Qualifications Scotland. Points from the examiners' course reports are given in our own words.