Real databases spread information across several tables: customers in one, orders in another, products in a third. To answer almost any useful question – “how much has each customer spent?”, “which products have never been ordered?” – you need to combine those tables. That is what joins do.
Joins are also where most SQL mistakes happen. A join that is slightly wrong rarely produces an error; it produces numbers that look plausible but are inflated, or rows that have quietly disappeared. This tutorial explains how joins work, walks through INNER, LEFT, RIGHT, FULL and self joins with a small example you can run, and then covers the mistakes to watch out for.
Sample tables
Run this in SQLite (version 3.39 or later supports every join type shown here) or adapt it to your database:
CREATE TABLE customers (
customer_id INTEGER PRIMARY KEY,
name TEXT,
city TEXT
);
INSERT INTO customers VALUES
(1, 'Ava', 'London'),
(2, 'Ben', 'Bristol'),
(3, 'Chloe', 'Leeds'),
(4, 'Dan', 'York');
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER,
order_date TEXT,
amount REAL
);
INSERT INTO orders VALUES
(101, 1, '2024-03-01', 120.00),
(102, 1, '2024-03-08', 35.50),
(103, 2, '2024-03-09', 80.00),
(104, 3, '2024-03-10', 210.00),
(105, 9, '2024-03-11', 15.00);Notice two deliberate quirks. Dan (customer 4) has never ordered, and order 105 belongs to customer 9, who does not exist in the customers table. Real data has exactly these kinds of gaps, and how each join treats them is the whole point.
How a join works
A join matches rows from two tables using a condition, usually that a key in one table equals a key in the other. Here, orders.customer_id refers to customers.customer_id. The type of join decides what happens to rows that have no match.
Step 1: INNER JOIN – only matching rows
SELECT c.name, o.order_id, o.amount
FROM customers AS c
INNER JOIN orders AS o
ON o.customer_id = c.customer_id
ORDER BY o.order_id;Result: four rows (orders 101–104). Dan is missing because he has no orders, and order 105 is missing because it has no customer. An inner join keeps only rows that match on both sides. JOIN on its own means INNER JOIN.
The short names c and o are table aliases. They make queries shorter and are essential when both tables have a column with the same name, such as customer_id.
Step 2: LEFT JOIN – keep everything from the first table
SELECT c.name, o.order_id, o.amount
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.customer_id
ORDER BY c.customer_id;Now Dan appears, with NULL for order_id and amount. A left join keeps every row from the left table (the one after FROM) and fills in columns from the right table where there is a match.
LEFT JOIN is the join you will use most in analysis, because you usually start from the thing you are reporting on – customers, products, dates – and want every one of them in the output, even if they had no activity.
Step 3: Finding rows with no match (anti-join)
Which customers have never ordered? Left join, then keep rows where the right side is NULL:
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;This returns Dan. The same question can be written with NOT EXISTS, which many people find clearer:
SELECT c.name
FROM customers AS c
WHERE NOT EXISTS (
SELECT 1 FROM orders AS o WHERE o.customer_id = c.customer_id
);Avoid NOT IN (SELECT customer_id FROM orders) for this: if the subquery returns even one NULL, NOT IN returns no rows at all.
Step 4: RIGHT JOIN and FULL OUTER JOIN
A right join is a left join with the tables swapped: it keeps every row from the second table. Most people simply rewrite it as a left join, which is easier to read. A full outer join keeps everything from both sides:
SELECT c.name, o.order_id, o.amount
FROM customers AS c
FULL OUTER JOIN orders AS o
ON o.customer_id = c.customer_id
ORDER BY o.order_id;This returns Dan (no order) and order 105 (no customer). Full joins are particularly useful for reconciliation – comparing two lists to see what is in one but not the other. MySQL does not support FULL OUTER JOIN; there you combine a left and right join with UNION.
Step 5: Joins with aggregation
Total spend per customer, including customers who spent nothing:
SELECT c.name,
COUNT(o.order_id) AS orders,
COALESCE(SUM(o.amount), 0) AS total_spent
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name
ORDER BY total_spent DESC;Two details matter here. COUNT(o.order_id) counts only non-null order IDs, so Dan correctly shows 0; COUNT(*) would count his single null-filled row and wrongly show 1. And COALESCE turns his NULL total into 0. Grouping is covered fully in SQL GROUP BY and HAVING.
Step 6: Filtering – ON versus WHERE
With inner joins, putting a condition in ON or WHERE gives the same result. With left joins it does not. Compare:
-- A: condition in WHERE. Customers without a qualifying order disappear.
SELECT c.name, o.amount
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.amount > 100;
-- B: condition in ON. Every customer kept, only big orders attached.
SELECT c.name, o.amount
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.customer_id
AND o.amount > 100;Query A returns only Ava and Chloe: the WHERE clause removes rows where amount is NULL, silently turning the left join into an inner join. Query B returns all four customers, with NULL amounts for Ben and Dan. Decide which question you are asking, and put the filter in the right place.
Step 7: Self joins
A self join joins a table to itself. The classic example is an employee table where each row stores the ID of that person’s manager:
CREATE TABLE staff (id INTEGER, name TEXT, manager_id INTEGER);
INSERT INTO staff VALUES (1, 'Rosa', NULL), (2, 'Sam', 1), (3, 'Tara', 1), (4, 'Umar', 2);
SELECT e.name AS employee, m.name AS manager
FROM staff AS e
LEFT JOIN staff AS m ON m.id = e.manager_id;Aliases are mandatory here, because the database needs to know which “copy” of the table each column comes from.
The fan-out trap: why totals get inflated
The most damaging join mistake is joining two “many” tables to the same parent and then summing. Suppose each customer also has several support tickets:
CREATE TABLE tickets (ticket_id INTEGER, customer_id INTEGER);
INSERT INTO tickets VALUES (1, 1), (2, 1), (3, 1);
-- WRONG: Ava's 2 orders x 3 tickets = 6 rows, so her spend is tripled
SELECT c.name, SUM(o.amount) AS total_spent, COUNT(t.ticket_id) AS tickets
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
LEFT JOIN tickets AS t ON t.customer_id = c.customer_id
GROUP BY c.name;Ava’s spend shows as 466.50 instead of 155.50, and her ticket count as 6 instead of 3. The fix is to aggregate each child table before joining:
WITH o AS (
SELECT customer_id, SUM(amount) AS total_spent
FROM orders GROUP BY customer_id
),
t AS (
SELECT customer_id, COUNT(*) AS tickets
FROM tickets GROUP BY customer_id
)
SELECT c.name,
COALESCE(o.total_spent, 0) AS total_spent,
COALESCE(t.tickets, 0) AS tickets
FROM customers AS c
LEFT JOIN o ON o.customer_id = c.customer_id
LEFT JOIN t ON t.customer_id = c.customer_id;The WITH clause defines common table expressions (CTEs); see CTEs vs Subqueries for more.
Common mistakes
- Not checking row counts. After every join, compare the result’s row count with what you expect. More rows than the parent table usually means a duplicate key or fan-out.
- Filtering a left join in WHERE and accidentally turning it into an inner join.
- Joining on non-unique keys, for example on name instead of ID.
- Using NOT IN with a column that can be NULL.
- Ambiguous column names – always prefix columns with table aliases in multi-table queries.
- Mismatched data types, such as joining a text ID to an integer ID, which can match nothing or force slow conversions.
FAQ
Which join should I use by default?
For reports, start with a LEFT JOIN from the entity you are reporting on, so nothing is silently dropped. Use INNER JOIN when you genuinely want only matched rows.
What is a CROSS JOIN?
It pairs every row of one table with every row of another, with no condition. It is useful for generating combinations, such as every product for every month, but dangerous on large tables.
How do joins work in pandas?
pandas’ merge uses the same ideas, with how="inner", "left", "right" or "outer". See Merging and Joining DataFrames in Pandas.
Can I join on more than one column?
Yes: ON a.store_id = b.store_id AND a.sale_date = b.sale_date.
Next steps
If you are new to SQL, start with SQL SELECT and WHERE for Absolute Beginners. After joins, practise summarising with GROUP BY and HAVING, then move on to window functions.
