FULL OUTER JOIN availability across databases in SQL

Introduction

A FULL OUTER JOIN helps you combine all rows from two tables, showing matches and also rows without matches from both sides. But not all databases support it the same way.

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 differences and matches between them.

You want to merge two datasets fully, keeping all information from both.

You are working with two tables and want to find unmatched rows on either side.

Syntax

SQL

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

FULL OUTER JOIN returns all rows from both tables, matching where possible.

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

Examples

Shows all employees and all departments, matching where dept_id matches.

SQL

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

Combines all rows from tableA and tableB by id, including unmatched rows.

SQL

SELECT * FROM tableA FULL OUTER JOIN tableB ON tableA.id = tableB.id;

Sample Program

This query shows all rows from A and B. Alice has no matching color, and Blue has no matching name.

SQL

CREATE TABLE A (id INT, name VARCHAR(10));
CREATE TABLE B (id INT, color VARCHAR(10));

INSERT INTO A VALUES (1, 'Alice'), (2, 'Bob');
INSERT INTO B VALUES (2, 'Red'), (3, 'Blue');

SELECT A.id, A.name, B.id, B.color
FROM A
FULL OUTER JOIN B ON A.id = B.id
ORDER BY COALESCE(A.id, B.id);

Important Notes

Not all databases support FULL OUTER JOIN directly (e.g., MySQL before 8.0 does not).

You can simulate FULL OUTER JOIN using UNION of LEFT JOIN and RIGHT JOIN if needed.

Always check your database documentation for FULL OUTER JOIN support.

Summary

FULL OUTER JOIN returns all rows from both tables, matching where possible.

Some databases do not support FULL OUTER JOIN directly.

You can use UNION of LEFT JOIN and RIGHT JOIN to mimic FULL OUTER JOIN.