FULL OUTER JOIN Syntax
You'll learn how to write FULL OUTER JOIN queries and see how they combine the powers of LEFT and RIGHT joins together.
Understanding the Syntax
A FULL OUTER JOIN returns all rows from both tables, matching them where possible and filling in NULLs where no match exists. Think of it as taking a LEFT JOIN and a RIGHT JOIN, then merging their results—you get everything from both sides.
The basic syntax looks like this:
SELECT columns
FROM table_a
FULL OUTER JOIN table_b
ON table_a.key = table_b.key;
The Relationship Between Join Types
Here's how the joins relate to each other:
- LEFT JOIN: All rows from the left table + matches from the right
- RIGHT JOIN: All rows from the right table + matches from the left
- FULL OUTER JOIN: All rows from BOTH tables, matched where possible
Imagine two lists of people: customers and employees. An INNER JOIN finds people who are both. A LEFT JOIN finds all customers (whether they're employees or not). A FULL OUTER JOIN finds everyone—all customers, all employees, matching them if someone appears on both lists.
Practical Example
SELECT
customers.customer_name,
orders.order_id
FROM customers
FULL OUTER JOIN orders
ON customers.customer_id = orders.customer_id;
This returns all customers (even those with no orders) and all orders (even orphaned ones with no matching customer).
Key Takeaway: FULL OUTER JOIN syntax uses FULL OUTER JOIN with an ON condition to retrieve all rows from both tables, showing NULLs where no match exists on either side—it's the most inclusive join type.