SQL Joins & Reading Code (SQA National 5 Computing Science): Revision Note
Exam code: X816 75
SQL joins and reading code
What is an equi-join?
An equi-join is used to combine data from two linked tables in a relational database
It retrieves data where the primary key (PK) in one table matches the foreign key (FK) in another
This ensures that only related records are displayed
Structure of an equi-join
When querying two linked tables, you must include the join condition in the WHERE clause
SELECT field1, field2
FROM table1, table2
WHERE table1.primaryKey = table2.foreignKey;
The condition links the two tables correctly so that the output shows matched data from both
Example
Table: Employees
EmployID | Name | Salary | City |
|---|---|---|---|
1 | Fynn Roberts | 45000 | London |
2 | Zarmeen Azra | 52000 | Manchester |
3 | Ella Stanley | 39500 | Birmingham |
Table: Departments
DepartID | EmployID | Department | Manager | |
|---|---|---|---|---|
1 | 1 | HR | Sally Jones | [email protected] (opens in a new tab)(opens in a new tab) |
2 | 2 | Sales | Peter Evans | [email protected] (opens in a new tab)(opens in a new tab) |
3 | 3 | Marketing | Stuart Davies | [email protected] (opens in a new tab)(opens in a new tab) |
Select the name, manager and email address of employees in the sales department
SELECT Employees.Name, Departments.Manager, Departments.Email
FROM Employees, Departments
WHERE Employees.EmployID = Departments.EmployID
AND Department = 'Sales';
FROM Employees, Departmentsmeans data is taken from both tablesWHERE Employees.EmployID = Departments.EmployIDis the equi-join condition linking the tablesAND Department = 'Sales'filters the results to show only sales department employees
Output:
Understanding and debugging SQL joins
A missing join condition is the most common error
If the equi-join condition is missing:
SELECT Employees.Name, Departments.Manager
FROM Employees, Departments;
The database tries to match every employee with every department, producing incorrect results
You must include the join condition to correctly link both tables
Reading and explaining SQL code
When reading SQL that includes joins, check:
Tables: Are both tables named correctly?
Fields: Do the field names exist in those tables?
Join condition: Is there a line linking the PK and FK?
Filtering: Are
WHERE,AND, andORused correctly to filter results?
SELECT Player.forename, Team.town
FROM Player, Team
WHERE Player.teamName = Team.teamName
AND town = 'Paisley';
Retrieves the forename and town of all players whose team is based in Paisley
The join
Player.teamName = Team.teamNameensures each player is correctly matched to their team
Examiner Tips and Tricks
When asked to retrieve data from two linked tables:
Always include the equi-join condition (
table1.PK = table2.FK)Always check for correct field names and logical filters
If the question asks you to “explain why a query doesn’t work,” look for a missing join, wrong field name, or incorrect condition
Summary
SQL feature | Purpose | Example |
|---|---|---|
Equi-join | Combine data from two related tables |
|
Filtering | Narrow results |
|
Common error | Missing join condition (produces incorrect output) |
|
Worked Example
Scenario: The Botanical Database
A database is used by an environmental group to track edible plants and the climates they grow in. The database consists of two linked tables: Plant and Climate.
The
Climatetable contains details likeclimateTypeandtemperatureRange.The
Planttable contains details likeplantName,ediblePart, andsoilType.The link between the tables is made using the primary key
climateRef(inClimate) and the foreign keyclimateRef(inPlant).
The group wishes to generate a detailed list of all edible plants that produce fruit and grow in tropical climates. The list should be ordered to display the plants that grow in the highest temperatureRange first
(i) Write the full SQL statement that implements the design above, including the necessary equi-join
[3]
An intern wrote the following SQL statement in an attempt to find plants that grow in Loam soil and a Mediterranean climate.
SELECT climateRef, temperatureRange, plantName, ediblePart
FROM Climate
WHERE soilType = "Loam"
AND climateType = "Mediterranean";(ii) State two specific reasons why this SQL statement will not produce the expected output, based on the required structure of the two linked tables
[2]
Answers
(i)
SELECT plantName, climateType, temperatureRange FROM Plant, Climate [1 mark]
WHERE Plant.climateRef = Climate.climateRef [1 mark]
AND ediblePart = 'Fruit' AND climateType = 'Tropical' ORDER BY temperatureRange DESC; [1 mark]
(ii)
missing the equi-join condition in the
WHEREclause [1 mark]This is required because data is being selected from two linked tables (
PlantandClimate)
FROMclause only specifies theClimatetable [1 mark]But the query attempts to search using the field soilType, which is an attribute located in the Plant table (OR: The
Planttable is missing from theFROMclause)
Unlock more, it's free!
Did this page help you?
