Inner and Outer Joins

The Problem Joins Solve

Relational databases store data across multiple normalized tables to avoid duplication. An orders table holds transaction facts. A customers table holds customer attributes. Neither table alone answers the question: "Which customers placed orders last month, and how much did each spend?"

A join combines rows from two or more tables based on a related column between them. The related column is called a join key or business key. In a well-designed schema, the join key is a foreign key in one table that references a primary key in another.

The Sample Schema

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    region VARCHAR(50)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    amount DECIMAL(10,2),
    status VARCHAR(20)
);

orders.customer_id is the foreign key that references customers.customer_id. This is the join key.

What a Join Actually Does

Conceptually, a join computes the Cartesian product of the two tables (every row of table A paired with every row of table B) and then filters that product using the join condition. If customers has 1,000 rows and orders has 10,000 rows, the Cartesian product has 10,000,000 rows. The join condition customers.customer_id = orders.customer_id filters that down to only the meaningful pairings.

This is why join performance matters. The database engine uses indexes and hash-based algorithms to avoid materializing the full Cartesian product, but understanding the conceptual model helps you reason about what a join produces.

Types of Joins at a Glance

The rest of this topic walks through each type with concrete examples.

INNER JOIN: Only the Matches

An INNER JOIN returns only rows where the join condition is satisfied in both tables. If a customer has no orders, that customer does not appear. If an order has no matching customer (a data quality issue), that order does not appear.

Syntax

SELECT c.name, o.order_id, o.amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

The ON clause specifies the join condition. Table aliases (c and o) keep the query readable.

What Gets Excluded

Consider this data:

customers

orders

An INNER JOIN on customer_id returns:

Carol is excluded because she has no orders. This is the defining behavior of INNER JOIN: it answers "show me the intersection."

When to Use INNER JOIN

Use INNER JOIN when you only care about rows that have a complete relationship. Examples:

• "Show me all orders with customer names" (you do not care about customers who never ordered)
• "List products that have been sold at least once"
• "Find employees assigned to a department"

A Common Mistake

Placing a filter on the right table in the WHERE clause instead of the ON clause can silently turn an OUTER JOIN into an INNER JOIN:

-- This looks like a LEFT JOIN but behaves like an INNER JOIN
SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.status = 'shipped';

The WHERE o.status = 'shipped' clause filters out all rows where o.status is NULL (customers with no orders), eliminating the very rows the LEFT JOIN was meant to preserve. To filter the right table while preserving left-table rows, put the condition in the ON clause:

SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
    AND o.status = 'shipped';

LEFT OUTER JOIN: Preserve the Left Table

A LEFT JOIN returns every row from the left table (the one before the JOIN keyword), plus matching rows from the right table. When no match exists, the right table's columns are filled with NULL.

Syntax

SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

Using the same sample data:

Carol appears with NULL values for the order columns. This is the critical difference from INNER JOIN.

Why This Matters Analytically

LEFT JOIN is the workhorse of analytical queries because it lets you answer questions like:

• "Which customers have NOT placed an order?" (filter WHERE o.order_id IS NULL)
• "What is each customer's total spend, including customers who spent nothing?" (use COALESCE(SUM(o.amount), 0))
• "Show all products and their sales, including products with zero sales"

Finding Unmatched Rows

The IS NULL anti-join pattern is one of the most useful techniques in SQL:

-- Customers who never placed an order
SELECT c.name, c.email
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

This works because the LEFT JOIN preserves all customers, and those without orders have NULL in every orders column. Filtering on IS NULL isolates exactly the unmatched rows.

RIGHT OUTER JOIN

A RIGHT JOIN is the mirror image: it preserves all rows from the right table and fills the left table's columns with NULL where no match exists.

SELECT c.name, o.order_id
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;

In practice, RIGHT JOIN is rarely used. Most SQL developers rewrite it as a LEFT JOIN by swapping the table order, because LEFT JOIN is more widely recognized and easier to read:

-- Equivalent to the RIGHT JOIN above
SELECT c.name, o.order_id
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id;

Chaining Multiple LEFT JOINs

You can chain LEFT JOINs to build a complete picture from a central table:

SELECT c.name, o.order_id, p.product_name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
LEFT JOIN order_items oi ON o.order_id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.product_id;

Each LEFT JOIN preserves the rows accumulated so far. This pattern is common in star-schema queries where a central fact table is surrounded by dimension tables.

FULL OUTER JOIN: Preserve Both Tables

A FULL OUTER JOIN returns all rows from both tables. Where a match exists, the columns are combined. Where no match exists, the missing side's columns are filled with NULL.

Syntax

SELECT c.name, o.order_id, o.amount
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id;

When You Need FULL OUTER JOIN

FULL OUTER JOIN answers the question: "Show me everything from both sides, and tell me what matches and what does not."

Common use cases:

• Data reconciliation: Compare two systems' records to find rows present in one but not the other.
• Audit queries: Identify orphaned foreign keys (orders without customers) and missing references (customers without orders) in a single query.
• Merging datasets: Combine two overlapping but non-identical datasets.

Example: Finding Data Quality Issues

SELECT
    c.customer_id AS cust_id,
    c.name,
    o.order_id,
    CASE
        WHEN c.customer_id IS NULL THEN 'Orphan order'
        WHEN o.order_id IS NULL THEN 'Customer with no orders'
        ELSE 'Matched'
    END AS match_status
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id;

This query surfaces three categories: matched rows, orphan orders (no customer), and customers with no orders. Each category can then be counted or investigated.

FULL OUTER JOIN Is Not Universally Supported

MySQL does not support FULL OUTER JOIN directly. The standard workaround is a UNION of a LEFT JOIN and a RIGHT JOIN:

SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id

UNION

SELECT c.name, o.order_id
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;

PostgreSQL, SQL Server, and Oracle support FULL OUTER JOIN natively. SQLite added support in version 3.39.

Choosing the Right Join Type

The decision framework is straightforward:

1. Do you only want matched rows? Use INNER JOIN.
2. Do you want all rows from one table plus matches from the other? Use LEFT JOIN (or RIGHT JOIN, but prefer LEFT for readability).
3. Do you want all rows from both tables? Use FULL OUTER JOIN.

The analytical question drives the join type. "Show me customers and their orders" is ambiguous. "Show me all customers, including those without orders" is a LEFT JOIN. "Show me only customers who have ordered" is an INNER JOIN. Precision in the question determines precision in the query.