Use a SQL JOIN to combine related rows from two tables. Specify both tables, then use an ON condition to match a foreign key in one table with the corresponding primary key in the other.
How a JOIN Works
Suppose a database contains these tables:
Student(student_id, student_name, course_id)Course(course_id, course_name)
Course.course_id is the primary key, while Student.course_id is a foreign key referencing it. The query is:
SELECT s.student_name, c.course_name
FROM Student AS s
INNER JOIN Course AS c
ON s.course_id = c.course_id;
The query is processed logically as follows:
FROM Student AS sidentifies the first table and assigns the aliass.INNER JOIN Course AS cidentifies the related table and assigns the aliasc.ON s.course_id = c.course_idgives the join condition.SELECTspecifies the fields returned in the result.
If Maya's course_id is 3 and course 3 is Computer Science, the result includes Maya | Computer Science.
| JOIN type | Rows returned |
|---|---|
INNER JOIN | Only rows with matching values in both tables |
LEFT JOIN | Every row from the left table, plus matching rows from the right table |
RIGHT JOIN | Every row from the right table, plus matching rows from the left table |
A common misconception is that merely listing two tables connects them correctly. Without an appropriate join condition, SQL can produce a Cartesian product, pairing every row in one table with every row in the other.
IB Exam Technique
For A3.3 Database programming, identify the primary-key/foreign-key relationship before constructing the query. Examiners expect correct field names, table names, and an ON condition; use qualified names such as s.course_id to avoid ambiguity when both tables contain fields with the same name.
