FULL OUTER JOIN

FULL OUTER JOIN

The FULL OUTER JOIN keyword returns all rows from both tables, with nulls where there is no match.

💡 Returns ALL rows from BOTH tables, with NULLs where there is no match

Visual Diagram

Table A       JOIN        Table B
┌─────────┐             ┌─────────┐
│  Rows   │  ────────→  │  Rows   │
│  from   │  matched    │  from   │
│  A      │  by key     │  B      │
└─────────┘             └─────────┘

Basic Syntax

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

Example

SELECT
    c.first_name,
    c.last_name,
    o.total AS order_total
FROM customers c
FULL OUTER JOIN orders o ON c.id = o.customer_id;

⚠️ Always specify the join condition in the ON clause. Omitting it creates a CROSS JOIN (Cartesian product)!

✅ Key Takeaways

1. FULL OUTER JOIN combines rows from two tables based on a related column
2. The ON clause specifies how the tables are related
3. Use table aliases to make queries more readable
4. Different join types determine which rows are included
5. Practice joins with sample data to understand the differences