Skip to content
VirtusAcademy

SQL: Combining Tables

FoundationHigherAQA

Learn SQL: Combining Tables for GCSE Computer Science with this free worksheet and full mark scheme — Foundation and Higher exam-style questions with worked answers. Data from two tables can be combined by linking them on a shared field.

Free downloads

These worksheets and mark schemes are original, written for Virtus Academy and checked against the current AQA specification. Every worksheet comes with a full mark scheme.

Foundation

WorksheetFoundation · PDFMark SchemeFoundation · PDF

Higher

WorksheetHigher · PDFMark SchemeHigher · PDF

Topic overview

When data is spread across linked tables, a query must combine them to return fields from both.

Both tables are named in the FROM clause, separated by a comma. The WHERE clause then states which fields link them — typically the foreign key in one table matching the primary key in the other.

Field names must be qualified with the table name when the same name appears in both, written as Table.Field. So SELECT Students.Name, Courses.Title FROM Students, Courses WHERE Students.CourseID = Courses.CourseID returns each student alongside their course. Omitting the linking condition returns every possible combination of rows, which is almost never what was wanted.

Revision notes

Naming both tables

List both tables in the FROM clause, separated by a comma.

The WHERE clause then states the linking condition — usually the foreign key in one table matching the primary key in the other.

Qualifying field names

When the same field name appears in both tables, qualify it as Table.Field.

Students.Name distinguishes it from any Name field in another table. This is required whenever the name is ambiguous, and is good practice throughout.

The linking condition

Without a linking condition, the query returns every possible combination of rows from both tables.

Two tables of 100 records each would return 10 000 rows, almost none of them meaningful. The WHERE clause is what makes the join correct.

Key points

  • Both tables are named in the FROM clause.
  • Tables are separated by a comma.
  • WHERE states the linking condition.
  • The foreign key matches the primary key.
  • Field names are qualified as Table.Field.
  • Omitting the link returns every combination.

Worked examples

Example 1

Write a query returning Students.Name and Courses.Title from linked tables. [3 marks]

Working

SELECT Students.Name, Courses.Titleselect the qualified field names
FROM Students, Coursesname both tables in the FROM clause
WHERE Students.CourseID = Courses.CourseIDstate the linking condition

Example 2

Explain why field names are qualified with the table name. [2 marks]

Working

The same field name may appear in more than one tablestate the problem
so qualifying it as Table.Field makes clear which table the field comes fromexplain the solution

Example 3

Explain what happens if the linking condition is omitted. [2 marks]

Working

The query returns every possible combination of rows from both tablesstate the result
producing a very large number of rows, almost none of which are meaningfulexplain the consequence

Common mistakes

  • Forgetting the linking condition.

    The result is every combination of rows.

  • Not qualifying ambiguous field names.

    The query fails if the name appears in both tables.

  • Naming only one table in FROM.

    Both must be listed when fields come from both.

  • Linking on the wrong fields.

    The foreign key must match the corresponding primary key.

Exam tips

  • Always include the linking condition.
  • Qualify field names as Table.Field.
  • List both tables in the FROM clause.
  • Check the link uses the foreign and primary keys.

Key terms

Join
Combining data from two linked tables.
Qualified field name
A field written as Table.Field.
Linking condition
The WHERE clause matching keys between tables.
Foreign key
The field linking to another table's primary key.

Related topics

Written and reviewed against the current AQA specification. Spotted an error? Let us know.