Definition
- Logical order –
FROM/JOIN→WHERE→GROUP BY→HAVING→SELECT→DISTINCT→ORDER BY→LIMIT/OFFSET. - SELECT is late – the database evaluates
SELECTexpressions and their aliases afterWHEREandGROUP BYhave already run. - Alias not in WHERE – because
WHEREruns beforeSELECT, a column alias defined inSELECTdoes not exist yet whenWHEREexecutes. - Alias in ORDER BY –
ORDER BYruns afterSELECT, so it can referenceSELECTaliases. - Aggregates in HAVING –
HAVINGruns afterGROUP BY, so it can use aggregate functions;WHEREcannot because grouping has not happened yet.
Imagine processing a stack of forms at National Bank of India. First you pull together the record tables (FROM/JOIN). Then you throw away forms that fail a filter (WHERE). Then you sort into piles by category (GROUP BY) and discard piles that fail a group filter (HAVING). Only then do you fill in computed summary columns (SELECT). The computed column simply does not exist earlier when the WHERE filter runs.
Understanding the Concepts
What is the logical execution order?
The database logically evaluates clauses in this order, regardless of how the query is written.
1. FROM / JOIN – build the working set by combining tables
2. WHERE – filter individual rows (before grouping)
3. GROUP BY – collapse rows into groups
4. HAVING – filter groups (can use aggregates)
5. SELECT – evaluate output expressions, aliases, window functions
6. DISTINCT – remove duplicate output rows
7. ORDER BY – sort the final rows
8. LIMIT / OFFSET – take the final slice
Why can a SELECT alias not be referenced in WHERE?
The alias is created during step 5 (SELECT), but WHERE runs at step 2. The alias does not exist yet.
-- Wrong: gross does not exist when WHERE runs
SELECT amount * 1.18 AS gross
FROM transactions
WHERE gross > 10000; -- ERROR: column "gross" does not exist
-- Fix 1: repeat the expression
SELECT amount * 1.18 AS gross
FROM transactions
WHERE amount * 1.18 > 10000;
-- Fix 2: use a subquery or CTE
WITH computed AS (
SELECT txn_id, amount * 1.18 AS gross
FROM transactions
)
SELECT * FROM computed WHERE gross > 10000;
At National Bank of India, an analyst wrote WHERE gross > 10000 and got an error. Rajesh explained the logical order.
Why can aggregates appear in HAVING but not WHERE?
GROUP BY runs at step 3. HAVING runs at step 4 (after grouping), so aggregate functions are available. WHERE runs at step 2 (before grouping), so aggregates do not exist yet.
-- Wrong: SUM does not exist when WHERE runs
SELECT department, SUM(salary) AS total
FROM employees
WHERE SUM(salary) > 500000 -- ERROR!
GROUP BY department;
-- Correct: use HAVING for aggregate filters
SELECT department, SUM(salary) AS total
FROM employees
GROUP BY department
HAVING SUM(salary) > 500000;
Why can ORDER BY reference SELECT aliases?
ORDER BY runs at step 7, after SELECT (step 5). So the alias exists by the time ORDER BY executes.
-- Valid: ORDER BY runs after SELECT
SELECT department, SUM(salary) AS total
FROM employees
GROUP BY department
ORDER BY total DESC; -- alias 'total' is available here
Which engines relax the alias rules?
Some engines allow aliases in GROUP BY or HAVING as a convenience, but relying on this is non-portable.
-- MySQL allows this (non-standard)
SELECT YEAR(created_at) AS yr, COUNT(*) AS cnt
FROM transactions
GROUP BY yr; -- alias in GROUP BY: works in MySQL, not in Oracle
-- PostgreSQL also allows alias in GROUP BY
-- Oracle and SQL Server do NOT
-- Portable approach: repeat the expression
SELECT YEAR(created_at) AS yr, COUNT(*) AS cnt
FROM transactions
GROUP BY YEAR(created_at);
How does the logical order affect window functions?
Window functions are evaluated during the SELECT step (step 5), after WHERE and GROUP BY. They cannot be used in WHERE or HAVING.
-- Wrong: window function in WHERE
SELECT *, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees
WHERE rn = 1; -- ERROR: window functions not allowed in WHERE
-- Correct: wrap in a subquery
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees
) ranked
WHERE rn = 1;
Is the logical order the same as the physical execution order?
No. The logical order is what the result must be consistent with, not the physical execution order. The optimizer is free to reorder operations, push filters down, or compute values early, as long as the final result matches what the logical order would produce.
EXPLAIN ANALYZE
SELECT a.account_name, SUM(t.amount) AS total
FROM accounts a
JOIN transactions t ON t.account_id = a.id
WHERE a.city = 'Mumbai'
GROUP BY a.account_name
HAVING SUM(t.amount) > 100000
ORDER BY total DESC
LIMIT 10;
-- The optimizer may push the city='Mumbai' filter into the accounts scan
-- even though logically WHERE runs after FROM/JOIN
-- The result is the same either way
How do you demonstrate the full order in a single query?
-- Full demonstration at National Bank of India
SELECT -- Step 5: compute aliases
a.city,
COUNT(*) AS num_accounts,
SUM(a.balance) AS total_balance
FROM accounts a -- Step 1: source table
JOIN customers c ON c.id = a.customer_id -- Step 1: join
WHERE c.status = 'ACTIVE' -- Step 2: filter rows
GROUP BY a.city -- Step 3: group
HAVING SUM(a.balance) > 1000000 -- Step 4: filter groups
ORDER BY total_balance DESC -- Step 7: sort (alias OK)
LIMIT 5; -- Step 8: slice
What are common interview follow-up questions?
- Can you use a column alias in a JOIN ON clause? (No, JOIN is step 1 and SELECT is step 5.)
- Why does
GROUP BY 1work? (It is a positional reference to the first SELECT column, a convenience allowed by some engines.) - Can WHERE use a column from a subquery in SELECT? (No, but a lateral join or a CTE can achieve this.)
Visual Overview
Written order: Logical execution order:
┌──────────┐ ┌──────────────┐
│ SELECT │ │ 1. FROM/JOIN │
│ FROM │ │ 2. WHERE │
│ WHERE │ ───▶ │ 3. GROUP BY │
│ GROUP BY │ │ 4. HAVING │
│ HAVING │ │ 5. SELECT │
│ ORDER BY │ │ 6. DISTINCT │
│ LIMIT │ │ 7. ORDER BY │
└──────────┘ │ 8. LIMIT │
└──────────────┘
Alias visibility:
WHERE ──▶ alias NOT available (runs before SELECT)
HAVING ──▶ alias NOT available (runs before SELECT)
ORDER BY ──▶ alias IS available (runs after SELECT)
Key Takeaway
Even though SQL queries are written starting with SELECT, the database logically evaluates clauses in a very different order. FROM and JOIN build the working set first. Then WHERE filters individual rows before any grouping. GROUP BY collapses rows into groups, and HAVING filters those groups. Only then does SELECT evaluate output expressions, aliases, and window functions. After that DISTINCT removes duplicates, ORDER BY sorts, and LIMIT takes the slice.
This order explains why a SELECT alias cannot be referenced in WHERE: the alias is created at step 5 but WHERE runs at step 2, so it does not exist yet. The fix is either to repeat the expression or wrap the computation in a subquery or CTE. The same reasoning explains why aggregate filters like SUM(amount) > 50000 must go in HAVING, not WHERE.
ORDER BY is the exception: it runs after SELECT, so it can reference aliases. Some engines like MySQL relax the rules further and allow aliases in GROUP BY, but relying on that is non-portable.
At the National Bank of India an analyst wrote SELECT amount * 1.18 AS gross ... WHERE gross > 10000 and got an error. Rajesh explained the logical order and showed how to use a CTE to make the alias available.
Remember one sentence: SQL is written SELECT-first but runs FROM first and SELECT late, so aliases created in SELECT are not visible to WHERE.
---