Table Aliases and Multi-Table Joins

Writing full table names gets long. Table aliases give each table a short name for the rest of the query:

SELECT o.id, c.name, o.status
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = 'pending';

+----+--------------+---------+
| id |     name     | status  |
+----+--------------+---------+
|  6 | Alan Turing  | pending |
|  8 | Ada Lovelace | pending |
+----+--------------+---------+

AS is optional for table aliases (FROM orders o). Pick short but recognizable aliases — the table's initial is common.

Joining three or more tables

Each JOIN adds another table. To see who bought what, go from customers to orders to order items to products:

SELECT c.name AS customer, o.id AS order_id, p.name AS product, oi.quantity
FROM customers c
JOIN orders o       ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p     ON p.id = oi.product_id
WHERE c.name = 'Ada Lovelace'
ORDER BY o.id, p.name;

+--------------+----------+---------------------+----------+
|   customer   | order_id |       product       | quantity |
+--------------+----------+---------------------+----------+
| Ada Lovelace |        1 | Mechanical Keyboard |        1 |
| Ada Lovelace |        1 | USB-C Hub           |        2 |
| Ada Lovelace |        2 | 27-inch Monitor     |        1 |
| Ada Lovelace |        8 | Webcam              |        3 |
+--------------+----------+---------------------+----------+

Think of it as following the arrows between tables: each ON condition follows one foreign key.

Joins with aggregation

Joins and GROUP BY together answer questions like "revenue per customer":

SELECT c.name, SUM(oi.quantity * oi.unit_price) AS revenue
FROM customers c
JOIN orders o       ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status <> 'cancelled'
GROUP BY c.id, c.name
ORDER BY revenue DESC;

+-------------------+---------+
|       name        | revenue |
+-------------------+---------+
| Ada Lovelace      |  589.69 |
| Grace Hopper      |  583.98 |
| Margaret Hamilton |     241 |
| Linus Torvalds    |   234.5 |
| Alan Turing       |      89 |
+-------------------+---------+

Grouping by c.id as well as c.name keeps two customers with the same name separate.

Practice

Show the total quantity sold of each product, with its name, most sold first.

SELECT p.name, SUM(oi.quantity) AS units_sold
FROM products p
JOIN order_items oi ON oi.product_id = p.id
GROUP BY p.id, p.name
ORDER BY units_sold DESC, p.name;

+-----------------------------+------------+
|            name             | units_sold |
+-----------------------------+------------+
| Webcam                      |          4 |
| 27-inch Monitor             |          3 |
| Laptop Stand                |          3 |
| USB-C Hub                   |          3 |
| Mechanical Keyboard         |          2 |
| Noise-Cancelling Headphones |          2 |
+-----------------------------+------------+