FULL OUTER JOIN

Overview

A FULL OUTER JOIN (or FULL JOIN) combines the results of both LEFT JOIN and RIGHT JOIN, returning all rows from both tables matched where possible, or filled with NULLs where unmatched.

Learning Objectives

• Use FULL OUTER JOIN to reconcile datasets.
• Identify unmatched records on both sides simultaneously.
• Perform dataset audit comparisons.

Detailed Concept Explanation

FULL OUTER JOIN returns:

1. All matching rows between Table A and Table B.
2. Unmatched rows from Table A (with NULLs for Table B columns).
3. Unmatched rows from Table B (with NULLs for Table A columns).

Code Examples

SQL

-- Full audit of active employees vs payroll records
SELECT 
  e.emp_id AS hr_emp_id,
  e.name AS hr_name,
  p.emp_id AS payroll_emp_id,
  p.salary AS payroll_salary
FROM employees e
FULL OUTER JOIN payroll p ON e.emp_id = p.emp_id;

-- Find discrepancies (records missing in either HR or Payroll)
SELECT *
FROM employees e
FULL OUTER JOIN payroll p ON e.emp_id = p.emp_id
WHERE e.emp_id IS NULL OR p.emp_id IS NULL;

Best Practices

• Data Reconciliation: Use FULL OUTER JOIN during ETL validation to catch records present in source systems but missing in target target tables (or vice versa).

Interview Perspective

SELECT * FROM A LEFT JOIN B ON A.id = B.id
UNION
SELECT * FROM A RIGHT JOIN B ON A.id = B.id;

Interactive Challenges

Summary

FULL OUTER JOIN retains all records from both tables, pairing matches and padding unmatched sides with NULLs.