Lesson 20 — Optional Relationships

LEFT JOIN

LEFT JOIN returns all rows from the left table, with NULL for columns from the right table where no match exists. It is the join for 'show me everything, even records without related data'.

SQL — Basic LEFT JOIN 
-- LEFT JOIN — all learners, even those without placements:
SELECT
    l.full_name,
    l.course,
    p.employer_name,
    p.monthly_salary
FROM   learners    AS l
LEFT JOIN placements AS p
    ON l.learner_id = p.learner_id;
-- Lerato and Bongani appear with NULL employer_name and salary
-- Thandi and Sipho appear with their placement data
SQL — Find unmatched rows 
-- Find learners WITHOUT placements (IS NULL trick):
SELECT
    l.full_name,
    l.course,
    l.city
FROM   learners    AS l
LEFT JOIN placements AS p
    ON l.learner_id = p.learner_id
WHERE  p.learner_id IS NULL;   -- RIGHT side is NULL = no match = unplaced
-- This is more efficient than a NOT IN subquery on large tables

-- The same pattern finds orphaned records:
SELECT p.*
FROM   placements AS p
LEFT JOIN learners AS l ON p.learner_id = l.learner_id
WHERE  l.learner_id IS NULL;  -- placements with no matching learner
SQL — LEFT JOIN with GROUP BY 
-- LEFT JOIN with aggregate — include learners with 0 assessments:
SELECT
    l.full_name,
    l.course,
    COUNT(a.assessment_id)   AS assessment_count,
    COALESCE(AVG(a.score), 0) AS avg_score
FROM   learners    AS l
LEFT JOIN assessments AS a ON l.learner_id = a.learner_id
GROUP  BY l.learner_id, l.full_name, l.course
ORDER  BY avg_score DESC;

Practice Task

Your Turn

Write queries: (1) all learners and their placement status (placed/unplaced); (2) all courses and how many learners are enrolled (include courses with zero learners); (3) learners who have not submitted any assessment; (4) all learners with their most recent assessment score (or NULL if none).

Common Mistakes

  • Filtering on the right table in WHERE instead of ON — converts LEFT JOIN to INNER JOIN: WHERE p.employer_name = 'Acme' excludes unmatched rows.
  • Not handling NULLs from the right table — aggregate functions on NULL columns give unexpected results.
  • COALESCE on aggregate results from LEFT JOIN — COALESCE(COUNT(a.id), 0) is wrong; COUNT never returns NULL. COALESCE is needed on AVG and SUM.
  • Confusing left and right — the 'left' table is the one in FROM; the 'right' is in JOIN.

Professional Tip

The LEFT JOIN + WHERE right_id IS NULL pattern to find unmatched rows is one of the most useful SQL patterns. It efficiently finds records in one table with no corresponding entry in another.

Mini Quiz

How do you find learners who have NO placements using a LEFT JOIN?