Joins & Relationship Reasoning

Real data lives in many tables. A customer is in one table. Their orders are in another table.

A join puts rows from two tables side by side. It uses a column that both tables share. One result then answers a question about both tables.

In chapter 1 you learned NULL and grain. NULL means a value is missing. Grain is what one row stands for.

The shop again

This chapter uses the same shop as chapter 1. Here is all the data. Every result in this chapter comes from these rows.

Two facts matter most in this chapter.

• Omar is a customer, but he has no orders.
• Order 105 exists, but it has no items.

What you will learn

1. Say how two tables relate: one-to-many and more.
2. Choose between INNER JOIN, LEFT JOIN and FULL JOIN.
3. Predict how many rows a join returns.
4. Find two common bugs: sums that double, and rows that vanish.
5. Find rows with no match: EXISTS, NOT EXISTS and NOT IN.
6. Check a join with counts.

Joins are a very common interview topic. The examples use PostgreSQL. When another database differs, we tell you.

To join two tables, you need a column that links them. This column is a key. In chapter 1, a key was a column that finds one row.

Primary key and foreign key

A primary key is unique in its own table. In customers, customer_id is the primary key. No two customers share it.

A foreign key is a column whose values come from the primary key of another table. In orders, customer_id is a foreign key. It says which customer made the order.

Count the rows and the different customer_id values in orders:

SELECT COUNT(*)                   AS total_rows,
       COUNT(DISTINCT customer_id) AS different_ids
FROM orders;

Now count the same two things in customers:

SELECT COUNT(*)                   AS total_rows,
       COUNT(DISTINCT customer_id) AS different_ids
FROM customers;

Three shapes of relationship

Asha has two orders. So one customer matches many orders. This is a one-to-many relationship.

The foreign key sits on the “many” side, in orders.

There are three shapes. Our shop has two of them.

Orders and products are many-to-many. The order_items table sits in the middle and links them.

Some rows match nothing. Omar has no orders. So “one-to-many” really means zero, one or many.

An INNER JOIN keeps only the pairs of rows that match. A match is a pair of rows that pass the test in ON.

This query puts the customer name next to each order:

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

Here is what happened:

• AS o gives orders a short name. A short name for a table is an alias. o.amount means the amount column of table o.
• ON says when two rows match: the two customer_id values are equal.
• Order 103 has customer_id 2. That matches Ben, so Ben appears next to order 103.
• Every order found one customer, so you get 6 rows.

You can write JOIN alone. It means INNER JOIN.

Rows with no match disappear

Now start from customers. Omar has no order, so he has no match:

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

You get no error and no warning. A lost row is easy to miss.

If you need every customer, use a LEFT JOIN. The next section shows it.

A LEFT JOIN keeps every row from the left table. The left table is the one after FROM. The right table is the one after JOIN.

If a left row has no match, SQL still keeps it. It fills the right columns with NULL. We call this a NULL-padded row.

Which orders did each customer place? Keep every customer:

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

There are 7 rows: 6 orders and 1 NULL-padded row. NULL on the right side means “no match found”.

Counting after a LEFT JOIN

Count the rows and the orders in the same result:

SELECT COUNT(*)          AS result_rows,
       COUNT(o.order_id) AS orders
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id;

A RIGHT JOIN is a LEFT JOIN with the two tables swapped. It keeps every row of the right table. Left columns become NULL when there is no match.

This query keeps every customer, but now customers is the right table:

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

Most people write LEFT JOIN, because the table you keep comes first. You can always swap the two tables and use LEFT JOIN.

FULL JOIN keeps both sides

A FULL JOIN keeps unmatched rows from both tables. People use it to compare two sources of data.

Our shop has no order without a customer. For this example only, we add order 107 with customer_id 9. No customer 9 exists.

Look at the added row in the inputs:

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

In this table, customers is the left table and orders is the right table. See what each join does with three kinds of rows:

Chapter 1 explained grain: what one row stands for. A join can change the grain. Cardinality tells you how: it is how many rows on one side match one row on the other side.

Join orders to order_items. An order with two items becomes two rows:

SELECT o.order_id, oi.product, oi.quantity
FROM orders AS o
JOIN order_items AS oi
  ON oi.order_id = o.order_id
ORDER BY o.order_id, oi.product;

Order 101 now has two rows: Notebook and Pen. One row is no longer one order. One row is now one item in an order.

Order 105 is missing. It has no items, so INNER JOIN removed it.

Predict the row count

Follow three steps before you run a join:

1. Start with the rows in the left table.
2. Each left row gives one result row for every match it has.
3. A left row with no match gives 0 rows in an INNER JOIN. It gives 1 NULL-padded row in a LEFT JOIN.

Test it on our shop. The Items column is the cardinality: how many item rows match each order. This table counts the rows each order gives:

Add each column. INNER JOIN gives 7 rows. LEFT JOIN gives 8 rows.

Check the 7 with a count:

SELECT COUNT(*)                AS joined_rows,
       COUNT(DISTINCT o.order_id) AS different_orders
FROM orders AS o
JOIN order_items AS oi
  ON oi.order_id = o.order_id;

Fan-out means the join repeats a row once for each match. If you add up a repeated column, you count the same value many times.

Look at the amount column in this join:

SELECT o.order_id, o.amount, oi.product
FROM orders AS o
JOIN order_items AS oi
  ON oi.order_id = o.order_id
ORDER BY o.order_id, oi.product;

Now add up the amount column over the same join:

SELECT SUM(o.amount) AS total_amount
FROM orders AS o
JOIN order_items AS oi
  ON oi.order_id = o.order_id;

Add each order once to get the true total:

SELECT SUM(amount) AS total_amount
FROM orders;

The total from the join is 120.00 too high. That is the extra 40.00 and the extra 80.00.

Fix: one row per order

Sometimes you need item data and order data together. First make the items one row per order. GROUP BY does this, and chapter 3 teaches it.

A query inside brackets is a subquery. SQL runs it first and treats its result like a small table.

Here the subquery is named i. This is what i holds:

SELECT SUM(o.amount)   AS total_amount,
       SUM(i.item_rows) AS item_rows
FROM orders AS o
JOIN (
  SELECT order_id, COUNT(*) AS item_rows
  FROM order_items
  GROUP BY order_id
) AS i
  ON i.order_id = o.order_id;

The total is correct again. An INNER JOIN also removes orders with no items. That did not change the total here, because order 105 has no amount.

A column that belongs to each item row, like quantity, is safe to add. SUM(oi.quantity) gives 10, and every item row counts once.

ON decides which rows match. WHERE removes rows from the final result.

In an INNER JOIN they act the same. In a LEFT JOIN they do not.

You want every customer, with only their paid orders. Put the condition in ON:

SELECT c.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;

Ben and Omar stay. Ben’s order 103 is refunded, so it does not match.

Omar has no orders. Their order columns are NULL. Carla’s pending order 105 is not shown, and only her paid order 104 matches.

Now move the same condition to WHERE:

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

WHERE runs after the join. First the LEFT JOIN makes Ben’s row (order 103, refunded) and Omar’s row (status NULL).

Then WHERE keeps only rows with status = ‘paid’. Ben’s refunded is not paid.

For Omar, NULL = ‘paid’ is UNKNOWN. WHERE removes both rows.

The LEFT JOIN now acts like an INNER JOIN. You lost the rows you wanted to keep.

A WHERE condition on a left-table column is safe. One WHERE on the right table is on purpose: WHERE o.order_id IS NULL keeps only the rows with no match. The next section uses it.

Sometimes you need only a yes or no. Does this customer have a paid order? You do not need any order columns.

EXISTS checks for at least one matching row. This is called a semi-join. It returns each left row at most once:

SELECT c.name
FROM customers AS c
WHERE EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.customer_id = c.customer_id
    AND o.status = 'paid'
)
ORDER BY c.customer_id;

The query inside EXISTS( ... ) is a subquery too. For each customer, SQL runs it with that customer’s customer_id.

If it finds at least one paid order, EXISTS is TRUE and the customer stays. SELECT 1 is only a placeholder, because EXISTS does not use the value.

Anti-join: rows with no match

An anti-join keeps the left rows that have no match.

Which customers never ordered? Use a LEFT JOIN, then keep the NULL rows. Test order_id: in a real order it is never NULL, so NULL means “no order found”:

SELECT c.name
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

NOT EXISTS gives the same answer:

SELECT c.name
FROM customers AS c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.customer_id = c.customer_id
);

NOT IN and NULL

NOT IN looks similar, but it has a trap. Chapter 1 showed it with a list. Here the list comes from a subquery.

For this example only, we add order 108. Its customer_id is NULL. Run NOT IN on the new list:

SELECT name
FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);

The list now contains NULL. For Omar: 5 <> 1 is TRUE, but 5 <> NULL is UNKNOWN. TRUE AND UNKNOWN is UNKNOWN, so WHERE removes the row.

NOT EXISTS on the same data still returns Omar.

A self join joins a table to itself. You give the table two aliases, like a and b. Think of two copies of the same table.

Use it to find rows that share a value, like two orders from one customer.

Which orders did the same customer place? List the pairs:

SELECT a.customer_id,
       a.order_id AS first_order,
       b.order_id AS second_order
FROM orders AS a
JOIN orders AS b
  ON b.customer_id = a.customer_id
 AND a.order_id < b.order_id
ORDER BY a.customer_id, a.order_id;

a.order_id < b.order_id keeps each pair once. The pair (101, 102) passes. The pair (102, 101) fails, and so does (101, 101).

CROSS JOIN: every pair

A CROSS JOIN pairs every left row with every right row. It has no ON. The result has left rows × right rows.

VALUES builds a small table from a list. AS s(status) names that table s, with one column called status.

This grid lists three statuses for customers 1 and 2:

SELECT c.name, s.status
FROM customers AS c
CROSS JOIN (VALUES ('paid'), ('pending'), ('refunded')) AS s(status)
WHERE c.customer_id IN (1, 2)
ORDER BY c.customer_id, s.status;

The accidental cross join

If you list two tables with a comma and forget the join condition, you get a cross join by mistake:

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

When a join gives a surprising result, do not guess. Count the rows. Three numbers tell you most of the story.

1. The rows before the join.
2. The rows after the join.
3. The different keys after the join.

You already counted 7 rows and 5 orders for orders joined to order_items. Now add the rows before the join:

SELECT (SELECT COUNT(*) FROM orders) AS orders_before,
       COUNT(*)                      AS rows_after,
       COUNT(DISTINCT o.order_id)    AS different_orders
FROM orders AS o
JOIN order_items AS oi
  ON oi.order_id = o.order_id;

Read the numbers. Rows went from 6 to 7 although an order was lost, so the join repeated rows. Different orders went from 6 to 5, so one order was removed.

Which one? Order 105 has no items. Switch to LEFT JOIN to test the idea:

SELECT COUNT(*)                AS rows_after,
       COUNT(DISTINCT o.order_id) AS different_orders
FROM orders AS o
LEFT JOIN order_items AS oi
  ON oi.order_id = o.order_id;

Use this table to read the numbers:

Each exercise opens in the SQL workspace with its own tables. Write the query, run it, and check the result.

They follow the order of this chapter. Start at the top.