FULL OUTER JOIN in MySQL Using UNION

MySQL Has No FULL OUTER JOIN Keyword

A full outer result contains matched pairs, left-only rows and right-only rows. MySQL supports INNER, LEFT and RIGHT joins, but not a direct FULL OUTER JOIN clause. Writing that keyword causes a syntax error; a set-operation pattern is required.

Define the Expected Full Result

The school lab has five students and three classes:

• Four students match class 10 or 20.
• Vihaan is left-only because class_id is NULL.
• XI-A is right-only because no student uses class_id 30.

Therefore the full result must contain six rows: four matched + one student-only + one class-only. Predicting this total makes the emulation testable.

Correct LEFT JOIN + UNION ALL Pattern

SELECT s.student_id, s.student_name,
       c.class_id, c.class_name
FROM join_students AS s
LEFT JOIN classes AS c
  ON c.class_id = s.class_id

UNION ALL

SELECT s.student_id, s.student_name,
       c.class_id, c.class_name
FROM classes AS c
LEFT JOIN join_students AS s
  ON s.class_id = c.class_id
WHERE s.student_id IS NULL;

The first branch returns every student and any class match. The second reverses preservation, but the WHERE clause retains only class rows that did not find a student. Matched pairs are not appended a second time.

Verified Six-Row Output

To request display order, wrap the set operation in a derived table and sort the final result:

SELECT *
FROM (
  -- first SELECT
  SELECT s.student_id, s.student_name,
         c.class_id, c.class_name
  FROM join_students AS s
  LEFT JOIN classes AS c
    ON c.class_id = s.class_id
  UNION ALL
  -- right-only SELECT
  SELECT s.student_id, s.student_name,
         c.class_id, c.class_name
  FROM classes AS c
  LEFT JOIN join_students AS s
    ON s.class_id = c.class_id
  WHERE s.student_id IS NULL
) AS full_result
ORDER BY COALESCE(class_id, 999999), student_id;

Why Not a Naive UNION?

-- Common but weaker pattern:
left_side_with_matches
UNION
right_side_with_matches

UNION removes duplicate projected rows across the entire set. That may hide legitimate duplicates when selected columns are not keys, and it adds distinct-processing work. The explicit anti-match filter plus UNION ALL states the real logic: first all left rows, then only missing right rows.

Useful Counts and Variants

Label row origin

SELECT CASE
         WHEN student_id IS NULL THEN 'class_only'
         WHEN class_id IS NULL THEN 'student_only'
         ELSE 'matched'
       END AS row_status,
       COUNT(*) AS rows_count
FROM (
  SELECT s.student_id, c.class_id
  FROM join_students AS s
  LEFT JOIN classes AS c
    ON c.class_id = s.class_id
  UNION ALL
  SELECT s.student_id, c.class_id
  FROM classes AS c
  LEFT JOIN join_students AS s
    ON s.class_id = c.class_id
  WHERE s.student_id IS NULL
) AS x
GROUP BY row_status;

For a different match rule or composite key, repeat the complete predicate in both branches. Test it with left-only, right-only and multiple-match data.

Safety Checklist and Practice

1. Confirm MySQL needs emulation rather than direct FULL syntax.
2. Name a non-nullable key for absence detection.
3. Repeat the exact relationship in both branches.
4. Use UNION ALL and keep only unmatched rows in branch two.
5. Verify matched, left-only and right-only counts independently.
6. Apply ORDER BY to the final combined result.

Practice: add class 40 with no students and predict seven rows; then add an assigned student in class 30 and explain how the origin counts change.

Official References

• MySQL 8.4 Reference Manual: JOIN Clause
• MySQL 8.4: Outer Join Optimization
• MySQL 8.4: EXPLAIN Statement

Syntax and optimizer notes were checked against the official MySQL 8.4 manual. Always test plans and row counts on your own schema and data.