SQL JOINs: INNER JOIN and LEFT JOIN Explained

A JOIN combines rows from two tables into one result by matching a column in one with a column in the other. It is the single most important SQL skill after SELECT, and it is the most common interview topic. Take it slowly, and use the pictures in your head: two piles of cards, matched by an id.

The problem: names live in another table

Look at orders. It shows customer_id 1, 1, 2. That is not very friendly. You want the customer's name. The name is in customers. A join connects the two.

INNER JOIN

SELECT o.id, c.name, o.order_date, o.status
FROM orders AS o
INNER JOIN customers AS c ON c.id = o.customer_id
ORDER BY o.id;

id | name         | order_date | status
1  | Asha Rao     | 2024-06-02 | delivered
2  | Asha Rao     | 2024-06-20 | delivered
3  | Ben Carter   | 2024-06-21 | delivered
4  | Chidi Okafor | 2024-07-01 | cancelled
5  | Divya Nair   | 2024-07-04 | delivered
6  | Emma Wilson  | 2024-07-09 | shipped
7  | Ben Carter   | 2024-07-15 | delivered
8  | Farid Hassan | 2024-07-18 | delivered
9  | Asha Rao     | 2024-08-02 | shipped
10 | Grace Mwangi | 2024-08-05 | pending

Read it in parts.

• FROM orders AS o starts with the orders table and gives it the short name o. A short name is called a table alias.
• INNER JOIN customers AS c brings in the customers table, short name c.
• ON c.id = o.customer_id is the matching rule: a customer row and an order row are joined when the customer's id equals the order's customer_id.
• In the SELECT, o.id and c.name say which table each column comes from. When two tables have a column with the same name, such as id, you must say which one you mean.

INNER JOIN keeps only rows that have a match on both sides. Ten orders matched ten customers, so ten rows come back. Hari Singh has no orders, so Hari does not appear at all. You can write just JOIN instead of INNER JOIN. It means the same.

LEFT JOIN: keep everything from the left table

What if you want all customers, including those with no orders? LEFT JOIN keeps every row from the first (left) table. When there is no match on the right side, the right columns are filled with NULL.

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

name         | orders
Asha Rao     | 3
Ben Carter   | 2
Chidi Okafor | 1
Divya Nair   | 1
Emma Wilson  | 1
Farid Hassan | 1
Grace Mwangi | 1
Hari Singh   | 0

Hari appears with 0 orders. With an inner join Hari would be missing. Notice that we counted o.id, not *. For Hari the order columns are NULL, and COUNT(o.id) skips NULL, so the count is 0. COUNT(*) would say 1, which is wrong.

Find rows with no match

A very common request: which customers have never ordered? Do a LEFT JOIN, then keep only rows where the right side is NULL.

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

name
Hari Singh

This works because only unmatched customers have a NULL o.id. The same pattern finds products that nobody has ordered, employees with no manager assigned, and so on. In our data every product has been ordered at least once, so that query returns nothing.

Joining three tables

You can keep joining. To see the lines of an order with product names and the money for each line, join order_items to products.

SELECT oi.order_id, p.name, oi.quantity, p.price * oi.quantity AS line_total
FROM order_items AS oi
JOIN products AS p ON p.id = oi.product_id
WHERE oi.order_id IN (1, 3)
ORDER BY oi.id;

order_id | name         | quantity | line_total
1        | Notebook     | 3        | 360
1        | Pen pack     | 2        | 160
3        | Laptop stand | 1        | 1800
3        | Mouse        | 1        | 700

Now go further and combine joins with grouping to answer a real business question: how much has each customer spent, ignoring cancelled orders?

SELECT c.name, SUM(p.price * oi.quantity) AS spent
FROM customers AS c
JOIN orders AS o       ON o.customer_id = c.id
JOIN order_items AS oi ON oi.order_id = o.id
JOIN products AS p     ON p.id = oi.product_id
WHERE o.status <> 'cancelled'
GROUP BY c.id
ORDER BY spent DESC;

name         | spent
Asha Rao     | 4020
Ben Carter   | 3340
Divya Nair   | 1200
Emma Wilson  | 700
Farid Hassan | 650
Grace Mwangi | 320

Chidi's only order was cancelled, and Hari has no orders, so neither appears. Read the query as a chain: customer to orders, orders to items, items to products. Each ON line is one link in the chain. When a join returns strange numbers, check the chain one link at a time by removing the later joins.

Why join results sometimes grow

A join returns one row for every matching pair. If one order has three items, joining orders to items gives three rows for that order. This is correct, but if you then sum an order-level number such as a delivery fee, you would count it three times. When totals look too big, suspect a join that multiplied your rows. Check by counting rows before and after each join.

Other joins you will meet

• RIGHT JOIN keeps all rows from the right table. It is a LEFT JOIN written backwards, so most people just swap the table order and use LEFT JOIN. SQLite supports it in recent versions.
• FULL OUTER JOIN keeps all rows from both sides. It exists in PostgreSQL, SQL Server and Oracle. MySQL does not have it.
• CROSS JOIN pairs every row with every row. It is rarely wanted by accident and often a bug.
• Self join joins a table to itself, for example an employee table to find each person's manager. Give the two copies different aliases.

Common mistakes

• Forgetting the ON rule. In some engines this gives a cross join and a huge result.
• Writing a condition on the right table in WHERE after a LEFT JOIN, for example WHERE o.status = 'delivered'. It removes the NULL rows, so the result behaves like an inner join. Put such conditions in the ON clause instead.
• Not qualifying a column that exists in both tables. You get an error like “ambiguous column name”.
• Counting with COUNT(*) after a left join and counting the unmatched rows as one.

Your task

1. Show each product name with the total quantity ever ordered. Use order_items and products. Notebook and Sticky notes are both 10.
2. List every order with the customer's name and city, only for customers in Nairobi. Two rows come back.
3. Find the total value of each order (price times quantity summed per order). Order 3 should be 2500.
4. List customers who have at least one shipped order. Use joins and not a subquery.

How to check it worked

• The Nairobi query returns orders 8 and 10.
• Order values are 520, 1400, 2500, 1300, 1200, 700, 840, 650, 2100 and 320 for orders 1 to 10.
• The shipped query returns Emma Wilson and Asha Rao.