Table redesign
- SQL course
- Beginner
- Lesson 115/115
- 6 min
Rebuild a table with DROP and CREATE.
Challenge 4: Join tables
In this challenge you will write a query that joins two tables and filters the result.
SELECT o.id, c.name FROM orders o JOIN customers c ON o.customer_id = c.id WHERE c.city = 'İstanbul';This query returns orders from Istanbul customers.
o.id, c.name is selected from orders o. customers c is joined on o.customer_id = c.id, keeping only matching rows. WHERE keeps only the rows where c.city = 'İstanbul' holds.
Practice Challenge 4: Join tables
JOIN orders and customers on customer_id = id; return only Istanbul customers.
Starter code
-- join and filter
SELECT o.id, c.name
FROM orders o
JOIN
WHERESample solution
SELECT o.id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.city = 'İstanbul'Expected output
id name 1 Ayşe
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 Join and filter
Question
SELECT o.id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
____ c.city = 'İstanbul';
Answer
SELECT o.id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.city = 'İstanbul';Explanation: After the JOIN, WHERE keeps only orders of customers in Istanbul.
2 Where does the JOIN condition go?
- WHERE
- ON Correct answer
- FROM
- GROUP BY
Explanation: The JOIN condition goes in ON.