BeginnerLesson 8 of 305 min read

Joining Tables: INNER and LEFT JOIN

Combine tables with joins, find customers with no orders using LEFT JOIN, and learn how a missing ON condition multiplies rows.

In this lesson you will

  • Join two or more tables with ON
  • Use aliases to avoid unclear column names
  • Find rows with no match using LEFT JOIN
  • Spot a join that multiplies rows

About 28 minutes

The answer is spread over several tables

Maya’s question is: which customer placed which order? The order table stores only a customer number, and the name lives in the customers table. To see both, the database must join the tables, matching each order to the customer whose id equals the order’s customer_id. Joins are the heart of working with relational data, and they are the topic beginners find hardest. Take it slowly: a join is only a matching rule.

INNER JOIN: only the matches

Write JOIN customers AS c followed by ON c.id = o.customer_id, and read it as: take each order, find the customer with the matching id, and put them side by side. Plain JOIN means an inner join. A row appears in the result only if a match was found on both sides. The ON clause states the matching rule, usually that a foreign key equals a primary key. Short aliases such as o and c save typing, and they also fix a real problem: both tables have a column called id, so every use must say which one you mean, as in c.id and o.id.

LEFT JOIN: keep everything on the left

Sometimes you want every customer, whether or not they have orders. A LEFT JOIN keeps every row from the left-hand table, and where no match exists, it fills the right-hand columns with NULL. This is how to find what is missing: join, then keep the rows where a right-hand column is NULL. Some databases also offer right and full joins, and support differs between systems and versions, so check yours. Most people simply write the tables in the order that makes a left join do the job.

Five join queries

-- 1
SELECT o.id, c.name, o.status FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id ORDER BY o.id

-- 2
SELECT oi.order_id, p.name, oi.qty, p.price * oi.qty AS line_total
FROM order_items AS oi JOIN products AS p ON p.id = oi.product_id
WHERE oi.order_id = 6 ORDER BY p.name

-- 3
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 c.id

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

-- 5
SELECT COUNT(*) AS rows_without_on FROM customers, orders
-- 1
id | name | status
1 | Asha | shipped
2 | Ben | shipped
3 | Asha | shipped
4 | Chloe | cancelled
5 | Dev | shipped
6 | Ben | shipped
7 | Ella | pending
8 | Asha | shipped
9 | Dev | shipped
10 | Chloe | pending
-- 2
order_id | name | qty | line_total
6 | Fern | 1 | 12.50
6 | Mist spray | 1 | 4.00
6 | Plant food | 2 | 11.00
-- 3
name | orders
Asha | 3
Ben | 2
Chloe | 2
Dev | 2
Ella | 1
Femi | 0
-- 4
name
Femi
-- 5
rows_without_on
60

Query 1 joined orders to customers and returned 10 rows, one per order, now showing the customer’s name instead of a number. Query 2 joined the order items to products for order 6. It found 3 lines, and the line totals, price times quantity, add up to 27.50. Query 3 is a LEFT JOIN with a count. All 6 customers appear, and Femi has 0 orders. Counting o.id rather than a star matters here, because it counts only real order rows, so a customer with no orders gets zero, not one. Query 4 turned that into a direct question, ‘who never ordered?’, by keeping rows where the order id is NULL, and returned 1 name: Femi.

Query 5 shows what happens if you forget the matching rule. Listing two tables with a comma and no ON condition pairs every row with every other row, a cross join. Six customers times ten orders gives 60 rows. The result looks plausible and is wrong. Whenever a join returns far more rows than you expected, look for a missing or incomplete ON condition.

Checking a join

  1. Say in words what one row of the result should mean, such as one order with its customer.
  2. Count the rows with and without the join. An inner join on a key should not multiply rows unless the relationship is one to many.
  3. Check one example by hand, as with order 6 above.
  4. For LEFT JOINs, test the rows where the right side is NULL.
  5. Name columns with their table alias, so that nobody has to guess.
Common mistake: wrecking a LEFT JOIN with WHERE

Joining on the wrong columns, or adding a WHERE condition on the right-hand table of a LEFT JOIN. That quietly turns it back into an inner join. Put such tests in the ON clause when you want to keep the unmatched rows.

Made-up sample data

The tables and rows are made up. The join syntax shown is standard, but support for right and full joins and some shortcuts varies by database and version, so check your system’s documentation.

Write a query that lists each order with the number of items in it, using a join to order_items and a group. Check one order by hand.

Practice for this lesson

Check what you learned

Five quick questions. Pick an answer and you will see straight away whether it is right and why.

What does an INNER JOIN return?

  1. Every row from both tables
  2. Only unmatched rows
  3. Rows from the left table only
  4. Only rows with a match on both sides

No match, no row.

What does a LEFT JOIN do?

  1. Drops left rows with no match
  2. Keeps every left row and fills missing right values with NULL
  3. Sorts the left table
  4. Deletes the right table

Femi appeared with zero orders.

How do you find customers who never ordered?

  1. INNER JOIN and count
  2. WHERE orders = 0
  3. GROUP BY alone
  4. LEFT JOIN, then keep rows where the order id IS NULL

The NULL on the right side marks a missing match.

Why did joining customers and orders with no ON return 60 rows?

  1. Every customer was paired with every order
  2. There were 60 customers
  3. The database duplicated the data
  4. It was an inner join

A cross join multiplies 6 by 10.

Why count o.id rather than a star in the LEFT JOIN count?

  1. A star is invalid
  2. o.id is faster
  3. A star would count the NULL row as 1
  4. A star counts columns

Counting the right-hand id gives zero for no orders.

Summary

  • A join matches rows using an ON condition, often a foreign key and a primary key.
  • INNER JOIN keeps matches only; LEFT JOIN keeps all left rows.
  • NULL on the right side marks no match.
  • A missing ON condition multiplies rows.

Next lesson

Lesson 9 shows how to change data safely, and how to protect your queries from bad input.

Questions

What is the difference between JOIN and INNER JOIN?

In standard SQL they mean the same thing. Plain JOIN is an inner join.

Can I join more than two tables?

Yes. Add another JOIN with its own ON condition for each extra table, as lesson 10 does.

Are right and full joins available?

Support differs between databases and versions. Check your own system, or rewrite the query so a left join does the job.

Progress is saved in this browser.