SQL JOINs

Relational databases store data in separate tables to avoid redundancy — customers in one table, orders in another, products in a third. JOINs are how you bring that data back together when you need to answer questions that span multiple tables. Understanding which JOIN type to use — and why — is one of the most important skills in SQL, both for correctness and for performance.

Sample Schema

All examples use these three tables:

-- customers: people who placed orders
CREATE TABLE customers (
    id        SERIAL PRIMARY KEY,
    name      TEXT NOT NULL,
    country   TEXT
);

-- orders: each order belongs to one customer via the foreign key
CREATE TABLE orders (
    id          SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    amount      NUMERIC(10, 2),
    order_date  DATE
);

-- order_items: line items on each order (many-to-many between orders and products)
CREATE TABLE order_items (
    order_id   INTEGER REFERENCES orders(id),
    product    TEXT,
    quantity   INTEGER
);

INNER JOIN

INNER JOIN returns only rows where the join condition matches in both tables. If a customer has no orders, they won’t appear in the result. This is the right choice when you only care about records that have a matching relationship — for example, listing orders alongside their customer details, where an order without a customer is meaningless.

-- Returns only customers who have at least one order
SELECT c.name, o.id AS order_id, o.amount
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;

The ON clause specifies the join condition — almost always a foreign key relationship. The c and o are table aliases that keep the query readable.

LEFT JOIN (Left Outer Join)

LEFT JOIN returns all rows from the left table, plus matching rows from the right. When there’s no match, right-side columns come back as NULL. This is the right choice when the left table’s records matter regardless of whether a match exists — for example, listing all customers and counting their orders, including customers who haven’t ordered yet.

-- Show all customers, including those with no orders
SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;
-- Customers with zero orders appear with order_count = 0
-- because COUNT(o.id) ignores NULLs

Finding Rows With No Match

A classic SQL pattern: use LEFT JOIN + IS NULL to find records that have no related row. This is often more readable and performant than NOT IN with a subquery.

-- Customers who have never placed an order
SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;  -- no matching order row means the join produced NULL

RIGHT JOIN (Right Outer Join)

RIGHT JOIN is the mirror of LEFT JOIN — it keeps all rows from the right table. In practice, most developers rewrite right joins as left joins by swapping table order, since reading left-to-right is more natural. Both queries below return identical results.

-- Equivalent queries — the second is generally preferred for readability
SELECT c.name, o.id FROM orders o RIGHT JOIN customers c ON c.id = o.customer_id;
SELECT c.name, o.id FROM customers c LEFT JOIN orders o ON o.customer_id = c.id;

FULL OUTER JOIN

FULL OUTER JOIN returns all rows from both tables. Rows that don’t match get NULL on the missing side. This is useful for reconciliation queries — spotting rows in one table that should have a match in the other but don’t.

-- All customers and all orders, matched where possible
-- Unmatched customers show NULL order columns; unmatched orders show NULL customer columns
SELECT c.name, o.id AS order_id
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.id;

CROSS JOIN

CROSS JOIN returns the Cartesian product: every row from the left table paired with every row from the right. A table with 10 rows crossed with a table of 5 rows gives 50 rows. It’s rarely what you want accidentally, but very useful for generating combinations or scaffolding report structures.

-- Generate all possible (customer, month) combinations for a report scaffold
SELECT c.name, m.month_name
FROM customers c
CROSS JOIN (VALUES ('Jan'), ('Feb'), ('Mar')) AS m(month_name);

Warning: Forgetting the ON clause in a regular join accidentally creates a Cartesian product. Always double-check that your ON condition is present and correct.

Self-Join

A self-join joins a table to itself. The canonical example is an employee table where each row has a manager_id pointing to another row in the same table. Because the table appears twice, you must use different aliases to distinguish the two references.

CREATE TABLE employees (
    id         SERIAL PRIMARY KEY,
    name       TEXT,
    manager_id INTEGER REFERENCES employees(id)
);

-- Pair each employee with their manager's name
-- LEFT JOIN ensures the CEO (no manager) still appears in results
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;

Joining on Multiple Conditions

Sometimes a single foreign key isn’t enough to correctly match rows. When the relationship depends on more than one column — for example, matching against a price list that’s valid only within a date range — add the extra conditions to the ON clause with AND.

-- Match orders to a price list by both product and validity date range
SELECT o.id, o.product, pl.unit_price
FROM order_items o
JOIN price_list pl
  ON pl.product = o.product
 AND o.order_date BETWEEN pl.valid_from AND pl.valid_to;

Joining Three Tables

Chain JOINs to pull data from multiple tables in one query. Each JOIN clause adds one more table. The query engine resolves them left to right, so the order of joins can affect readability and sometimes performance.

-- Customer name, order date, and line items in a single query
SELECT c.name, o.order_date, oi.product, oi.quantity
FROM customers c
JOIN orders o       ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id   = o.id
WHERE c.country = 'US'
ORDER BY o.order_date DESC;

Key Takeaways

• Use INNER JOIN when you only want rows with matches on both sides.
• Use LEFT JOIN when the left table’s rows must always appear, even without a match.
• The LEFT JOIN + IS NULL pattern efficiently finds unmatched rows.
• Always specify an ON condition — a missing condition silently produces a Cartesian product.
• Table aliases (c, o) aren’t just cosmetic; they’re essential when joining a table to itself.