Look at the orders table and you will see mostly numbers:
SELECT * FROM orders;
An order does not repeat the customer's name or the product's price. It stores a customer_id and a product_id, which point to rows in the other tables. This avoids storing the same information twice, and it is how nearly all databases are organized.
To see names instead of ids, join the tables:
SELECT orders.id, customers.name, orders.quantity
FROM orders
JOIN customers ON customers.id = orders.customer_id;
JOIN customersadds the second table.ONsays how the rows match: theidof the customer must equal thecustomer_idof the order.
For every order, the database finds the customer it belongs to and glues the two rows together.
Saying which table
Both tables have a column called id, so write table.column to be clear. It is a good habit for every column in a join.
More than two tables
Add another JOIN for each extra table:
SELECT customers.name, products.name, orders.quantity
FROM orders
JOIN customers ON customers.id = orders.customer_id
JOIN products ON products.id = orders.product_id;
A joined result behaves like any table: you can add WHERE, GROUP BY, ORDER BY and LIMIT after the joins.
This kind of join only returns rows that have a match in both tables. A customer who never ordered does not appear at all. A LEFT JOIN keeps them, which is a topic for another day.
Tables in the practice database
| Table | Columns |
|---|---|
customers | id, name, city, joined |
products | id, name, category, price, stock |
orders | id, customer_id, product_id, quantity, ordered |
Every run starts from a fresh copy of the database, so nothing you do can break it.
Your Task
- List every order with the name of the product that was ordered.
- Return three columns: the product's
name, the order'squantityand theordereddate. - Sort by the
ordereddate, earliest first.
- The query uses
JOINwith anONcondition - The result has the columns
name,quantityandordered - All fourteen orders are listed with the right product name
- The rows are sorted by date, earliest first