SQL query order of execution

Problem

Works here, not there

In the lesson on groups, ORDER BY month used the alias from SELECT. In the lesson on HAVING we repeated the whole SUM(...), and SUM in WHERE gave an error. Why does an alias work in one place and not in another?

The answer is in the order in which the database reads the query. It is not the order in which you write it.

The idea

Written in one order, run in another

You write it like this:

SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT

The database thinks like this:

FROM: where the rows come from;
WHERE: which rows stay;
GROUP BY: the groups;
HAVING: which groups stay;
SELECT: what is shown and what it is called;
ORDER BY: the sorting;
LIMIT: how many rows.

This is the logical order of execution of a query. The three busiest months, without the cancelled orders:

SQLdatabase: shop

SELECT strftime('%Y-%m', ordered_at) AS month, COUNT(*) AS orders
FROM orders
WHERE status <> 'cancelled'
GROUP BY strftime('%Y-%m', ordered_at)
HAVING COUNT(*) >= 5
ORDER BY orders DESC, month
LIMIT 3;

Step by step: FROM takes the 60 orders. WHERE removes the 7 cancelled ones, and 53 are left. GROUP BY splits them into 14 months. HAVING keeps the six with at least 5 orders. SELECT works out the month and the count and names them. ORDER BY sorts them, and LIMIT takes the first three.

More precisely, if you are curious

This is the order in which the query must appear to run. The real plan can be different: the database may use an index or skip a step, as long as the result is the same. We will look at query plans in the module on indexes.

The idea

Why the alias works in ORDER BY

The alias is born in SELECT, at step 5. ORDER BY is at step 6, so the name already exists:

SQLdatabase: shop

SELECT order_id, SUM(quantity * unit_price) AS total
FROM order_items
GROUP BY order_id
ORDER BY total DESC
LIMIT 3;

The three biggest orders. This works in every database. In WHERE, GROUP BY and HAVING, by the rule, the alias does not exist yet, because they come before SELECT.

Try it

The most active customers

Show the five customers with the most orders: customer_id and the count in a column called orders, the most first. On a tie, the smaller customer_id comes first.

SQLdatabase: shop

Solution

SELECT customer_id, COUNT(*) AS orders
FROM orders
GROUP BY customer_id
ORDER BY orders DESC, customer_id
LIMIT 5;

See it

What SQLite forgives

By the rule, an alias in WHERE should not work. The products that cost under 1 € with a 20% discount:

SQLdatabase: shop

SELECT name, price * 0.8 AS sale
FROM products
WHERE sale < 1;

SQLite accepts it. It accepts an alias in GROUP BY too:

SQLdatabase: shop

SELECT strftime('%Y', ordered_at) AS year, COUNT(*)
FROM orders
GROUP BY year;

Other databases are not as lenient. PostgreSQL accepts an alias in GROUP BY, but not in WHERE: there it says the column sale does not exist. SQL Server accepts it neither in WHERE nor in GROUP BY.

SQLite's leniency has limits. If the alias is the same as a column name, WHERE takes the column:

SQLdatabase: shop

SELECT name, price * 0.8 AS price
FROM products
WHERE price < 1;

Two products come out, not four: WHERE price < 1 looks at the real price, not the discounted one. And an alias of an aggregate in WHERE is still “misuse of aggregate”, because there are no groups yet at step 2:

SQLdatabase: shop

SELECT order_id, SUM(quantity * unit_price) AS total
FROM order_items
WHERE total > 30
GROUP BY order_id;

A query that works everywhere: in WHERE repeat the expression (WHERE price * 0.8 < 1), and use aliases freely in ORDER BY.

Exercise

The monthly report

The owner wants a report for 2026: for each month, how many orders there are (without the cancelled ones) and from how many different customers. Months with fewer than 3 orders are too quiet and she does not need them.

Show the columns month (year-month), orders and customers for the months of 2026 with at least 3 orders, not counting cancelled ones, in month order.

SQLdatabase: shop

Solution

SELECT strftime('%Y-%m', ordered_at) AS month,
       COUNT(*) AS orders,
       COUNT(DISTINCT customer_id) AS customers
FROM orders
WHERE status <> 'cancelled' AND ordered_at >= '2026-01-01'
GROUP BY strftime('%Y-%m', ordered_at)
HAVING COUNT(*) >= 3
ORDER BY month;

Test

Check yourself

1. Which part does the database start with when it runs the query?WHERESELECTFROM

2. Why does ORDER BY total work when total is an alias from SELECT?Because total is a column in the tableBecause ORDER BY comes after SELECT, and the name already existsIt only works in SQLite

3. What is true about WHERE sale < 1, when sale is an alias from SELECT?SQLite accepts it, PostgreSQL gives an errorNo database accepts itIt works everywhere

4. The query has ORDER BY orders DESC and LIMIT 3. When are the three rows taken?Before sortingBefore HAVINGAfter sorting, at the very end

The order of query execution

Cookies