FULL JOIN

Full Join in MySQL

Introduction
Complete datasets require preserving ALL records from both tables regardless of matches.
MySQL lacks FULL OUTER JOIN but simulates with UNION of LEFT + RIGHT JOIN.
It is widely used for:

• Complete data reconciliation
• Missing relationship analysis
• Full dataset reporting

What is Full Join
FULL OUTER JOIN returns ALL rows from both tables with matches where possible.
Non-matching rows show NULLs from missing side.

MySQL FULL JOIN Alternative

sql

SELECT s.name, c.course_name 

FROM Students s 

LEFT JOIN Courses c ON s.course_id = c.id 

UNION 

SELECT s.name, c.course_name 

FROM Students s 

RIGHT JOIN Courses c ON s.course_id = c.id 

WHERE s.course_id IS NULL; 

Example

sql

-- All students + all courses (NULL where no match) 

SELECT s.id as student_id, s.name, c.id as course_id, c.course_name 

FROM Students s 

LEFT JOIN Courses c ON s.course_id = c.id 

UNION 

SELECT s.id as student_id, s.name, c.id as course_id, c.course_name 

FROM Students s 

RIGHT JOIN Courses c ON s.course_id = c.id 

WHERE s.id IS NULL; 

Simplified UNION Syntax
Cleaner column matching.

sql

SELECT s.name, c.course_name 

FROM Students s LEFT JOIN Courses c ON s.course_id = c.id 

UNION ALL 

SELECT s.name, c.course_name 

FROM Courses c LEFT JOIN Students s ON s.course_id = c.id 

WHERE s.course_id IS NULL; 

Full Join with WHERE
Filter after complete dataset.

sql

SELECT s.name, c.course_name 

FROM Students s LEFT JOIN Courses c ON s.course_id = c.id 

UNION 

SELECT s.name, c.course_name 

FROM Students s RIGHT JOIN Courses c ON s.course_id = c.id 

WHERE s.course_id IS NULL 

HAVING s.name IS NOT NULL OR c.course_name IS NOT NULL; 

Full Join with ORDER BY
Sort complete dataset.

sql

SELECT COALESCE(s.name, 'No Student') as student,

       COALESCE(c.course_name, 'No Course') as course

FROM Students s LEFT JOIN Courses c ON s.course_id = c.id

UNION

SELECT COALESCE(s.name, 'No Student'),

       COALESCE(c.course_name, 'No Course')

FROM Students s RIGHT JOIN Courses c ON s.course_id = c.id

WHERE s.course_id IS NULL

ORDER BY student, course;

Full Join with GROUP BY
Aggregate across complete set.

sql

SELECT COALESCE(s.city, 'Unknown') as city, COUNT(*) as total_records

FROM Students s LEFT JOIN Courses c ON s.course_id = c.id

UNION

SELECT COALESCE(s.city, 'Unknown'), COUNT(*)

FROM Students s RIGHT JOIN Courses c ON s.course_id = c.id

WHERE s.course_id IS NULL

GROUP BY COALESCE(s.city, 'Unknown');

Identifying Match Types
Use NULLs to categorize relationships.

sql

SELECT

    s.name,

    c.course_name,

    CASE

        WHEN s.name IS NULL THEN 'Course Only'

        WHEN c.course_name IS NULL THEN 'Student Only'

        ELSE 'Matched'

    END as relationship

FROM Students s LEFT JOIN Courses c ON s.course_id = c.id

UNION

SELECT s.name, c.course_name, relationship

FROM Students s RIGHT JOIN Courses c ON s.course_id = c.id

WHERE s.course_id IS NULL;

Performance Comparison

Method	Speed	Memory	Complexity
FULL JOIN (UNION)	Medium	High	Complex
INNER JOIN	Fastest	Low	Simple
LEFT JOIN	Fast	Medium	Simple

Full Join Multiple Tables
Complex complete datasets.

sql

-- Students + Courses + Departments (all combinations preserved)

SELECT s.name, c.course_name, d.dept_name

FROM Students s

LEFT JOIN Courses c ON s.course_id = c.id

LEFT JOIN Departments d ON c.dept_id = d.id

UNION

-- Additional RIGHT JOIN logic for complete coverage

SELECT s.name, c.course_name, d.dept_name

FROM Departments d

LEFT JOIN Courses c ON d.id = c.dept_id

LEFT JOIN Students s ON c.id = s.course_id

WHERE s.course_id IS NULL OR c.dept_id IS NULL;

Execution Order with Full Join
LEFT JOIN → RIGHT JOIN → UNION → WHERE → GROUP BY → ORDER BY → LIMIT

Performance Considerations

• UNION removes duplicates (use UNION ALL if duplicates OK)
• Index all join columns
• Large tables: consider separate LEFT/RIGHT queries
• COALESCE improves NULL readability

Important Notes

• MySQL lacks native FULL OUTER JOIN
• UNION LEFT + RIGHT = FULL JOIN
• Duplicate removal in UNION
• WHERE clauses affect completeness
• Complex but powerful for reconciliation

Example Scenario
Complete enrollment reconciliation report:

sql

SELECT

    COALESCE(s.student_id, 'MISSING') as student_id,

    COALESCE(s.name, 'No Student') as student_name,

    COALESCE(c.course_id, 'MISSING') as course_id,

    COALESCE(c.course_name, 'No Course') as course_name,

    CASE

        WHEN s.student_id IS NULL THEN 'Orphan Course'

        WHEN c.course_id IS NULL THEN 'Unenrolled Student'

        ELSE 'Enrolled'

    END as status

FROM Students s LEFT JOIN Enrollments e ON s.student_id = e.student_id

                LEFT JOIN Courses c ON e.course_id = c.course_id

UNION

SELECT student_id, student_name, course_id, course_name, status

FROM Courses c LEFT JOIN Enrollments e ON c.course_id = e.course_id

               LEFT JOIN Students s ON e.student_id = s.student_id

WHERE s.student_id IS NULL OR e.course_id IS NULL

ORDER BY status, student_name, course_name;

Common Mistakes

• WHERE filtering before UNION completeness
• UNION ALL creating duplicates
• Non-indexed join columns
• Complex logic readability
• Performance expectations vs INNER JOIN

Key Points to Remember

• FULL JOIN = LEFT + RIGHT UNION
• Preserves ALL rows from both tables
• NULLs show missing relationships
• UNION removes duplicates
• Index join columns critical
• Use COALESCE for readable NULLs