Stop 06 · SQL DatabasesTopics · 3/14

Joins

In short

Your data lives in separate tables, but your screens need it together, like an order with the customer's name. Joins stitch tables back together when you query.

SQL's mechanism for combining rows from multiple tables based on a related column — essential for querying data that's spread across your normalized schema.

The blob ties matching rows of a guest list and a plate list together with ribbons.

Think of it like this

Guest list meets plate list

The caterer has a plate list with guest numbers; you have the guest list with names. Line up matching numbers and you know who ate what. Keep only matches: inner join. Keep every guest, even those who skipped dinner: left join.

Watch it happen

Matching orders to users

u1 Riya

u2 Aman

u3 Zoya

o1 by u1

o2 by u1

o3 by u2

1/5Three users on the left, three orders on the right. Each order stores a user_id.

The big ideas

What you need to know

ON says how rows match

Usually a foreign key equals a primary key: orders.user_id = users.id.

INNER JOIN keeps matches

Only rows with a partner on both sides appear. A user with no orders simply vanishes.

LEFT JOIN keeps the left

Every row from the left table stays. Missing partners show up as NULL.

Rows can multiply

One user with five orders gives five rows. Use GROUP BY when you want one row per user.

See it in code

Walk through the code

sql

1-- Every order with the customer's name2SELECT o.id, u.name, o.total3FROM orders o4JOIN users u ON u.id = o.user_id;5 6-- Every user with their order count (0 if none)7SELECT u.name, COUNT(o.id) AS orders8FROM users u9LEFT JOIN orders o ON o.user_id = u.id10GROUP BY u.id, u.name;

1/3Plain JOIN means INNER JOIN. Only orders with a matching user come back.

o and u are short aliases. COUNT(o.id) instead of COUNT(*) is the small trick that makes users with no orders show 0.

Save yourself the pain

Common mistakes

Oops

Using INNER JOIN for a report that must list every customer.

Do this instead

If rows without a match must still show, LEFT JOIN from the table you need to keep.

Oops

Leaving out the join condition and getting thousands of strange rows.

Do this instead

Without a condition, every row pairs with every other row. Always join on the key.

Your mission

Join three tables

~20 min

Quick check

users has 3 rows, and only 2 of them have orders. Which query returns all 3 users?

Go deeper

1Practice INNER JOIN, LEFT JOIN and their different results on the same tables.
2Write a query joining 3+ tables to answer a real question, e.g. 'orders with customer and product names'.
3Learn when a JOIN is more efficient than multiple separate queries.