ASAmol Shukla

8. Joins and grain

Step 1 of 4 · Lesson

Lesson 8 of 21

MySQL JOIN explained: INNER, LEFT, self join and double counting

Combine tables with INNER JOIN and LEFT JOIN, find rows with no match, join a table to itself, and avoid the double counting that row expansion causes.

85 min 4 min read 19 problems · 0 solved Learning Shop (small)

In short

What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN returns only rows that have a match in both tables. LEFT JOIN returns every row from the left table and fills the right table's columns with NULL where there is no match. Use LEFT JOIN … WHERE right.id IS NULL to find rows with no match, such as customers who never ordered.

By the end of this lesson you can

  • Join two, three and four tables on their keys with table aliases
  • Choose between INNER JOIN and LEFT JOIN, and find unmatched rows
  • Explain why joins multiply rows, and count correctly with COUNT(DISTINCT …)
  • Join a table to itself, and know what RIGHT JOIN and CROSS JOIN do

Why joins exist

The shop stores each fact once: a customer's city lives only in customers, a product's price only in products. Orders refer to them by id. A join puts the pieces back together for a report by matching keys. The ON clause says which columns must be equal:

SQL · shop
SELECT o.order_id, o.order_date, c.customer_name, c.city
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
ORDER BY o.order_id;
MySQL result8 rows
order_idorder_datecustomer_namecity
1012026-01-05AshaPune
1022026-01-08BilalMumbai
1032026-02-03AshaPune
1042026-02-10CharuPune
1052026-02-15DevDelhi
1062026-03-02BilalMumbai
1072026-03-04EshaMumbai
1082026-03-11DevDelhi

orders o gives the table a short alias. Once you alias, prefix every column (o.order_id, c.city) so a reader — and MySQL — knows where it comes from. JOIN on its own means INNER JOIN.

INNER JOIN keeps only matches

An inner join returns a row only when the ON condition finds a partner on both sides. Farah (customer 6) has no orders, so she does not appear above. Usually that is what you want — but not when the question is “list all customers”.

customersorders

INNER JOIN — only customers with orders

customersorders

LEFT JOIN — every customer; NULL where no order

LEFT JOIN keeps every row on the left

A LEFT JOIN keeps every row of the left (first) table. Where no partner exists, the right table's columns are NULL:

SQL · shop
SELECT c.customer_id, c.customer_name, o.order_id, o.status
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.order_id;
MySQL result9 rows
customer_idcustomer_nameorder_idstatus
1Asha101completed
1Asha103completed
2Bilal102completed
2Bilal106completed
3Charu104cancelled
4Dev105completed
4Dev108completed
5Esha107pending
6FarahNULLNULL

Farah now appears with NULL order columns. Customers with two orders appear twice — once per match.

Find rows with no match (anti-join)

Add WHERE right_table.key IS NULL to a LEFT JOIN and only the unmatched rows remain. This answers who never ordered?, which products never sold?, which orders have no items?:

SQL · shop
SELECT c.customer_id, c.customer_name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;
MySQL result1 row
customer_idcustomer_name
6Farah

Joins multiply rows — mind the grain

Joining orders (one row per order) to order_items (one row per item) gives one row per item. Order 101 had one row; after the join it has two. This is called row expansion, and it is the number-one source of wrong totals in SQL.

orders (1 row)101 · Asha · completedorder_items (2 rows)101 · Notebook · 2 × 100101 · Pen · 5 × 20after JOIN: 2 rowsorder 101 againorder 101 againCOUNT(*) now counts 2 for one order → use COUNT(DISTINCT order_id).
SQL · shop
SELECT COUNT(*)                   AS joined_rows,
       COUNT(DISTINCT o.order_id)  AS completed_orders,
       SUM(i.quantity * i.unit_price) AS revenue
FROM orders o
JOIN order_items i ON i.order_id = o.order_id
WHERE o.status = 'completed';
MySQL result1 row
joined_rowscompleted_ordersrevenue
1266150.00

12 joined rows, but only 6 orders. COUNT(*) after the join counts items. Use COUNT(DISTINCT o.order_id) for orders. Revenue (6150.00) is correct because quantity × unit_price lives at the item grain.

Three and four tables

Chain joins one at a time. Each JOIN … ON adds one table using a key it shares with a table already in the query:

SQL · shop
SELECT o.order_id, c.customer_name, p.product_name, i.quantity, i.unit_price,
       i.quantity * i.unit_price AS line_total
FROM orders o
JOIN customers   c ON c.customer_id = o.customer_id
JOIN order_items i ON i.order_id    = o.order_id
JOIN products    p ON p.product_id  = i.product_id
WHERE o.order_id IN (101, 102)
ORDER BY o.order_id, p.product_id;
MySQL result4 rows
order_idcustomer_nameproduct_namequantityunit_priceline_total
101AshaNotebook2100.00200.00
101AshaPen520.00100.00
102BilalDesk lamp1800.00800.00
102BilalMug2250.00500.00

Self join: a table joined to itself

Give the same table two aliases and it can be compared with itself — for example, pairs of customers who live in the same city:

SQL · shop
SELECT a.customer_name AS customer, b.customer_name AS neighbour, a.city
FROM customers a
JOIN customers b
  ON b.city = a.city
 AND b.customer_id > a.customer_id   -- each pair once, no self-pairs
ORDER BY a.city, a.customer_id;
MySQL result2 rows
customerneighbourcity
BilalEshaMumbai
AshaCharuPune

Self joins also model hierarchies (an employees table where manager_id points to another employee) and before/after comparisons.

RIGHT JOIN and CROSS JOIN (briefly)

  • RIGHT JOIN is a LEFT JOIN written the other way round: it keeps every row of the right table. Most teams always write LEFT JOIN and put the table they want to keep first, so queries read the same way.
  • CROSS JOIN pairs every row of one table with every row of the other (6 customers × 6 products = 36 rows). It is useful for building grids — every city × every category — and dangerous by accident: a JOIN with a missing or wrong ON condition behaves like one.
  • MySQL has no FULL OUTER JOIN; combine a LEFT JOIN and a RIGHT JOIN with UNION (lesson 10).
SQL · shop
SELECT COUNT(*) AS pairs
FROM customers
CROSS JOIN products;
MySQL result1 row
pairs
36

Common mistakes

Wrong

COUNT(*) after joining orders to order_items

Fix

COUNT(DISTINCT o.order_id)

Why: Counts item rows (12), not orders (6).

Wrong

LEFT JOIN orders o … WHERE o.status = 'completed'

Fix

Move the condition into ON: … ON o.customer_id = c.customer_id AND o.status = 'completed'

Why: Rows with no order have NULL status and are removed, so the LEFT JOIN acts like an INNER JOIN.

Wrong

Joining on names (ON p.product_name = …)

Fix

Join on primary / foreign keys.

Why: Names change and can repeat; ids are the keys.

Wrong

SELECT order_id FROM orders o JOIN order_items i ON …

Fix

Prefix with the alias: o.order_id.

Why: order_id exists in both tables, so MySQL raises error 1052 (ambiguous).

Check yourself

  1. Why does Farah appear with LEFT JOIN but not with INNER JOIN?
  2. Orders joined to items gives 12 rows for 6 completed orders. Explain why, and how to count orders.
  3. Where should a filter on the right-hand table go in a LEFT JOIN, and why?

Answer out loud or in your notebook. If one is hard, reread that section before practising.

Frequently asked questions

Is JOIN the same as INNER JOIN in MySQL?

Yes. JOIN, INNER JOIN and CROSS JOIN with an ON clause all produce an inner join in MySQL.

How do I find records that do not exist in another table?

LEFT JOIN the other table and keep rows where its key IS NULL, or use WHERE NOT EXISTS (SELECT 1 FROM other WHERE other.key = this.key). Both are shown in this course.

Does MySQL support FULL OUTER JOIN?

No. Emulate it with a LEFT JOIN UNION a RIGHT JOIN of the same tables.

Quick quiz — 4 questions

Quick Check: Joins and grain

1 / 4
Which join lists all 6 customers, including those with no orders?

Answers — Joins and grain

  1. Easy. Attach customer names to every order. Target: 8 rows, not 14. Order 101 belongs to Asha.

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

  2. Medium. Find customers who have never placed any order. Target: Farah only. Esha has a pending order and must not be included.

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

  3. Hard. Calculate each completed order total. Target: 101=300, 102=1300, 103=1400, 105=550, 106=1300, 108=1300; sum=6150.

    SELECT o.order_id, SUM(i.quantity*i.unit_price) AS total FROM orders o JOIN order_items i ON i.order_id=o.order_id WHERE o.status='completed' GROUP BY o.order_id ORDER BY o.order_id;

  4. Professional. Deliver completed order count and revenue in one audited result. Target: 6 orders and 6150 revenue. Explain why a raw COUNT(*) returns 12 item rows.

    SELECT COUNT(DISTINCT o.order_id) AS orders, SUM(i.quantity*i.unit_price) AS revenue FROM orders o JOIN order_items i ON i.order_id=o.order_id WHERE o.status='completed';

  5. Easy. Show each order item with the product name. Target: 14 rows; first (101, Notebook, 2), last (108, Mug, 2)

    SELECT i.order_id, p.product_name, i.quantity FROM order_items i JOIN products p ON p.product_id = i.product_id ORDER BY i.order_id, p.product_id;

  6. Easy. Show every order with customer name and city. Target: 8 rows; first (101, Asha, Pune), last (108, Dev, Delhi)

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

  7. Easy. List orders placed by customers from Mumbai. Target: 3 rows: (102, Bilal); (106, Bilal); (107, Esha)

    SELECT o.order_id, c.customer_name FROM orders o JOIN customers c ON c.customer_id = o.customer_id WHERE c.city = 'Mumbai' ORDER BY o.order_id;

  8. Easy. List every customer and their order ids, keeping customers with no orders. Target: 9 rows; first (Asha, 101), last (Farah, NULL)

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

  9. Medium. Show order 103 as a receipt: product, quantity, unit_price, line_total. Target: 2 rows: (Pen, 10, 20.00, 200.00); (Keyboard, 1, 1200.00, 1200.00)

    SELECT p.product_name, i.quantity, i.unit_price, i.quantity * i.unit_price AS line_total FROM order_items i JOIN products p ON p.product_id = i.product_id WHERE i.order_id = 103 ORDER BY p.product_id;

  10. Medium. Count orders per customer name, including customers with zero orders. Target: 6 rows: Asha 2, Bilal 2, Charu 1, Dev 2, Esha 1, Farah 0

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

  11. Medium. Find products never sold in a completed order (a pending order does not count). Target: 1 row: Planner

    SELECT p.product_name FROM products p LEFT JOIN (SELECT DISTINCT i.product_id FROM order_items i JOIN orders o ON o.order_id = i.order_id WHERE o.status = 'completed') s ON s.product_id = p.product_id WHERE s.product_id IS NULL;

  12. Medium. Show completed revenue per product name. Target: 5 rows: Keyboard 2400, Desk lamp 1600, Mug 1250, Notebook 600, Pen 300

    SELECT p.product_name, SUM(i.quantity * i.unit_price) AS revenue FROM orders o JOIN order_items i ON i.order_id = o.order_id JOIN products p ON p.product_id = i.product_id WHERE o.status = 'completed' GROUP BY p.product_id, p.product_name ORDER BY revenue DESC, p.product_id;

  13. Medium. Show completed revenue per category. Target: 3 rows: Electronics 4000, Home 1250, Stationery 900

    SELECT p.category, SUM(i.quantity * i.unit_price) AS revenue FROM orders o JOIN order_items i ON i.order_id = o.order_id JOIN products p ON p.product_id = i.product_id WHERE o.status = 'completed' GROUP BY p.category ORDER BY revenue DESC;

  14. Hard. Show completed revenue per city. Target: 3 rows: Mumbai 2600, Delhi 1850, Pune 1700

    SELECT c.city, SUM(i.quantity * i.unit_price) AS revenue FROM customers c JOIN orders o ON o.customer_id = c.customer_id JOIN order_items i ON i.order_id = o.order_id WHERE o.status = 'completed' GROUP BY c.city ORDER BY revenue DESC;

  15. Hard. Count completed orders per customer after joining to items — get 2 for Asha, not 4. Target: 3 rows: Asha 2, Bilal 2, Dev 2

    SELECT c.customer_name, COUNT(DISTINCT o.order_id) AS orders FROM customers c JOIN orders o ON o.customer_id = c.customer_id JOIN order_items i ON i.order_id = o.order_id WHERE o.status = 'completed' GROUP BY c.customer_id, c.customer_name ORDER BY c.customer_id;

  16. Hard. List customers who have orders but no completed order. Target: 2 rows: Charu, Esha

    SELECT c.customer_name FROM customers c JOIN orders o ON o.customer_id = c.customer_id GROUP BY c.customer_id, c.customer_name HAVING SUM(o.status = 'completed') = 0 ORDER BY c.customer_id;

  17. Hard. Show which products each customer bought in completed orders (distinct pairs). Target: 10 rows; first (Asha, Keyboard), last (Dev, Notebook)

    SELECT DISTINCT c.customer_name, p.product_name FROM customers c JOIN orders o ON o.customer_id = c.customer_id JOIN order_items i ON i.order_id = o.order_id JOIN products p ON p.product_id = i.product_id WHERE o.status = 'completed' ORDER BY c.customer_name, p.product_name;

  18. Professional. Find order lines where unit_price differs from the current catalogue price (price-change audit). Target: 0 rows — every historical price matches; explain why the check is still worth keeping

    SELECT i.order_id, p.product_name, i.unit_price, p.price FROM order_items i JOIN products p ON p.product_id = i.product_id WHERE i.unit_price <> p.price;

  19. Professional. Status reconciliation: value of items in completed, cancelled and pending orders; total must be 7550. Target: 3 rows: cancelled 800, completed 6150, pending 600

    SELECT o.status, SUM(i.quantity * i.unit_price) AS item_value FROM orders o JOIN order_items i ON i.order_id = o.order_id GROUP BY o.status ORDER BY o.status;

Up next · Lesson 9

Subqueries and CTEs

85 min · 19 practice problems

Start next lesson
GROUP BY and HAVING Subqueries and CTEs