SQL 32 🛢️ Order of Execution in SQL Queries - KandZ

SQL queries are written in one order and executed in another. The written order is familiar: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY. The execution order is different, and understanding it explains why column aliases work in ORDER BY but not in WHERE, why aggregate functions cannot appear in WHERE, why HAVING can reference aggregates, and why a LIMIT cannot be applied before sorting. These are not arbitrary rules; they are consequences of the sequence in which the database engine processes the query.

The execution order is the mental model that turns SQL from a collection of syntax rules into a coherent system. Once you know that FROM runs first and SELECT runs fifth, the placement of a condition in the wrong clause stops being a mystery. The database does not read a query the way a human does. It assembles the source data, filters it, groups it, computes the output columns, sorts the result, and only then limits what is returned.

This chapter covers the logical execution order, each phase and what it does, the implications for alias visibility and aggregate placement, the role of LIMIT and OFFSET, and the patterns that follow from understanding the sequence.

Key point: SQL executes in the order FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. Column aliases defined in SELECT are available to ORDER BY but not to WHERE, GROUP BY, or HAVING. Aggregate functions are available to HAVING and SELECT but not to WHERE. LIMIT applies after sorting.

Why execution order matters

The alias-visibility problem. A column alias is defined in the SELECT clause. If SELECT runs after WHERE and GROUP BY, then those clauses cannot see the alias. This is why WHERE annual_salary > 100000 fails when annual_salary is an alias defined in SELECT, and why ORDER BY annual_salary works.

The aggregate-placement problem. Aggregate functions are computed during the GROUP BY phase. WHERE runs before that phase, so it cannot reference aggregates. HAVING runs after, so it can. The rule is structural, not a matter of syntax preference.

The limit-placement problem. LIMIT applies to the final result. If it ran before ORDER BY, it would return an arbitrary subset of rows rather than the top N. The execution order guarantees that ORDER BY sorts the full result before LIMIT truncates it.

The subquery problem. A subquery in the WHERE clause is evaluated as part of the WHERE phase, once per outer row for a correlated subquery, or once for an uncorrelated one. A subquery in the FROM clause is evaluated before the outer query, because FROM runs first. The same subquery in different positions can have different performance and different results.

The join-order problem. The FROM clause includes joins. The order in which joins are executed affects performance, and the optimizer chooses the order based on statistics, not on the written order. Understanding that FROM is where joins happen clarifies why join conditions belong in the ON clause and why the optimizer can reorder them.

a. The logical execution order

The complete logical order is:

Step	Clause	Purpose
1	FROM / JOIN	Assemble source rows
2	WHERE	Filter individual rows
3	GROUP BY	Partition rows into groups
4	HAVING	Filter groups
5	SELECT	Compute output columns
6	DISTINCT	Remove duplicate output rows
7	ORDER BY	Sort the result
8	LIMIT / OFFSET	Truncate the result

This is the logical order, not necessarily the physical order. The optimizer may rearrange operations for performance, but the logical order determines what the query means and which names are visible in which clause.

The written order is different. A query is written as SELECT first, FROM second, and so on, because that is how humans read it: what columns, from where, with what filters. The database translates the written form into the logical order.

b. FROM and JOIN

The FROM clause is where the source rows are assembled. It identifies the tables, applies joins, and produces the initial row set that the rest of the query operates on.

FROM employees e
JOIN departments d ON e.department_id = d.department_id

The result of this phase is every combination of rows that satisfies the join condition. If the join produces 1,000 rows, then WHERE, GROUP BY, and the rest operate on those 1,000 rows.

A subquery in the FROM clause — a derived table — is evaluated during this phase:

FROM (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) AS dept_stats

The derived table is computed first, then treated as a table by the outer query. This is why derived tables require an alias: the outer query refers to them by name.

c. WHERE

The WHERE clause filters individual rows. It runs after FROM and before GROUP BY, so it sees the rows that the FROM phase produced, but not the aggregates that GROUP BY will compute.

WHERE active = TRUE AND salary > 50000

Each row is tested against the condition. Rows that pass are kept; rows that fail are discarded. The output of this phase is a subset of the rows from FROM.

Because WHERE runs before SELECT, column aliases defined in SELECT are not visible here:

-- Error: column "annual_salary" does not exist
SELECT salary * 12 AS annual_salary
FROM employees
WHERE annual_salary > 100000;

The fix is to repeat the expression or use a derived table or CTE:

SELECT salary * 12 AS annual_salary
FROM employees
WHERE salary * 12 > 100000;

d. GROUP BY

The GROUP BY clause partitions the remaining rows into groups. Each group is a set of rows that share the same values for the grouping columns.

GROUP BY department_id

The output of this phase is one row per group, with the grouping columns known and the aggregate functions ready to be computed.

A column that is not in GROUP BY and not wrapped in an aggregate cannot be referenced in SELECT, because there is no single value for it within a group:

-- Error: column "first_name" must appear in GROUP BY
SELECT department_id, first_name, COUNT(*)
FROM employees
GROUP BY department_id;

The rule is a consequence of the execution order: after grouping, the only values available are the grouping columns and the aggregates.

e. HAVING

The HAVING clause filters groups. It runs after GROUP BY, so aggregate functions are available.

HAVING COUNT(*) > 5 AND AVG(salary) > 70000

Each group is tested against the condition. Groups that pass are kept; groups that fail are discarded. The output of this phase is a subset of the groups from GROUP BY.

Because HAVING runs before SELECT, column aliases defined in SELECT are not visible here:

-- Error in most databases: alias not available
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING avg_salary > 70000;

The fix is to repeat the aggregate expression:

HAVING AVG(salary) > 70000;

Some databases, notably MySQL, permit aliases in HAVING as a non-standard extension. For portability, repeat the expression.

f. SELECT

The SELECT clause computes the output columns. It runs after WHERE, GROUP BY, and HAVING, so it can reference grouping columns and aggregates, but not the raw rows that were filtered out.

SELECT department_id,
       COUNT(*) AS employee_count,
       AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

This is where column aliases are defined. Because SELECT runs after WHERE, GROUP BY, and HAVING, those clauses cannot reference the aliases. Because SELECT runs before ORDER BY, ORDER BY can.

The SELECT phase also includes DISTINCT, which removes duplicate rows from the output. If DISTINCT is present, it runs immediately after SELECT computes the columns.

g. ORDER BY

The ORDER BY clause sorts the result. It runs after SELECT, so it can reference column aliases.

ORDER BY annual_salary DESC

The sorting applies to the final result set, which means it operates on the output columns, not the original rows. This is why ordering by a column that is not in the SELECT list is allowed in most databases (the column is available for sorting but not for output), and why ordering by an alias works.

SELECT first_name, salary * 12 AS annual_salary
FROM employees
ORDER BY annual_salary DESC;

The alias annual_salary is available because ORDER BY runs after SELECT.

h. LIMIT and OFFSET

The LIMIT clause truncates the result to a specified number of rows. The OFFSET clause skips a specified number of rows before returning the rest. Both run after ORDER BY, so the truncation applies to the sorted result.

ORDER BY annual_salary DESC
LIMIT 10
OFFSET 20;

This returns rows 21 through 30 of the sorted result. If LIMIT ran before ORDER BY, the result would be an arbitrary subset of rows, not the top 10. The execution order guarantees that sorting happens first.

Some databases use FETCH FIRST n ROWS ONLY instead of LIMIT, and OFFSET may be part of the OFFSET ... FETCH syntax. The execution order is the same.

Complete Example Session

-- ============================================
-- PART 1: CREATE SAMPLE DATA
-- ============================================
CREATE TABLE employees (
    employee_id   INTEGER PRIMARY KEY,
    first_name    VARCHAR(50),
    department_id INTEGER,
    salary        NUMERIC(10, 2),
    active        BOOLEAN
);

INSERT INTO employees VALUES
(1, 'Alice',  1, 95000, TRUE),
(2, 'Bob',    1, 78000, TRUE),
(3, 'Carol',  1, 88000, TRUE),
(4, 'David',  2, 72000, TRUE),
(5, 'Eve',    2, 65000, TRUE),
(6, 'Frank',  2, 68000, FALSE),
(7, 'Grace',  3, 92000, TRUE),
(8, 'Henry',  3, 85000, TRUE),
(9, 'Ivy',    3, 79000, TRUE),
(10, 'Jack',  3, 95000, TRUE);

-- ============================================
-- PART 2: FULL QUERY WITH ALL CLAUSES
-- ============================================
SELECT department_id,
       COUNT(*) AS employee_count,
       AVG(salary) AS avg_salary
FROM employees
WHERE active = TRUE
GROUP BY department_id
HAVING COUNT(*) >= 3
ORDER BY avg_salary DESC
LIMIT 2;

-- ============================================
-- PART 3: ALIAS IN WHERE FAILS
-- ============================================
-- Error: column "annual_salary" does not exist
SELECT salary * 12 AS annual_salary
FROM employees
WHERE annual_salary > 100000;

-- ============================================
-- PART 4: ALIAS IN ORDER BY WORKS
-- ============================================
SELECT first_name, salary * 12 AS annual_salary
FROM employees
ORDER BY annual_salary DESC;

-- ============================================
-- PART 5: AGGREGATE IN WHERE FAILS
-- ============================================
-- Error: aggregate functions are not allowed in WHERE
SELECT department_id, COUNT(*)
FROM employees
WHERE COUNT(*) > 2
GROUP BY department_id;

-- ============================================
-- PART 6: AGGREGATE IN HAVING WORKS
-- ============================================
SELECT department_id, COUNT(*) AS cnt
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 2;

-- ============================================
-- PART 7: DERIVED TABLE EVALUATED FIRST
-- ============================================
SELECT dept, avg_salary
FROM (
    SELECT department_id AS dept, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) AS dept_stats
WHERE avg_salary > 75000;

-- ============================================
-- PART 8: CTE WITH SAME EFFECT
-- ============================================
WITH dept_stats AS (
    SELECT department_id AS dept, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT dept, avg_salary
FROM dept_stats
WHERE avg_salary > 75000;

-- ============================================
-- PART 9: LIMIT AFTER ORDER BY
-- ============================================
SELECT first_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

-- ============================================
-- PART 10: CORRECTING ALIAS ISSUES WITH CTE
-- ============================================
WITH emp AS (
    SELECT first_name, salary * 12 AS annual_salary
    FROM employees
)
SELECT first_name, annual_salary
FROM emp
WHERE annual_salary > 100000;

These ten parts cover a full query with all clauses, alias failures in WHERE, alias success in ORDER BY, aggregate failures in WHERE, aggregate success in HAVING, derived tables, CTEs, LIMIT after ORDER BY, and using a CTE to filter on a computed value.

Quick Reference

Execution Order

Step	Clause	What Happens
1	FROM / JOIN	Assemble source rows
2	WHERE	Filter rows
3	GROUP BY	Partition into groups
4	HAVING	Filter groups
5	SELECT	Compute output columns
6	DISTINCT	Remove duplicate rows
7	ORDER BY	Sort result
8	LIMIT / OFFSET	Truncate result

Alias Visibility

Clause	Can Reference Column Alias	Can Reference Table Alias
SELECT	Defining	Yes
WHERE	No	Yes
GROUP BY	No (standard)	Yes
HAVING	No (standard)	Yes
ORDER BY	Yes	Yes

Aggregate Availability

Clause	Aggregates Allowed
WHERE	No
GROUP BY	No
HAVING	Yes
SELECT	Yes
ORDER BY	Yes

Written vs Execution Order

Written Order	Execution Order
SELECT	FROM
FROM	WHERE
WHERE	GROUP BY
GROUP BY	HAVING
HAVING	SELECT
ORDER BY	DISTINCT
LIMIT	ORDER BY
	LIMIT

Best Practices

✅ Do This:

-- Filter rows in WHERE, groups in HAVING
SELECT department_id, AVG(salary)
FROM employees
WHERE active = TRUE
GROUP BY department_id
HAVING AVG(salary) > 70000;

-- Use CTE to filter on computed value
WITH emp AS (SELECT salary * 12 AS annual FROM employees)
SELECT * FROM emp WHERE annual > 100000;

-- Use alias in ORDER BY
SELECT salary * 12 AS annual FROM employees ORDER BY annual;

❌ Don’t Do This:

-- Alias in WHERE
WHERE annual_salary > 100000  -- ❌ alias not available

-- Aggregate in WHERE
WHERE COUNT(*) > 5  -- ❌ aggregate not available

-- LIMIT before ORDER BY
LIMIT 10 ORDER BY salary  -- ❌ wrong syntax and logic

Common Pitfalls

Pitfall	Why It Happens	Fix
Alias not found in WHERE	SELECT runs after WHERE	Repeat expression or use CTE
Aggregate not allowed in WHERE	GROUP BY runs after WHERE	Use HAVING
Column not in GROUP BY	Not grouped or aggregated	Add to GROUP BY
Alias not found in HAVING	SELECT runs after HAVING	Repeat aggregate expression
LIMIT returns arbitrary rows	No ORDER BY	Add ORDER BY before LIMIT
Derived table missing alias	Required in most databases	Add `AS name`

Real-World Examples

1. Full Clause Order

SELECT department_id, COUNT(*) AS cnt
FROM employees
WHERE active = TRUE
GROUP BY department_id
HAVING COUNT(*) > 2
ORDER BY cnt DESC
LIMIT 5;

2. Alias in WHERE Fails

-- Error
SELECT price * quantity AS total FROM items WHERE total > 100;

3. Fix with CTE

WITH item_totals AS (
    SELECT price * quantity AS total FROM items
)
SELECT * FROM item_totals WHERE total > 100;

4. Alias in ORDER BY Works

SELECT price * quantity AS total FROM items ORDER BY total DESC;

5. Aggregate in HAVING

SELECT customer_id, SUM(total) AS lifetime
FROM orders GROUP BY customer_id HAVING SUM(total) > 10000;

6. Derived Table Evaluated First

SELECT * FROM (SELECT MAX(salary) AS max_sal FROM employees) AS t;

7. LIMIT After ORDER BY

SELECT * FROM products ORDER BY price DESC LIMIT 10;

8. Top N per Group

WITH ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
    FROM employees
)
SELECT * FROM ranked WHERE rn <= 3;

9. HAVING with Subquery

SELECT department_id, AVG(salary)
FROM employees GROUP BY department_id
HAVING AVG(salary) > (SELECT AVG(salary) FROM employees);

10. DISTINCT After SELECT

SELECT DISTINCT department_id FROM employees ORDER BY department_id;

Visual

Execution Pipeline

┌──────────────────────────────────────────────────────────────┐
│  FROM / JOIN      ──▶ Assemble rows                          │
│       │                                                      │
│       ▼                                                      │
│  WHERE            ──▶ Filter rows                            │
│       │                                                      │
│       ▼                                                      │
│  GROUP BY         ──▶ Partition into groups                  │
│       │                                                      │
│       ▼                                                      │
│  HAVING           ──▶ Filter groups                          │
│       │                                                      │
│       ▼                                                      │
│  SELECT           ──▶ Compute output columns                 │
│       │                                                      │
│       ▼                                                      │
│  DISTINCT         ──▶ Remove duplicate rows                  │
│       │                                                      │
│       ▼                                                      │
│  ORDER BY         ──▶ Sort result                            │
│       │                                                      │
│       ▼                                                      │
│  LIMIT / OFFSET   ──▶ Truncate result                        │
└──────────────────────────────────────────────────────────────┘

Alias and Aggregate Availability

┌──────────────────────────────────────────────────────────────┐
│  CLAUSE      │  COLUMN ALIAS  │  TABLE ALIAS  │  AGGREGATE   │
│  ────────────┼────────────────┼───────────────┼───────────── │
│  FROM        │  No            │  Defining     │  No          │
│  WHERE       │  No            │  Yes          │  No          │
│  GROUP BY    │  No            │  Yes          │  No          │
│  HAVING      │  No            │  Yes          │  Yes         │
│  SELECT      │  Defining      │  Yes          │  Yes         │
│  ORDER BY    │  Yes           │  Yes          │  Yes         │
└──────────────────────────────────────────────────────────────┘

Why Alias Fails in WHERE

┌──────────────────────────────────────────────────────────────┐
│  SELECT salary * 12 AS annual_salary                         │
│  FROM employees                                              │
│  WHERE annual_salary > 100000;                               │
│                                                              │
│  Execution:                                                  │
│  1. FROM employees → rows                                    │
│  2. WHERE annual_salary > 100000 → alias not yet defined ❌  │
│  3. SELECT salary * 12 AS annual_salary → alias defined here │
│                                                              │
│  Fix: repeat expression or use CTE.                          │
└──────────────────────────────────────────────────────────────┘

LIMIT After ORDER BY

┌──────────────────────────────────────────────────────────────┐
│  SELECT * FROM products                                      │
│  ORDER BY price DESC                                         │
│  LIMIT 10;                                                   │
│                                                              │
│  Execution:                                                  │
│  1. FROM products → all rows                                 │
│  2. SELECT * → all columns                                   │
│  3. ORDER BY price DESC → sorted by price                    │
│  4. LIMIT 10 → top 10 rows of sorted result                  │
│                                                              │
│  Without ORDER BY, LIMIT returns an arbitrary 10 rows.       │
└──────────────────────────────────────────────────────────────┘

Summary

Item	Value
Execution order	FROM, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, LIMIT
FROM	Assembles source rows and applies joins
WHERE	Filters individual rows
GROUP BY	Partitions rows into groups
HAVING	Filters groups; aggregates available
SELECT	Computes output columns and defines aliases
DISTINCT	Removes duplicate rows
ORDER BY	Sorts the result; aliases available
LIMIT	Truncates the result
Column alias in WHERE	Not available
Column alias in ORDER BY	Available
Aggregate in WHERE	Not allowed
Aggregate in HAVING	Allowed

Key takeaways:

SQL is written in one order and executed in another. The written order is SELECT first; the execution order is FROM first. The difference explains why certain clauses can reference certain names and others cannot.
WHERE runs before GROUP BY, so it cannot reference aggregates. A condition on an aggregate must go in HAVING, which runs after grouping.
SELECT runs after WHERE and HAVING, so aliases are not available to them. A column alias defined in SELECT can be used in ORDER BY, because ORDER BY runs after SELECT.
GROUP BY creates groups; only grouping columns and aggregates are available afterward. A column that is not grouped and not aggregated cannot appear in SELECT.
ORDER BY runs before LIMIT. This guarantees that LIMIT truncates the sorted result, not an arbitrary subset. Without ORDER BY, LIMIT returns arbitrary rows.
Derived tables and CTEs are evaluated before the outer query. A subquery in FROM is computed first; the outer query treats the result as a table. This is how you filter on a computed value that WHERE cannot see.
The optimizer may reorder operations for performance, but the logical order determines meaning. The written query means what the logical order says it means, regardless of how the database physically executes it.

Remember: The execution order is the mental model that makes SQL coherent. FROM assembles rows, WHERE filters them, GROUP BY partitions them, HAVING filters the groups, SELECT computes the output, ORDER BY sorts it, and LIMIT truncates it. Every rule about where aliases and aggregates can be used follows from this sequence. Once you internalize the order, you stop making the common mistakes: putting an aggregate in WHERE, referencing a column alias in WHERE, expecting LIMIT to return the top N without ORDER BY. The patterns that fix these mistakes — repeating the expression, using HAVING, using a CTE — become obvious. SQL is not arbitrary; it is a pipeline, and knowing the pipeline is knowing the language.

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!