Loading Academy Hub...
Understand how database query engines actually process SQL clauses vs how humans write them.
A common mistake in data engineering interviews is confusing the Lexical (written) order with the Logical (execution) order.
| Written (Lexical) Order | Logical Execution Order | What Engine Does |
|---|---|---|
1. SELECT | 1. FROM / JOIN | Identifies source tables, evaluates Cartesian products, applies ON filters |
2. FROM | 2. WHERE | Discards rows that do not meet non-aggregate boolean conditions |
3. WHERE | 3. GROUP BY | Groups rows by distinct key combinations into aggregation buckets |
4. GROUP BY | 4. HAVING | Filters aggregate groups using group-level conditions |
5. HAVING | 5. SELECT | Evaluates expressions, columns, UDFs, and assigns aliases |
6. ORDER BY | 6. DISTINCT | Eliminates duplicate rows from the projected result set |
7. LIMIT | 7. ORDER BY | Sorts the final output rows |
8. LIMIT / OFFSET | Slices the requested row window and discards the rest |
[!IMPORTANT] Why Aliases Fail in WHERE Clauses: Because
WHEREis evaluated at Step 2, long beforeSELECTassigns column aliases at Step 5!SELECT price * qty AS total_cost FROM orders WHERE total_cost > 100;fails with "column total_cost does not exist"!
Review the production code, company interview Q&As, incident traces, and comprehension quiz inside this lesson.
-- Correct Enterprise SQL demonstrating Execution Order & Windowing
SELECT
dept_id,
COUNT(*) AS total_employees,
AVG(salary) AS avg_dept_salary,
SUM(salary) AS total_payroll
FROM employees
WHERE is_active = TRUE -- Step 2: Filters raw rows before grouping
GROUP BY dept_id -- Step 3: Groups rows into department buckets
HAVING COUNT(*) >= 5 -- Step 4: Filters groups (only depts with 5+ employees)
ORDER BY total_payroll DESC -- Step 7: Sorts final departments by payroll
LIMIT 10; -- Step 8: Top 10 departments
ERROR: column "net_revenue" does not exist\nLINE 4: WHERE net_revenue > 1000