SQL joins explained with real examples: INNER, LEFT and the traps to avoid

A well-designed database splits data into separate tables: customers in one, orders in another, products in a third. That’s what keeps it correct (I explained why in database design basics). But the questions you actually want answered, like “what did each customer spend?”, need data from several tables at once. That’s what a join does.

Every example below runs on the same small database, and I ran each query in SQLite to check the results. The same SQL works in PostgreSQL and MySQL.

The example data#

Three customers, four orders. Notice two awkward cases, because real data always has them: Chen hasn’t ordered anything, and order 104 has no customer (say, a guest checkout).

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  total INTEGER NOT NULL,
  status TEXT NOT NULL
);

INSERT INTO customers VALUES (1, 'Ayesha', 'Dhaka'), (2, 'Ben', 'London'), (3, 'Chen', 'Toronto');
INSERT INTO orders VALUES
  (101, 1, 40, 'paid'),
  (102, 1, 25, 'refunded'),
  (103, 2, 60, 'paid'),
  (104, NULL, 15, 'paid');

INNER JOIN: only rows that match on both sides#

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

name    order_id  total
Ayesha  101       40
Ayesha  102       25
Ben     103       60

JOIN (short for INNER JOIN) pairs each order with its customer, using the rule after ON. Anything without a partner disappears: Chen has no orders, and order 104 has no customer, so neither is in the result. Ayesha appears twice because she has two orders. A join produces one row per match, not one row per customer.

That’s usually what you want for “show orders with customer names”. It’s not what you want when the missing rows matter.

LEFT JOIN: keep everything from the first table#

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

name    order_id  total
Ayesha  101       40
Ayesha  102       25
Ben     103       60
Chen    NULL      NULL

(Without ORDER BY, the database can return rows in any order it likes, and SQLite did swap Ayesha’s two orders when I left it out. Add it whenever the order matters.)

A LEFT JOIN keeps every row from the left table (the one after FROM), matched or not. Where there’s no match, the columns from the right table are NULL. Chen is back.

A neat trick follows from that: to find rows with no match, keep only the ones where the right side is empty.

-- Customers who have never ordered
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

That returns Chen. The same pattern finds products never sold, members who never logged in, or posts with no comments.

Counting and totals per customer#

Joins and GROUP BY go together all the time:

SELECT c.name,
       COUNT(o.id) AS orders,
       COALESCE(SUM(o.total), 0) AS spent
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id;

name    orders  spent
Ayesha  2       65
Ben     1       60
Chen    0       0

Two details make this correct:

• COUNT(o.id), not COUNT(*). COUNT(*) counts rows, and Chen has one row (full of NULLs), so he’d show 1 order. COUNT(o.id) skips NULLs and gives 0.
• COALESCE(..., 0) turns Chen’s NULL total into 0, which is friendlier in a report.

Trap 1: a WHERE clause that quietly undoes your LEFT JOIN#

Say you want every customer, with their paid orders only. This looks right:

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

name    order_id
Ayesha  101
Ben     103

Chen is gone again. WHERE runs after the join, and for Chen o.status is NULL, which isn’t 'paid', so his row is filtered out. You’ve turned your LEFT JOIN back into an INNER JOIN without meaning to.

The fix is to put the condition in the ON clause, so it decides which orders match instead of which rows survive:

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

name    order_id
Ayesha  101
Ben     103
Chen    NULL

Rule of thumb: with a LEFT JOIN, conditions about the right-hand table go in ON. Conditions about the left table can stay in WHERE.

Many-to-many: joining through a middle table#

An order can contain many products, and a product can be in many orders. That relationship lives in a table in the middle:

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE order_items (
  order_id INTEGER REFERENCES orders(id),
  product_id INTEGER REFERENCES products(id),
  quantity INTEGER NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

INSERT INTO products VALUES (1, 'Notebook'), (2, 'Pen'), (3, 'Desk lamp');
INSERT INTO order_items VALUES (101, 1, 2), (101, 2, 5), (102, 2, 1), (103, 3, 1), (103, 1, 1);

To list what’s in each order, join twice, one step at a time:

SELECT o.id AS order_id, p.name AS product, oi.quantity
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id;

order_id  product    quantity
101       Notebook   2
101       Pen        5
102       Pen        1
103       Desk lamp  1
103       Notebook   1

Trap 2: totals that double#

Now the mistake I see most in real reports. You want each customer’s spending and the number of items they bought, so you join everything and add up:

SELECT c.name, SUM(o.total) AS spent
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
GROUP BY c.id;

name    spent
Ayesha  105
Ben     120

Ben spent 60, not 120. Joining to order_items repeats each order once per item in it: order 103 has two items, so its 60 is counted twice. Ayesha’s order 101 has two items too, so her 40 is counted twice: 40 + 40 + 25 = 105.

The fix is to total the detail table first, so it has one row per order, and join that:

SELECT c.name, SUM(o.total) AS spent, SUM(i.items) AS items
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN (
  SELECT order_id, SUM(quantity) AS items
  FROM order_items
  GROUP BY order_id
) i ON i.order_id = o.id
GROUP BY c.id;

name    spent  items
Ayesha  65     8
Ben     60     2

Whenever a total looks too high, check whether a join is repeating rows before the SUM.

The other joins, briefly#

• RIGHT JOIN is a LEFT JOIN written the other way round: it keeps every row from the second table. Most people just swap the tables and use LEFT JOIN, which reads more naturally.
• FULL OUTER JOIN keeps unmatched rows from both sides. With our data it returns Chen (no orders) and order 104 (no customer). PostgreSQL supports it, SQLite since version 3.39; MySQL doesn’t, so there you combine a LEFT and a RIGHT join with UNION.
• CROSS JOIN pairs every row with every row: 3 customers × 4 orders = 12 rows. Occasionally useful (every product in every size), usually a mistake.

Joins are fast when the columns you join on are indexed. Primary keys are indexed automatically, but foreign keys like orders.customer_id often aren’t, depending on the database. See database indexes explained for how to check.

If you need a database designed or a slow report fixed, I do API integration and backend work.