Stop 03 · SQL for Data AnalysisTopics · 4/10

Joins (INNER, LEFT, RIGHT, FULL)

In short

Orders are in one table, customers in another, products in a third. Joins stitch them together, and choosing the wrong join is the most common way analysts silently lose rows.

Combining rows from two or more tables based on a shared key — INNER returns only matches, LEFT keeps all rows from the first table, RIGHT the second, FULL keeps everything from both.

The jaadu joins matching jigsaw pieces from two colourful piles.

Think of it like this

Matching puzzle pieces from two piles

You have two piles of puzzle pieces. INNER keeps only pieces that found a partner. LEFT keeps every piece from the left pile, partner or not. RIGHT does the same for the right pile. FULL keeps everything, matched or lonely.

Watch it happen

LEFT JOIN customers to orders

customers

orders

result

id 1 (Riya)?

1/5For Riya, the database looks for orders with customer_id = 1.

The big ideas

What you need to know

INNER: only matches

Rows appear only if the key exists in both tables. The default when you just write JOIN.

LEFT: keep all of the left

Every row from the first table stays. Missing matches show NULL. The analyst's favourite.

RIGHT: keep all of the right

The mirror of LEFT. Most people swap the table order and use LEFT instead.

FULL: keep everything

All rows from both sides, matched where possible. Not supported in MySQL.

See it in code

Walk through the code

sql

1SELECT2  c.customer_id,3  c.name,4  COUNT(o.order_id) AS orders5FROM customers AS c6LEFT JOIN orders AS o7  ON o.customer_id = c.customer_id8GROUP BY c.customer_id, c.name9ORDER BY orders;

1/3COUNT of an order column, not *, so customers with no orders get 0 instead of 1.

Every customer with their order count, including people who never ordered (they show 0).

Save yourself the pain

Common mistakes

Oops

Using INNER JOIN and reporting it as 'all customers'.

Do this instead

If you need everyone, start from the customers table and LEFT JOIN the rest.

Oops

Joining on a non-unique key and not noticing rows multiplied.

Do this instead

Check row counts before and after a join. If totals jump, a key is duplicated somewhere.

Your mission

Find the silent customers

~30 min

Quick check

You LEFT JOIN customers to orders. A customer has no orders. What do the order columns show?

Go deeper

1Draw a Venn diagram for each join type before writing the query.
2Practice a LEFT JOIN to find rows in one table with no match in another (e.g. users with no orders).
3Write the same question as an INNER JOIN and a LEFT JOIN and compare row counts.