Query plan: join, aggregate, compare

Walking relationships with JOIN

To show data from several tables at once you use JOIN, matching rows by their keys: the foreign key of one table with the id of the other.

SELECT customers.name, orders.date
FROM customers
JOIN orders ON orders.customer_id = customers.id;

To know which products each order carries we chain several JOINs, going through the bridge table order_items:

SELECT customers.name, products.name, order_items.quantity
FROM customers
JOIN orders       ON orders.customer_id    = customers.id
JOIN order_items  ON order_items.order_id  = orders.id
JOIN products     ON products.id           = order_items.product_id;

Summarizing with GROUP BY

The amount of each line is quantity * price. For the total spent per customer we sum all their lines and group by customer:

SELECT customers.name, SUM(order_items.quantity * products.price) AS total
FROM customers
JOIN orders       ON orders.customer_id    = customers.id
JOIN order_items  ON order_items.order_id  = orders.id
JOIN products     ON products.id           = order_items.product_id
GROUP BY customers.id
ORDER BY total DESC;

Comparing with subqueries

A subquery is a SELECT inside another. It's useful, for example, to compare each row against a computed value (like an average):

SELECT name FROM products
WHERE price > (SELECT AVG(price) FROM products);

Deterministic order. sql.js compares the result row by row, so we always close with an ORDER BY that leaves no ambiguous ties (adding the id or the name as a tiebreaker when needed).