Lesson 19 — Matching Records

INNER JOIN

INNER JOIN is the most common join — it returns only rows with matching records in both tables. Use it when you need complete, related data from multiple tables.

SQL — Basic INNER JOIN 
-- INNER JOIN — learners with their placements:
SELECT
    l.learner_id,
    l.full_name,
    l.course,
    p.employer_name,
    p.position,
    p.monthly_salary,
    p.city AS placement_city
FROM   learners   AS l
INNER JOIN placements AS p
    ON l.learner_id = p.learner_id
ORDER BY l.full_name;
SQL — Three-table JOIN 
-- Three-table join:
SELECT
    l.full_name,
    l.course,
    a.title         AS assessment,
    a.score,
    f.full_name     AS facilitator
FROM   learners    AS l
INNER JOIN assessments AS a  ON l.learner_id  = a.learner_id
INNER JOIN facilitators AS f ON a.facilitator_id = f.facilitator_id
WHERE  a.score >= 70
ORDER BY l.full_name, a.title;
SQL — JOIN with GROUP BY 
-- INNER JOIN with aggregate:
SELECT
    l.full_name,
    l.course,
    COUNT(a.assessment_id)  AS assessment_count,
    AVG(a.score)            AS avg_score,
    SUM(a.score)            AS total_score
FROM   learners    AS l
INNER JOIN assessments AS a ON l.learner_id = a.learner_id
GROUP BY l.learner_id, l.full_name, l.course
HAVING COUNT(a.assessment_id) >= 3    -- only learners with 3+ assessments
ORDER BY avg_score DESC;
SQL — Self-join 
-- Self-join — join a table to itself:
-- Find learners in the same city as 'Thandi':
SELECT
    other.full_name,
    other.city
FROM   learners AS source
INNER JOIN learners AS other
    ON  source.city = other.city
    AND other.learner_id != source.learner_id   -- exclude the source learner
WHERE  source.full_name = 'Thandi Mokoena';

Practice Task

Your Turn

Write queries: (1) all learners with their assessment scores (only learners who have assessments); (2) learners with their enrolment details (3 tables); (3) average assessment score per learner, only for learners with 2+ assessments; (4) self-join to find pairs of learners in the same course.

Common Mistakes

  • INNER JOIN on a NULL FK — NULLs never match anything, so NULL FK rows are excluded.
  • Missing table alias — ambiguous column names cause errors when joining.
  • Missing index on join column — critical for performance on large tables.
  • Joining on the wrong column — a logical error that produces a Cartesian product or wrong rows.

Professional Tip

INNER JOIN on indexed foreign keys is extremely fast even on millions of rows. The query optimiser uses the index to look up matching rows directly, not scan every row.

Mini Quiz

If a learner has no assessments, what happens when you INNER JOIN learners to assessments?