SELECT and the logical order

Every SQL example in this wiki runs in your browser, on a real SQLite, over the tables of a fictional shop: customers, products, orders, order_items and events. Press Run and change whatever you like.

SELECT id, customer_id, order_date, amount
FROM orders
WHERE status = 'delivered' AND amount >= 500
ORDER BY amount DESC, id;

id  | customer_id | order_date | amount
--- | ----------- | ---------- | ------
105 | 4           | 2024-06-03 | 1050
102 | 2           | 2024-06-01 | 900
110 | 1           | 2024-06-05 | 900

The order in which the database thinks

The query is read like this, not in the order in which it was written:

• FROM and JOIN: build the starting rows;
• WHERE: filters row by row;
• GROUP BY: gathers the rows into groups;
• HAVING: filters the groups;
• SELECT: computes the columns (and the aliases are born here);
• DISTINCT, ORDER BY and finally LIMIT/OFFSET.

That is why an alias created in the SELECT does not exist in the WHERE of standard SQL: the filter runs first. Use the full expression or a CTE. (SQLite is lenient and accepts an alias in the WHERE; PostgreSQL and Spark SQL reject it. Do not count on it.)

SELECT id, amount * 0.9 AS discounted
FROM orders
WHERE amount * 0.9 > 800
ORDER BY discounted, id;

id  | discounted
--- | ----------
102 | 810
110 | 810
105 | 945

ORDER BY without LIMIT, LIMIT without ORDER BY

Without ORDER BY, the order of the rows is not guaranteed: in a distributed engine like Spark it changes from one run to the next. LIMIT without ORDER BY returns "some" rows, not "the first" ones.

SELECT name, created_at
FROM customers
ORDER BY created_at DESC
LIMIT 3;

name  | created_at
----- | ----------
Fábio | 2024-05-12
Elisa | 2024-04-01
Diego | 2024-03-15

• SELECT * in production ties the code to the shape of the table: list the columns.
• In columnar formats (Parquet, Delta), reading fewer columns means reading fewer bytes from disk.