Lesson 22 — Complete Picture

FULL JOIN

FULL JOIN returns all rows from both tables, with NULL on the side that has no match. It is useful for finding all unmatched records across two tables simultaneously.

SQL — FULL JOIN 
-- FULL OUTER JOIN — all learners and all placements:
SELECT
    l.full_name,
    p.employer_name
FROM   learners   AS l
FULL OUTER JOIN placements AS p
    ON l.learner_id = p.learner_id;
-- Placed learners: full data on both sides
-- Unplaced learners: NULL employer_name
-- Orphaned placements: NULL full_name

-- FULL JOIN is not supported in MySQL!
-- Emulate with LEFT JOIN UNION RIGHT JOIN:
SELECT l.full_name, p.employer_name
FROM   learners l LEFT  JOIN placements p ON l.learner_id = p.learner_id
UNION
SELECT l.full_name, p.employer_name
FROM   learners l RIGHT JOIN placements p ON l.learner_id = p.learner_id;
SQL — Finding all unmatched 
-- Practical: find ALL unmatched records from both tables at once:
SELECT
    COALESCE(l.full_name, '(no learner)') AS learner,
    COALESCE(p.employer_name, '(unplaced)') AS employer,
    CASE
        WHEN l.learner_id IS NULL THEN 'Orphaned placement'
        WHEN p.learner_id IS NULL THEN 'Unplaced learner'
        ELSE 'Matched'
    END AS status
FROM   learners   AS l
FULL OUTER JOIN placements AS p ON l.learner_id = p.learner_id
WHERE  l.learner_id IS NULL OR p.learner_id IS NULL
ORDER BY status;

Practice Task

Your Turn

Write a FULL JOIN (or its UNION emulation for MySQL) between learners and assessments. Use COALESCE for the NULL columns. Add a status column using CASE to label each row as 'No assessment', 'No learner', or 'Both exist'. Count how many of each status you have.

Common Mistakes

  • FULL JOIN is not in MySQL — use LEFT JOIN UNION RIGHT JOIN.
  • FULL JOIN returns duplicates if there are multiple matches on both sides — same as INNER JOIN.
  • Forgetting COALESCE — NULL columns from FULL JOIN often need defaults for display.
  • Using UNION ALL instead of UNION in the MySQL emulation — introduces duplicates for matched rows.

Professional Tip

FULL JOIN is most useful for data quality audits: 'show me everything in both tables and highlight where things don't match'. In day-to-day application queries, INNER JOIN and LEFT JOIN cover most cases.

Mini Quiz

Which databases natively support FULL OUTER JOIN?