FULL OUTER JOIN
In this page:
What is a FULL OUTER JOIN?
A FULL OUTER JOIN combines LEFT and RIGHT joins into one result set. It keeps all rows from both tables, showing matches and filling missing data with NULL on whichever side has no corresponding row.
Example: What is a FULL OUTER JOIN?
CREATE TABLE customers (customer_id INT, name TEXT);
CREATE TABLE orders (order_id INT, customer_id INT);
INSERT INTO customers VALUES (1, 'Amit'), (2, 'Priya');
INSERT INTO orders VALUES (101, 1), (102, 3);
SELECT customers.name, orders.order_id
FROM customers LEFT JOIN orders ON customers.customer_id = orders.customer_id
UNION
SELECT customers.name, orders.order_id
FROM customers RIGHT JOIN orders ON customers.customer_id = orders.customer_id;Why MySQL Has No Native Keyword
MySQL does not support the FULL JOIN keyword directly, unlike PostgreSQL or SQL Server. You can get the same result by combining LEFT and RIGHT joins using UNION, since together they cover every row from both tables.
Example: Why MySQL Has No Native Keyword
CREATE TABLE customers (customer_id INT, name TEXT);
CREATE TABLE orders (order_id INT, customer_id INT);
INSERT INTO customers VALUES (1, 'Amit'), (2, 'Priya');
INSERT INTO orders VALUES (101, 1), (102, 3);
-- MySQL has no FULL JOIN keyword -- simulate it with LEFT + RIGHT via UNION
SELECT customers.name, orders.order_id FROM customers LEFT JOIN orders ON customers.customer_id = orders.customer_id
UNION
SELECT customers.name, orders.order_id FROM customers RIGHT JOIN orders ON customers.customer_id = orders.customer_id;Using UNION ALL safely
Using UNION removes duplicate rows automatically, which is usually what you want when simulating a FULL JOIN. If you want to keep duplicate rows instead, use UNION ALL and filter the right-side query so matched rows aren't counted twice.
Example: Using UNION ALL safely
CREATE TABLE customers (customer_id INT, name TEXT);
CREATE TABLE orders (order_id INT, customer_id INT);
INSERT INTO customers VALUES (1, 'Amit');
INSERT INTO orders VALUES (101, 1);
SELECT customers.name, orders.order_id FROM customers LEFT JOIN orders ON customers.customer_id = orders.customer_id
UNION ALL
SELECT customers.name, orders.order_id FROM customers RIGHT JOIN orders ON customers.customer_id = orders.customer_id
WHERE customers.customer_id IS NULL;Finding Exclusive Rows
You can find rows that exist only in one table but not both, such as customers with no orders and orders with no matching customer. To do this, use a simulated FULL JOIN with IS NULL checks on both sides of the union.
Example: Finding Exclusive Rows
CREATE TABLE customers (customer_id INT, name TEXT);
CREATE TABLE orders (order_id INT, customer_id INT);
INSERT INTO customers VALUES (1, 'Amit'), (2, 'Priya');
INSERT INTO orders VALUES (101, 3);
SELECT customers.name, NULL AS order_id FROM customers LEFT JOIN orders ON customers.customer_id = orders.customer_id WHERE orders.order_id IS NULL
UNION
SELECT NULL, orders.order_id FROM customers RIGHT JOIN orders ON customers.customer_id = orders.customer_id WHERE customers.customer_id IS NULL;Comprehensive Aggregation
A simulated FULL JOIN is useful for merging and summarizing metrics from two separate sets of data that don't perfectly overlap, giving you a complete picture instead of silently dropping the unmatched rows from either side.
Example: Comprehensive Aggregation
CREATE TABLE online_sales (product TEXT, amount INT);
CREATE TABLE store_sales (product TEXT, amount INT);
INSERT INTO online_sales VALUES ('Keyboard', 100), ('Mouse', 50);
INSERT INTO store_sales VALUES ('Mouse', 30), ('Monitor', 200);
SELECT product, SUM(amount) AS total FROM (
SELECT * FROM online_sales
UNION ALL
SELECT * FROM store_sales
) combined
GROUP BY product;Chapter Quiz — Complete all 10 topics to unlock
0/10 topics done
Complete these topics first: