After data is split across tables, we often need to bring it back together. orders may contain only user_id and product_id, but people want names rather than raw IDs. SQL uses JOIN to combine related rows.
These are the source tables used by this section's JOINs. Follow orders.user_id to a user, then orders.product_id to a product.
| users.id | users.name |
|---|---|
| 1 | Alice |
| 2 | Bob |
| 3 | Chris |
| 4 | Diana |
| orders.id | orders.user_id | orders.product_id | orders.quantity |
|---|---|---|---|
| 1 | 1 | 2 | 1 |
| 2 | 1 | 3 | 2 |
| 3 | 2 | 1 | 1 |
| 4 | 3 | 4 | 1 |
| products.id | products.name |
|---|---|
| 1 | Keyboard |
| 2 | Mouse |
| 3 | Coffee Mug |
| 4 | Backpack |
Order 1's foreign keys, user_id = 1 and product_id = 2, therefore resolve to a readable result: Alice · Mouse · 1.
INNER JOIN: keep matches only
INNER JOIN returns rows with a successful match on both sides. ON orders.user_id = users.id pairs each order with the user whose id matches its user_id. Alice appears twice because she has two orders; JOIN combines related rows rather than collapsing users.
SELECT orders.id, users.name, orders.quantity
FROM orders
INNER JOIN users
ON orders.user_id = users.id;Joining three tables
Start from orders, find a user through user_id, then a product through product_id, and finally select the columns to show. Tables often share names such as id and name, so qualify them as users.name, products.name, and orders.id to avoid ambiguity.
Aliases keep long queries readable: o means orders, u users, and p products. After defining them, use o.id, u.name, and p.name.
SELECT o.id, u.name AS user_name,
p.name AS product_name, o.quantity
FROM orders AS o
INNER JOIN users AS u ON o.user_id = u.id
INNER JOIN products AS p ON o.product_id = p.id;LEFT JOIN: preserve every left row
To list every user even when no order exists, start from users and use LEFT JOIN. Alice, Bob, and Chris match orders. Diana remains, but the right-side order_id is filled with NULL. The “left” table is the one after FROM.
SELECT u.name, o.id AS order_id
FROM users AS u
LEFT JOIN orders AS o
ON u.id = o.user_id;RIGHT JOIN: preserve every right row
RIGHT JOIN mirrors LEFT JOIN: all rows from the right table remain, and unmatched left-side columns become NULL. Some SQL products do not support RIGHT JOIN, but swapping table order and using LEFT JOIN expresses the same result.
Choose by asking which side must remain complete: INNER for successful pairs only, LEFT for every row after FROM, and RIGHT for every row after JOIN.
JOIN still works with WHERE and ORDER BY. JOIN combines rows, WHERE filters them, and ORDER BY sorts the result.
INNER vs LEFT: which rows remain?
users is left and orders is right. Switch the join and watch Diana and NULL.
| user | order_id | quantity |
|---|---|---|
| Alice | 1 | 1 |
| Alice | 2 | 2 |
| Bob | 3 | 1 |
| Chris | 4 | 1 |
INNER JOIN keeps only matched rows.RIGHT JOIN: keep the product catalog complete
orders is left and products is right. Switch INNER and RIGHT; watch the never-sold Notebook.
| order_id | product | quantity |
|---|---|---|
| 3 | Keyboard | 1 |
| 1 | Mouse | 1 |
| 2 | Coffee Mug | 2 |
| 4 | Backpack | 1 |
INNER JOIN keeps only matched rows.KNOWLEDGE QUIZ
KNOWLEDGE QUIZ
KNOWLEDGE QUIZ
Build a readable order list
Support needs order_id, user_name, product_name, and quantity. Join orders, users, and products using aliases o, u, and p, then return only Alice's orders.
Find users with no orders
Marketing wants users who have never ordered. Write a LEFT JOIN that preserves all users, then use WHERE o.id IS NULL to keep only unmatched people. Explain why INNER JOIN cannot solve this.