SQL — Joining Tables Grade 12

In SA IT CAPS, joining two tables is done using the WHERE clause method — listing both tables in FROM and linking them with a matching condition in WHERE. This is the only join method required for CAPS.

CAPS Method: WHERE Clause — Not INNER JOIN

The official SA IT CAPS specifies the WHERE method for single joins. The CAPS document states: “Create a join query (single joins) using WHERE”. Do NOT use the INNER JOIN keyword in exams.

Why Join Tables?

A relational database splits related data across tables. For example, a student record stores a ClassID but the class name lives in a separate table. To display both together, you join the tables.

Syntax

SQL — WHERE join syntax

SELECT TableA.Field1, TableB.Field2
FROM TableA, TableB
WHERE TableA.KeyField = TableB.KeyField;

Both tables appear in FROM, separated by a comma. The WHERE clause links the tables on their shared key field.

Examples

Example 1 — Students with their class names

SQL

SELECT tblStudents.Name, tblStudents.Surname, tblClasses.ClassName
FROM tblStudents, tblClasses
WHERE tblStudents.ClassID = tblClasses.ClassID;

Example 2 — With extra filtering and sorting

SQL

SELECT tblStudents.Name, tblStudents.Surname, tblClasses.ClassName
FROM tblStudents, tblClasses
WHERE tblStudents.ClassID = tblClasses.ClassID
AND tblStudents.Grade = 12
ORDER BY tblStudents.Surname ASC;

Adding extra conditions

Use AND after the join condition to filter results further. The join condition always comes first in WHERE.

Example 3 — Join with GROUP BY and COUNT

SQL — count students per class

SELECT tblClasses.ClassName, COUNT(tblStudents.StudentID) AS Total
FROM tblStudents, tblClasses
WHERE tblStudents.ClassID = tblClasses.ClassID
GROUP BY tblClasses.ClassName
ORDER BY Total DESC;

Example 4 — Using LIKE with a join

SQL

SELECT tblBooks.Title, tblAuthors.AuthorName, tblBooks.Price
FROM tblBooks, tblAuthors
WHERE tblBooks.AuthorID = tblAuthors.AuthorID
AND tblBooks.Title LIKE "IT%"
ORDER BY tblBooks.Price DESC;

Using a Join in Delphi (TADOQuery)

Delphi

qryData.Close;
qryData.SQL.Clear;
qryData.SQL.Add('SELECT tblStudents.Name, tblStudents.Surname, tblClasses.ClassName');
qryData.SQL.Add('FROM tblStudents, tblClasses');
qryData.SQL.Add('WHERE tblStudents.ClassID = tblClasses.ClassID');
qryData.SQL.Add('AND tblStudents.Grade = 12');
qryData.Open;

Cartesian Product Warning

Never forget the join condition

If you list two tables in FROM but omit the WHERE link condition, SQL returns every possible combination of rows (a Cartesian product). For two tables with 100 rows each, this produces 10,000 rows — all incorrect. Always include WHERE TableA.Key = TableB.Key.

Common Mistakes

Mistake	Effect	Fix
Missing join condition in WHERE	Cartesian product (massive wrong result)	Always link: `WHERE tblA.Key = tblB.Key`
Ambiguous field name (e.g. just `Name` when both tables have it)	SQL error	Always prefix: `tblStudents.Name`
Not calling `qryData.Close` before changing SQL	Runtime error	Always close first

← PreviousSQL Next →OOP