SQL JOINs: combining related tables correctly

Why do we need JOINs?

Relational databases usually store different kinds of information in separate tables. Customer information might be stored in a customers table, and orders stored separately:

The customers table tells us who the customer is and where they live; the orders table tells us what they ordered and the order status. The connection between the tables is customers.customer_id to orders.customer_id. For example, orders.customer_id = 1 refers to customers.customer_id = 1, which is Asha.

INNER JOIN: return rows that match

Suppose the requirement is “show every order together with the customer’s name and country.” The order information comes from orders, the customer information from customers. We can combine them using INNER JOIN:

SELECT
    o.order_id,
    c.customer_name,
    c.country,
    o.product,
    o.status
FROM orders AS o
INNER JOIN customers AS c
    ON c.customer_id = o.customer_id
ORDER BY o.order_id;

The important part is ON c.customer_id = o.customer_id, which tells SQL how rows from the two tables are related. You can read the query as: for each order, find the customer whose customer_id matches the order’s customer_id.

INNER JOIN returns only rows for which a match exists on both sides. Esha exists in customers (customer_id = 5), but there is no order with customer_id = 5, so Esha does not appear in the INNER JOIN result. Table aliases (orders → o, customers → c) keep the query readable and, when both tables contain a customer_id, make it clear which column comes from which table.

LEFT JOIN: keep every row from the left table

Now consider a different requirement: “show every customer and any orders they have placed, including customers who have never ordered.” An INNER JOIN will not work because Esha has no matching order and would disappear. Use LEFT JOIN:

SELECT
    c.customer_id,
    c.customer_name,
    o.order_id,
    o.product,
    o.status
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
ORDER BY
    c.customer_id,
    o.order_id;

Esha is preserved because she belongs to the left table. There is no matching order, so SQL fills the order columns with NULL. You can read LEFT JOIN as: keep every row from the left table, and add matching information from the right table when it exists.

Direction matters. FROM customers LEFT JOIN orders preserves every customer, while FROM orders LEFT JOIN customers preserves every order. So before choosing LEFT JOIN, ask: which table contains the rows that must never disappear? Place that table on the left.

The LEFT JOIN that quietly loses unmatched rows

Suppose the requirement is: show every customer and any paid orders they have; customers without a paid order should still appear. A natural query uses LEFT JOIN to preserve customers, then WHERE o.status = 'paid' to keep paid orders. At first glance, this seems reasonable. But the result does not preserve every customer.

SELECT
    c.customer_name,
    o.order_id,
    o.status
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
WHERE o.status = 'paid'
ORDER BY c.customer_name;

Put the right-table filter in ON

The requirement is “preserve every customer, but match only paid orders.” That condition belongs in the join itself:

SELECT
    c.customer_name,
    o.order_id,
    o.status
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
   AND o.status = 'paid'
ORDER BY c.customer_name;

Now o.status = 'paid' controls which order rows are allowed to match. It does not decide whether the customer survives.

A practical rule for LEFT JOIN filters

When using a LEFT JOIN, ask: is this condition part of deciding which right-side rows should match, or should it remove completed result rows? “Keep all customers, but match only paid orders” goes in ON. “Return only customers whose joined order is paid” may be exactly what WHERE o.status = 'paid' is for. The placement should follow the business requirement.

RIGHT, FULL, and choosing a join

RIGHT JOIN is the mirror image of LEFT JOIN: it keeps every row from the right table. FROM orders AS o RIGHT JOIN customers AS c ON … preserves every customer, so Esha appears even though she has no order. This produces the same general result as putting customers on the left of a LEFT JOIN. For readability, programmers prefer LEFT JOIN and simply arrange the tables so the rows that must be preserved are on the left. You still need to understand RIGHT JOIN when reading existing SQL.

FULL JOIN, often written FULL OUTER JOIN, preserves rows from both sides: matching rows are combined, unmatched left rows are kept, and unmatched right rows are kept. It is useful for reconciliation problems such as “which records exist in both datasets, which only on the left, and which only on the right?” Database support for FULL JOIN can vary, so check the syntax available in the system you are using.

CROSS JOIN: every combination of rows

Most joins connect rows based on a relationship. CROSS JOIN is different: it creates every possible combination between two input sets. Suppose we have a shipping_methods table with Standard, Express, and Same Day. Then SELECT c.customer_name, s.shipping_method FROM customers AS c CROSS JOIN shipping_methods AS s produces, for Asha, Asha | Standard, Asha | Express, Asha | Same Day, and the same three combinations for every other customer. There are 5 customers × 3 shipping methods = 15 rows. No ON condition is used because every row is matched with every row.

CROSS JOIN can be intentional, for example generating every customer and shipping-method combination, or every product and month, or every region and reporting period. But it can also produce very large results: 1,000 rows crossed with 10,000 rows produces 10,000,000 combinations. So always ask whether every possible pair is actually required.

Self joins, row counts, and more tables

A join does not require two different tables. Sometimes rows in one table refer to other rows in the same table. The referred_by_customer_id column points to another row in the same customers table: Ben.referred_by_customer_id = 1, and customer 1 is Asha, so Asha referred Ben. To show each customer together with the person who referred them, we can join customers to itself:

SELECT
    c.customer_name AS customer,
    ref.customer_name AS referred_by
FROM customers AS c
LEFT JOIN customers AS ref
    ON ref.customer_id = c.referred_by_customer_id
ORDER BY c.customer_id;

There is still only one physical customers table. The aliases let it play two logical roles: c is the customer, ref is the referring customer. This is called a self join. Without aliases, customers.customer_id would be ambiguous because customers appears twice. Self joins are commonly used with data such as employees and managers, categories and parent categories, or accounts and parent accounts.

Joining more than two tables

Real queries often combine several related tables. If product information is stored in a products table and orders stores the corresponding product_id, we can combine customers, orders, and products with two joins, one per relationship. A query with several joins is easier to reason about when each relationship is checked individually: which customer owns this order, and which product does this order contain.

How to spot join problems

When results look wrong, inspect: which rows should be preserved? What defines a match? How many matches can each row have? Are filters in ON or WHERE intentionally?

Answer four questions before writing a join

One more prediction

Requirement: return every customer and any paid orders they have; customers without a paid order must still appear. Which query correctly expresses the requirement?

-- Option A
FROM customers c
INNER JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'paid'

-- Option B
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'paid'

-- Option C
FROM customers c
LEFT JOIN orders o
    ON o.customer_id = c.customer_id
   AND o.status = 'paid'

-- Option D
FROM customers c
CROSS JOIN orders o
WHERE o.status = 'paid'

Summary

A JOIN combines rows based on relationships between tables. Use INNER JOIN when only matching rows should remain, LEFT JOIN when every row from the left table must remain, RIGHT JOIN when every row from the right table must remain, FULL JOIN when unmatched rows from both sides must remain, and CROSS JOIN when every possible combination is intentionally required. A self join lets one table play multiple roles.

Next, we will build on joins by learning how to find rows that do or do not have related matches using EXISTS, NOT EXISTS, semi-join, and anti-join patterns.