SQL Basics · Lesson 7 of 9

Joining Tables

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 customers adds the second table.
  • ON says how the rows match: the id of the customer must equal the customer_id of 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
TableColumns
customersid, name, city, joined
productsid, name, category, price, stock
ordersid, 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

  1. List every order with the name of the product that was ordered.
  2. Return three columns: the product's name, the order's quantity and the ordered date.
  3. Sort by the ordered date, earliest first.
  • The query uses JOIN with an ON condition
  • The result has the columns name, quantity and ordered
  • All fourteen orders are listed with the right product name
  • The rows are sorted by date, earliest first