SQL JOINs: Combining Tables Correctly

A relational database stores similar kinds of information in one table. Multiple tables are used for storing different kinds of information. In our case here, customer details are stored in the customers table, while order details are stored in the orders table. If you look carefully, you will find the customer_id column, which is common in both tables. We say that the customer_id column connects the two tables. For example, order 1001 has customer_id = 1, which refers to Asha.

Asha has placed two orders. This is called a one-to-many relationship. It means one customer can have multiple orders. Here, customers is the one side, and orders is the many side.

Before we start, notice two special cases:

• Order 1007 has no customer_id because it was placed by a guest.
• Esha (customer 5) has not placed any orders yet.

These examples will help us understand what happens when a row has no match or when one row matches several rows in another table.

Suppose we want to show each order along with the customer’s name and country. Order details are stored in orders, while customer details are stored in 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 ON condition tells SQL how to match rows from the two tables. For each order, SQL looks for a customer with the same customer_id.

Because we use INNER JOIN, only orders with a matching customer are included.

The aliases o and c are shorter names for orders and customers. They make the query easier to read and show which table each column belongs to. This is especially useful when both tables contain a column named customer_id.

Now, let’s say the requirement has changed: show every customer along with their orders, including customers who have never placed an order. Here, using an INNER JOIN would exclude Esha because she has no orders. A LEFT JOIN ensures she is included.

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;

A LEFT JOIN keeps every row from the left table and adds matching rows from the right table. When there is no match, SQL still includes the row from the left table but fills the columns from the right table with NULL. This is why Esha appears with NULL values for her order details. Such a row is called a NULL-extended row.

The result contains 7 rows. Customers with multiple orders appear once for each matching order, just as they would with an INNER JOIN. The key difference is that a LEFT JOIN also includes customers who have no matching orders.

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

The requirement is to show every customer along with their paid orders. Customers who have never placed a paid order must still appear.

A first attempt might be to use a LEFT JOIN to include all customers, followed by a WHERE clause to filter for paid orders. The query runs without errors, but does it produce the expected result?

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, o.order_id;

The requirement has two parts: include every customer and show only their paid orders.

The paid-order condition determines which orders can match a customer, so it belongs in the ON clause rather than WHERE.

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, o.order_id;

Now, only paid orders can match. Dev’s cancelled order does not qualify, but Dev still appears with NULL values for his order details. Esha also remains, even though she has never placed an order.

The important difference is that the condition filters matching orders without removing customers.

If the requirement were instead to “show each paid order with its customer’s name,” the 4-row result from section 04 would be correct. In that case, an INNER JOIN restricted to paid orders would express the requirement more clearly.

What happens when the additional condition in ON filters the left table instead?

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

Where should we put a condition?

For a LEFT JOIN, the correct placement depends on whether the condition should control which rows match or which rows remain in the result.

LEFT JOIN and GROUP BY are often used together to summarise data.

The requirement is to count the orders placed by each customer, including customers who have never ordered. Those customers should have an order count of 0.

A LEFT JOIN ensures every customer appears, while GROUP BY produces one summary row per customer. However, the choice of COUNT expression matters.

SELECT
    c.customer_name,
    COUNT(*) AS orders
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.customer_name
ORDER BY c.customer_id;

COUNT(*) vs COUNT(column)

Orders are stored in orders table, while their payments are in payments table. Most orders have a single payment, but some are paid using multiple methods. For example, Carla split the payment for order 1003 into two transactions.

Finance needs two metrics: the total value of paid orders and the number of payments received.

A natural approach is to join paid orders with their payments and calculate both metrics using aggregate functions. But joining these tables introduces a problem.

SELECT
    SUM(o.amount)       AS order_value,
    COUNT(p.payment_id) AS payments
FROM orders AS o
INNER JOIN payments AS p
    ON p.order_id = o.order_id
WHERE o.status = 'paid';

Detecting join fan-out

Before using aggregates on joined tables, check whether the join can produce multiple matches. Here, orders has one row per order, while payments can have multiple rows for the same order_id.

To identify potential fan-out, check whether any order_id appears more than once in payments.

If it does, joining these tables will repeat the corresponding order details.

Why SUM(DISTINCT) is not the solution

You might try SUM(DISTINCT o.amount) to avoid counting repeated amounts. However, DISTINCT removes duplicate values, not duplicate orders.

For example, orders 1003 and 1006 both have an amount of 3,600. These are two different orders, and both should contribute to the total.

SUM(DISTINCT o.amount) counts 3,600 only once, incorrectly excluding one of the orders.

The problem is not duplicate amounts. It is that the join produces multiple rows for the same order.

-- An attempt to solve using DISTINCT returns wrong result as well.
SELECT SUM(DISTINCT o.amount) AS order_value
FROM orders AS o
INNER JOIN payments AS p
    ON p.order_id = o.order_id
WHERE o.status = 'paid';

The fix: Aggregate payments before joining

Instead of joining individual payments directly to orders, first summarise payments to produce one row per order.

The approach is:

1. Group payments by order_id and count the payments for each order.
2. Join the summarised payments with the paid orders.
3. Calculate the total order value and total payment count.

This ensures each order appears only once in the joined result, while its payment count still includes every payment.

The inner query used in the FROM clause is called a derived table, which we will explore in detail in P10.

SELECT
    SUM(o.amount)    AS order_value,
    SUM(p.payments)  AS payments,
    SUM(p.collected) AS collected
FROM orders AS o
INNER JOIN (
    SELECT
        order_id,
        COUNT(*)    AS payments,
        SUM(amount) AS collected
    FROM payments
    GROUP BY order_id
) AS p
    ON p.order_id = o.order_id
WHERE o.status = 'paid';

With this approach, both metrics are correct:

A join condition does not always need to compare two columns for equality. The ON clause can use any condition that determines whether two rows match. Two common examples are range joins, where a value must fall within a defined range, and time-window joins, where an event must occur within a specific period.

Range joins

The sales team wants to classify orders by their amount. They use a lookup table called order_tiers to define three categories:

Unlike the joins we have seen so far, orders and order_tiers do not share a common key. Instead, an order matches a tier when its amount falls within that tier’s range. We can express this condition using BETWEEN.

SELECT
    o.order_id,
    o.amount,
    t.tier
FROM orders AS o
INNER JOIN order_tiers AS t
    ON o.amount BETWEEN t.min_amount AND t.max_amount
ORDER BY o.order_id, t.min_amount;

Fixing overlapping ranges

To prevent an amount from matching two tiers, define each range as half-open:

• Include the minimum value using >=.
• Exclude the maximum value using <.

SELECT
    o.order_id,
    o.amount,
    t.tier
FROM orders AS o
INNER JOIN order_tiers AS t
    ON o.amount >= t.min_amount
   AND o.amount <  t.max_amount
ORDER BY o.order_id, t.min_amount;

Now, an amount of 1,500 belongs only to medium, because small excludes its upper boundary.

The result now contains 7 rows, one for each order.

Because each range starts exactly where the previous one ends, every amount from 0 (inclusive) to 100,000 (exclusive) belongs to exactly one tier.

However, range joins can also exclude rows. If an order amount is below 0 or at least 100,000, it will not match any tier and will disappear from an INNER JOIN result. Use a LEFT JOIN instead when every order must appear, even if no tier matches.

Time-window joins

Range conditions are also useful when matching events based on time. Consider a warehouse that records shipments in the shipments table.

Let’s say our new requirement is to, show every paid order, including its shipment date if it was shipped within 2 days of the order date. If it was shipped later or has not shipped, show NULL for the shipment date.

SELECT
    o.order_id,
    o.order_date,
    s.shipped_date
FROM orders AS o
LEFT JOIN shipments AS s
    ON s.order_id = o.order_id
   AND s.shipped_date >= o.order_date
   AND s.shipped_date <= o.order_date + INTERVAL '2' DAY
WHERE o.status = 'paid'
ORDER BY o.order_id;

Why does the time condition belong in ON?

The join needs to check three conditions before attaching a shipment to an order:

1. The shipment belongs to the same order.
2. The shipment date is not earlier than the order date.
3. The shipment date is no more than 2 days after the order date.

All three conditions belong in ON because they determine whether a shipment qualifies as a match.

Consider order 1003. It was placed on May 3 but shipped on May 6, which is 3 days later. The shipment does not satisfy the time-window condition, so it cannot match. However, because this is a LEFT JOIN, order 1003 still appears with a NULL shipment date. Order 1007 also remains, even though it has no matching shipment.

The condition o.status = 'paid' serves a different purpose. It determines which orders should appear in the final result, so it belongs in WHERE.

This follows the same distinction we learned earlier: ON controls matching rows, while WHERE filters the result.

A note on date arithmetic

The expression:

o.order_date + INTERVAL '2' DAY

adds two days to the order date. This syntax works in DuckDB, PostgreSQL and MySQL.

Real-world queries often combine three or more tables. The easiest way to understand these queries is to examine one join at a time.

The requirement is to show every customer, their orders, and each order’s shipment date. Customers who have never ordered must still appear, and orders that have not shipped should show NULL for the shipment date.

Here, we need two joins:

1. Join customers with orders to retrieve each customer’s orders.
2. Join the result with shipments to retrieve shipment dates.

The first join uses a LEFT JOIN to preserve every customer. But what happens if the second join uses an INNER JOIN?

SELECT
    c.customer_name,
    o.order_id,
    s.shipped_date
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
INNER JOIN shipments AS s
    ON s.order_id = o.order_id
ORDER BY c.customer_id, o.order_id;

Why did some customers disappear?

To understand the result, follow the joins in the order they are written.

Step 1: LEFT JOIN customers with orders

The first join produces 7 rows, as we saw in section 03. Every customer appears, including Esha, who has no orders. Her order_id is NULL. At this point, the requirement to preserve every customer is satisfied.

Step 2: INNER JOIN the result with shipments

The second join operates on the rows produced by the first join. Because it is an INNER JOIN, it keeps only rows whose order_id matches a record in shipments.

This changes the result:

• Esha disappears because her order_id is NULL, so it cannot match a shipment.
• Dev disappears because his only order, 1004, has not shipped.
• Ben loses order 1002 because it has not shipped, but he still appears through order 1006.
• Asha and Carla remain because their orders have matching shipment records.

The important point is that the second INNER JOIN removes rows preserved by the first LEFT JOIN.

A LEFT JOIN does not guarantee that its preserved rows will survive later joins. Each subsequent join can change which rows remain.

The fix: Use LEFT JOIN for shipments too

We want every customer to remain, even if they have no orders or their orders have not shipped.

To achieve this, change the second join to a LEFT JOIN:

SELECT
    c.customer_name,
    o.order_id,
    s.shipped_date
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
LEFT JOIN shipments AS s
    ON s.order_id = o.order_id
ORDER BY c.customer_id, o.order_id;

Now both joins preserve unmatched rows:

• The first LEFT JOIN keeps every customer, even those without orders.
• The second LEFT JOIN keeps every row from the first join, even when there is no matching shipment.

As a result, Ben’s unshipped order 1002 and Dev’s unshipped order 1004 remain with NULL shipment dates. Esha also remains, with NULL for both order and shipment details. The query now meets the requirement.

RIGHT JOIN

A RIGHT JOIN works like a LEFT JOIN, but preserves the rows from the right table instead of the left. It keeps every row from the table written after RIGHT JOIN, even when no matching row exists in the first table.

For example, the following query shows every order, including orders that have not shipped.

SELECT
    o.order_id,
    o.status,
    s.shipment_id
FROM shipments AS s
RIGHT JOIN orders AS o
    ON o.order_id = s.order_id
ORDER BY o.order_id;

Here, orders is the right table, so every order remains in the result.

Orders 1002, 1004 and 1007 have no matching shipment, so their shipment_id is NULL. Shipment S5 is excluded because it has no matching order, and unmatched rows from the left table are not preserved.

We could produce the same result by writing orders LEFT JOIN shipments. In practice, a LEFT JOIN is often easier to read because it places the table whose rows must be preserved first.

FULL JOIN

A FULL JOIN, also called a FULL OUTER JOIN, preserves unmatched rows from both tables. This is especially useful for reconciliation, where we compare two sources to find records that are missing from either side.

Consider a reconciliation between orders and shipments. Finance wants to identify orders that have not shipped, while the warehouse wants to find shipments that reference orders missing from the orders table.

For example, shipment S5 references order 1010, but order 1010 does not exist in orders. A FULL JOIN lets us examine both types of missing matches in one result.

SELECT
    o.order_id,
    o.status,
    s.shipment_id
FROM orders AS o
FULL JOIN shipments AS s
    ON s.order_id = o.order_id
ORDER BY o.order_id;

Showing the correct order ID

For reconciliation, we want to display the order ID regardless of which table contains it. We can use COALESCE to select the first non-NULL value from o.order_id and s.order_id.

We can also use a CASE expression to identify whether each row represents a matched order, an order without a shipment, or a shipment without an order.

SELECT
    COALESCE(o.order_id, s.order_id) AS order_id,
    o.status,
    s.shipment_id,
    CASE
        WHEN o.order_id IS NULL THEN 'shipment with no order'
        WHEN s.order_id IS NULL THEN 'not shipped'
        ELSE 'shipped'
    END AS reconciliation
FROM orders AS o
FULL JOIN shipments AS s
    ON s.order_id = o.order_id
ORDER BY 1;

The result now provides a clear reconciliation status for every record:

• shipped: The order has a matching shipment.
• not shipped: The order exists, but no matching shipment was found.
• shipment with no order: The shipment exists, but its order is missing from orders.

This answers both teams’ questions in one query.

Order 1007 is marked as paid but has no shipment, while shipment S5 references an order that does not exist in the order records. Both may need investigation.

USING instead of ON?

So far, we have used ON to specify which columns should match in a join. When both tables have a column with the same name, SQL also allows us to use USING.

Consider our earlier FULL JOIN:

SELECT
    o.order_id,
    s.order_id AS shipment_order_id,
    s.shipment_id
FROM orders AS o
FULL JOIN shipments AS s
    ON o.order_id = s.order_id;

For shipment S5, the two order ID columns contain different values:

This happens because ON keeps the two columns separate. The order ID from orders is NULL because there is no matching order, while the order ID from shipments is 1010.

Now consider the same join written with USING:

SELECT
    order_id,
    s.shipment_id
FROM orders AS o
FULL JOIN shipments AS s
    USING (order_id);

The result for shipment S5 is:

What changed?

USING (order_id) tells SQL two things:

1. Match rows using the order_id column from both tables.
2. Produce a single combined order_id column when that column is selected without a table prefix.

For a FULL JOIN, the combined column uses the available order ID from either table.

• If both tables match, their order IDs are equal, so that value is shown.
• If only orders has a row, its order ID is shown.
• If only shipments has a row, its order ID is shown.

This is similar to writing COALESCE(o.order_id, s.order_id) when using ON.

Important: USING does not change which rows match or which unmatched rows survive. It only simplifies the equality condition and combines the shared key in the output.

Use USING when the matching columns have the same name and are compared for equality. Use ON when column names differ or when the matching condition requires additional comparisons.

In practice, the ON form is more flexible because it also supports columns with different names and more complex matching conditions.

Database support: PostgreSQL and DuckDB support FULL JOIN. MySQL does not support it directly. In MySQL, we can produce the equivalent result by combining a LEFT JOIN with unmatched rows from the other table using UNION ALL.

Comparing the four join types

All four joins include rows that satisfy the matching condition. The difference is which unmatched rows they preserve.

When choosing a join, ask yourself: Which rows must remain even when no match exists?

• Use INNER JOIN when only matching rows matter.
• Use LEFT JOIN when every row from the left table must remain.
• Use RIGHT JOIN when every row from the right table must remain.
• Use FULL JOIN when unmatched rows from both tables must remain.

CROSS JOIN: Every possible combination

The joins we have seen so far use a matching condition to connect related rows. A CROSS JOIN works differently: it combines every row from the first table with every row from the second table. It does not need an ON clause because there is no matching condition.

Consider a business that offers two delivery options:

The requirement is to show every customer the available delivery options and their fees.

SELECT
    c.customer_name,
    m.method,
    m.fee
FROM customers AS c
CROSS JOIN shipping_methods AS m
ORDER BY c.customer_id, m.fee;

The result contains 10 rows: 5 customers × 2 delivery methods.

Each customer appears twice, once for Standard delivery and once for Express delivery. If the business offered three delivery methods, each customer would appear three times, producing 15 rows. This is called a Cartesian product, where every row from one table is paired with every row from another.

When is a CROSS JOIN useful?

A CROSS JOIN is useful when we intentionally need every possible combination. For example, suppose a sales report must show every product for every month, including months when a product had no sales.

We could first use a CROSS JOIN to generate every product-month combination. Then, a LEFT JOIN could attach the actual sales data. For months with no sales, COALESCE could replace the missing total with 0. This ensures the report includes every product and every month, even when no sales occurred.

Watch out for accidental CROSS JOINs

SQL also supports an older syntax where tables are separated by commas in the FROM clause, with matching conditions written in WHERE.

If the matching condition is missing, SQL combines every row from both tables.

For example:

SELECT COUNT(*) AS row_count
FROM customers, orders;

There are 5 customers and 7 orders, so the query produces:

5 × 7 = 35 rows

However, only 6 orders have a matching customer in our data. Instead of those 6 matching pairs, the query creates every possible customer-order combination, including combinations that have no relationship.

When a join returns more rows than expected, check whether the matching condition is missing, incorrect, or allows multiple matches.

Self joins: One table, two roles

So far, we have joined different tables. But SQL also allows a table to be joined with itself. This is called a self join.

A self join is useful when rows in a table refer to other rows in the same table.

Consider the customers table. It contains a column named referred_by_customer_id, which stores the ID of the customer who referred someone. For example, Ben has referred_by_customer_id = 1. Customer 1 is Asha, which means Asha referred Ben.

The requirement is to show every customer alongside the name of the customer who referred them. Customers without a referrer must still appear.

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;

How does a table join with itself?

Although customers appears twice in the query, there is still only one physical table.

We use two aliases to give it different roles:

• c represents the customer whose details we want to display.
• ref represents the referrer, whose name we want to retrieve.

The join condition connects these two roles:

ON ref.customer_id = c.referred_by_customer_id

For Ben, c.referred_by_customer_id is 1. SQL looks for a row where ref.customer_id = 1, finds Asha, and returns her name as referred_by.

The same process applies to every customer. Aliases are important because the same table appears twice. They allow SQL to distinguish which instance of customers each column belongs to.

The problem with most join mistakes are, they do not produce SQL errors. The query runs successfully, but the result may contain missing rows, duplicate rows, incorrect totals, or unexpected NULL values.

The best way to investigate is to start with what looks wrong in the result, then check which part of the join could have caused it.

Use this table as a quick reference:

Before choosing INNER JOIN, LEFT JOIN, or another join type, make sure you understand what the query needs to return.

Answer these five questions first.

1. What should one row in the result represent?
Is it one customer, one order, or one customer-order combination? This determines the starting table and the level of detail in the result.

2. How are the tables related?
Identify the condition that connects their rows. It might be a shared key, a range of values, or a time window. This becomes the ON condition.

3. How many matches can each row have?
Can one customer have multiple orders? Can one order have multiple payments? If a join can produce several matches, be careful when summing values from the other table. You may need to aggregate before joining to avoid counting the same value multiple times.

4. Which rows must remain when there is no match?
Use an INNER JOIN when only matching rows matter, a LEFT JOIN when every row from the starting table must remain, or a FULL JOIN when unmatched rows from both tables must remain.

5. Should a condition control which rows match or which rows appear in the final result?
Put conditions that determine valid matches in ON. Put conditions that filter the resulting rows in WHERE. With outer joins, this distinction is especially important because a filter in WHERE can remove rows the join was meant to preserve.

Applying the five questions

Consider the requirement from section 04:

“Show every customer and any paid orders they have, including customers who have no paid orders.”

Here is how the five questions guide the query:

Putting these decisions together gives us:

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_id, o.order_id;

The query now expresses the requirement:

• Every customer remains because customers is on the left of a LEFT JOIN.
• Only paid orders can match because the status condition is in ON.
• Customers with multiple paid orders appear once per order.
• Customers without paid orders still appear, with NULL order details.

The final requirement is to find customers who have never placed a paid order.

We already know that a LEFT JOIN can preserve customers without matching orders. We also learned in section 04 that filtering the right table in WHERE can remove those customers.

However, there is an important exception. We can use WHERE to find customers who have no matching orders at all.

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

Why does WHERE work differently here?

In section 04, we used:

WHERE o.status = 'paid'

For NULL-extended rows, NULL = 'paid' evaluates to UNKNOWN. Since WHERE keeps only rows where the condition is TRUE, customers without paid orders disappeared.

This time, we use:

WHERE o.order_id IS NULL

For those same NULL-extended rows, IS NULL evaluates to TRUE. Instead of removing unmatched customers, it selects them.

The difference is what the condition tests:

• o.status = 'paid' keeps rows containing paid orders.
• o.order_id IS NULL keeps rows where no paid order matched.

This pattern is called an anti-join. It finds rows in one table that have no qualifying match in another table.

When using this pattern, test a right-table column that cannot be NULL for a real matched row, usually its primary key.

Here, o.order_id is a reliable choice. Although o.status IS NULL would also work with this particular ON condition, checking a non-nullable key makes the intention clearer and is safer as a general pattern.

We will also learn how to solve this problem using NOT EXISTS, which expresses the requirement directly: find customers for whom no matching paid order exists.

A join produces one row for every pair of rows that satisfies its matching condition. Outer joins also preserve unmatched rows, filling the missing columns with NULL.

The most important skill is understanding what each result row represents. Before writing a join, consider how the tables relate, how many matches are possible, and which unmatched rows must remain.

Remember that ON determines which rows match, while WHERE filters the result. Be especially careful with one-to-many joins, where repeated rows can lead to incorrect totals.

Next, P08 turns the anti-join from section 14 into its own pattern: EXISTS and NOT EXISTS, for questions that only need to know whether a matching row exists.