On this page
A shop keeps its customers in one table and its orders in another. That is deliberate: a customer's name and city are stored once, and each order only stores the customer's id. But when someone asks "what is the total spent by customers in Pune?", you need both tables at once. A join is how you combine them.
This guide runs every join type on the same two small tables. The queries ran in DuckDB, and each output is exactly what it printed. The syntax works the same in MySQL, PostgreSQL, Snowflake and BigQuery. If you have not written a SELECT yet, start with SQL for beginners.
The 2 tables
customers has four people. orders has 4 orders, and each order stores the customer_id of the person who placed it. Two things are on purpose: Meena and Kiran have never ordered, and order 104 belongs to customer 5, who is not in the customers table.
CREATE TABLE customers (customer_id INTEGER, name VARCHAR, city VARCHAR);
INSERT INTO customers VALUES
(1, 'Asha', 'Pune'),
(2, 'Ravi', 'Mumbai'),
(3, 'Meena', 'Chennai'),
(4, 'Kiran', 'Pune');
CREATE TABLE orders (order_id INTEGER, customer_id INTEGER, amount INTEGER);
INSERT INTO orders VALUES
(101, 1, 1200),
(102, 1, 300),
(103, 2, 800),
(104, 5, 450);SELECT * FROM customers;customer_id | name | city ------------+-------+-------- 1 | Asha | Pune 2 | Ravi | Mumbai 3 | Meena | Chennai 4 | Kiran | Pune (4 rows)
SELECT * FROM orders;order_id | customer_id | amount ---------+-------------+------- 101 | 1 | 1200 102 | 1 | 300 103 | 2 | 800 104 | 5 | 450 (4 rows)
The shared column is customer_id. In customers it identifies a person. In orders it points back to a person. A column that points to another table's id is called a foreign key, and joining is mostly about following it.
Only the matches.
Asha twice, Ravi once.
Every customer.
Meena and Kiran get NULL.
Every order.
Order 104 gets a NULL name.
Everything from both sides.
3 matches, 2 customers, 1 order.
INNER JOIN: keep only the matches
INNER JOIN returns a row only when the key is found on both sides. The ON part says which columns to match.
SELECT c.name, c.city, o.order_id, o.amount
FROM customers c
INNER JOIN orders o ON o.customer_id = c.customer_id
ORDER BY o.order_id;name | city | order_id | amount -----+--------+----------+------- Asha | Pune | 101 | 1200 Asha | Pune | 102 | 300 Ravi | Mumbai | 103 | 800 (3 rows)
3 rows. Asha appears twice, because she has 2 orders, and each order becomes its own row. Meena and Kiran are missing, since they have no orders, and so is order 104, since customer 5 does not exist. c and o are short nicknames (aliases) for the 2 tables, so you can write c.name and know which table it comes from.
LEFT JOIN: keep everyone from the left table
LEFT JOIN keeps every row of the table written before the word JOIN (the "left" one), whether or not it found a match. Where nothing matched, the columns from the other table are filled with NULL, which means "no value".
SELECT c.name, c.city, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.order_id;name | city | order_id | amount ------+---------+----------+------- Asha | Pune | 101 | 1200 Asha | Pune | 102 | 300 Ravi | Mumbai | 103 | 800 Meena | Chennai | NULL | NULL Kiran | Pune | NULL | NULL (5 rows)
5 rows now. Meena and Kiran are back, with NULL where their order should be. This is the join you use to ask "show me all customers, with their orders if they have any".
A very common follow-up is "who has never ordered?". Keep only the rows where the order side is NULL:
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL
ORDER BY c.name;name ----- Kiran Meena (2 rows)
Meena and Kiran. Test a column that can never be NULL in a real order, such as order_id. You test for NULL with IS NULL, never with = NULL, and the NULL guide explains why.
RIGHT JOIN: the mirror image
RIGHT JOIN keeps every row of the table on the right, here orders.
SELECT c.name, o.order_id, o.amount
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.customer_id
ORDER BY o.order_id;name | order_id | amount -----+----------+------- Asha | 101 | 1200 Asha | 102 | 300 Ravi | 103 | 800 NULL | 104 | 450 (4 rows)
4 rows, one per order, and order 104 shows NULL for the name because customer 5 is not in customers. A RIGHT JOIN is the same as a LEFT JOIN with the tables written the other way round, so most people always write LEFT and swap the tables. It is easier to read when every join in a query leans the same way.
FULL JOIN: keep everything from both sides
FULL JOIN (also written FULL OUTER JOIN) keeps every row from both tables, matched where possible and NULL-filled where not.
SELECT c.name, o.order_id, o.amount
FROM customers c
FULL JOIN orders o ON o.customer_id = c.customer_id
ORDER BY c.name, o.order_id;name | order_id | amount ------+----------+------- Asha | 101 | 1200 Asha | 102 | 300 Kiran | NULL | NULL Meena | NULL | NULL Ravi | 103 | 800 NULL | 104 | 450 (6 rows)
6 rows: three matches, Meena and Kiran with no order, and order 104 with no customer. A full join is what you use to compare two lists and see what is missing from each side. MySQL has no FULL JOIN, so there it is built from a LEFT JOIN and a RIGHT JOIN joined with UNION.
CROSS JOIN: every row with every row
CROSS JOIN has no ON condition. It pairs each row of one table with every row of the other.
SELECT COUNT(*) AS pairs
FROM customers
CROSS JOIN orders;pairs ----- 16 (1 row)
4 customers times 4 orders is 16 rows. That is almost never what you want, and it is worth knowing because it happens by accident: if you forget the ON condition in a join, many databases quietly give you this. A query that suddenly returns millions of rows often has a missing join condition.
Common join mistakes
Joining on the wrong columns. The database will happily match any 2 columns of the same type. Try matching the customer id against the order id:
SELECT c.name, o.order_id
FROM customers c
INNER JOIN orders o ON c.customer_id = o.order_id;name | order_id -----+--------- (0 rows)
No rows, with no error at all, because customer ids go from 1 to 4 and order ids start at 101. The query is valid and the answer is wrong. Always check that the 2 columns mean the same thing.
Ambiguous column names. customer_id exists in both tables, so naming it without a table prefix confuses the database:
SELECT customer_id
FROM customers c
INNER JOIN orders o ON o.customer_id = c.customer_id;Binder Error: Ambiguous reference to column name "customer_id" (use: "c.customer_id" or "o.customer_id")
Fix it by writing c.customer_id or o.customer_id. A good habit is to prefix every column in a join, even when it is not required.
Rows that multiply. Asha has 2 orders, so joining customers to orders gives her 2 rows. If you then add up a customer-level column, such as a credit limit, her number is counted twice. Before joining, ask "how many rows can match for 1 row on the left?"
Which join should I use?
| You want | Use |
|---|---|
| Only rows that exist in both tables | INNER JOIN |
| Every row from the main table, with extra details if available | LEFT JOIN |
| Rows from the main table that have no match | LEFT JOIN then WHERE ... IS NULL |
| To compare two lists and see what each side is missing | FULL JOIN |
| Every combination of two small lists, on purpose | CROSS JOIN |
Practise with the free problems Employee department names, Employees with optional department and Customers who never ordered. When you are comfortable, SQL interview questions for data engineers shows the join questions interviewers ask, including the NULL trap with NOT IN.