Course contentsShow
← Data Science
Lesson 936 of 2,14524. SQL JoinsFree lesson

FULL OUTER JOIN Syntax

Writing FULL OUTER JOIN queries and understanding the relationship between left, right, and full joins.

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.