JOINS — Overview

JOINs combine data from multiple tables into a single result set. They are the mechanism that makes the relational model powerful — pulling together normalised data on demand.

Why JOINs Exist

In a normalised database, data is split across multiple tables to eliminate redundancy. A learner's details live in learners, their enrolments in enrolments, their placements in placements. A JOIN re-combines this data for a specific query — without ever storing it together permanently.

The Four Join Types

-- Visualising join types with a simple example:
-- learners table has: 1-Thandi, 2-Sipho, 3-Lerato, 4-Bongani
-- placements table has: learner_id 1-Thandi (placed), 2-Sipho (placed)
-- Lerato and Bongani have no placement yet

-- INNER JOIN — only rows matching in BOTH tables:
-- Thandi, Sipho

-- LEFT JOIN — all from left + matching from right (NULL where no match):
-- Thandi, Sipho, Lerato (NULL placement), Bongani (NULL placement)

-- RIGHT JOIN — all from right + matching from left:
-- Thandi, Sipho + any placements with no learner (data integrity issue)

-- FULL JOIN — all from both, NULL where no match:
-- Thandi, Sipho, Lerato (NULL), Bongani (NULL) + orphaned placements (NULL)

-- JOIN syntax:
SELECT
    l.full_name,
    l.course,
    p.employer_name,
    p.monthly_salary
FROM   learners  AS l      -- left table, aliased as l
JOIN   placements AS p     -- INNER JOIN by default
    ON l.learner_id = p.learner_id;

-- Table aliases make queries much more readable:
-- l.full_name instead of learners.full_name
-- Essential when a table appears more than once (self-join)

-- Old-style (implicit join) — avoid in new code:
SELECT l.full_name, p.employer_name
FROM   learners l, placements p
WHERE  l.learner_id = p.learner_id;

Choosing the Right Join

• INNER JOIN — only rows with matching records in both tables. Use when you only want complete records.
• LEFT JOIN — all rows from the left table, NULL for unmatched right rows. Use when you want all records from one table regardless of whether they have related data.
• RIGHT JOIN — rarely needed; a LEFT JOIN with tables swapped is clearer.
• FULL JOIN — all rows from both sides. Use when you need to see unmatched data from both.

Practice Task

Common Mistakes

• Forgetting the ON clause — a JOIN without ON is a CROSS JOIN (Cartesian product) returning every combination.
• Ambiguous column names — always prefix with table alias when joining.
• Missing indexes on join columns — slow JOINs are almost always caused by unindexed FK columns.
• Joining on non-key columns — JOINs on non-unique columns produce multiple rows per left row.