FULL OUTER JOIN behavior in SQL

Introduction

A FULL OUTER JOIN helps you combine two tables and keep all rows from both, even if they don't match.

You want to see all customers and all orders, even if some customers have no orders and some orders have no customers.

You need to compare two lists and find all matches plus all differences.

You want to merge two sets of data and keep everything, filling missing parts with empty values.

You are analyzing data from two sources and want a complete view including unmatched records.

Syntax

SQL

SELECT columns
FROM table1
FULL OUTER JOIN table2
ON table1.common_column = table2.common_column;

FULL OUTER JOIN returns all rows from both tables.

If there is no match, columns from the other table will be NULL.

Examples

Shows all employees and all departments, matching where possible.

SQL

SELECT *
FROM employees
FULL OUTER JOIN departments
ON employees.department_id = departments.id;

Lists all orders and all customers, even if some orders have no customer or some customers have no orders.

SQL

SELECT orders.id, customers.name
FROM orders
FULL OUTER JOIN customers
ON orders.customer_id = customers.id;

Sample Program

This query shows all students and all scores. Students without scores and scores without students appear with NULLs.

SQL

CREATE TABLE students (id INT, name VARCHAR(10));
CREATE TABLE scores (student_id INT, score INT);

INSERT INTO students VALUES (1, 'Amy'), (2, 'Bob'), (3, 'Cara');
INSERT INTO scores VALUES (2, 85), (3, 90), (4, 75);

SELECT students.id, students.name, scores.score
FROM students
FULL OUTER JOIN scores
ON students.id = scores.student_id
ORDER BY students.id NULLS LAST, scores.student_id NULLS LAST;

Important Notes

FULL OUTER JOIN can be slower than INNER JOIN because it returns more data.

If your database does not support FULL OUTER JOIN, you can simulate it using UNION of LEFT and RIGHT JOIN.

NULL values mean no matching row was found in the other table.

Summary

FULL OUTER JOIN keeps all rows from both tables.

Unmatched rows show NULLs for missing data.

Useful to find all matches and all differences between two tables.