foundations • Beginner

Relational Model & SQL Query Execution Lifecycle

The journey of a SQL query through the database engine: Parser (AST) -> Query Rewriter -> Cost-Based Optimizer -> Execution Engine -> Buffer Pool / Storage Engine.

Intuitive Mental Model

The Flight Control Center: The passenger requests a destination (declarative SQL). Flight planners calculate fuel costs and wind speeds (Optimizer) before the pilot flies the optimal route (Execution Engine).

SQL DDL / DML QueryPostgreSQL / MySQL

-- Declarative Query (Specify WHAT, not HOW):
SELECT u.name, COUNT(o.id) AS total_orders
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active'
GROUP BY u.name
HAVING COUNT(o.id) >= 5
ORDER BY total_orders DESC
LIMIT 10;

Key Architectural Takeaways

•SQL is declarative: You describe the desired dataset, and the Cost-Based Optimizer (CBO) decides the physical access path.
•Logical Query Execution Order: FROM -> JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> ORDER BY -> LIMIT.
•Understanding execution order explains why column aliases in SELECT cannot be referenced inside the WHERE clause.

Common Production Mistake

Using a SELECT alias in WHERE (e.g. SELECT (price * 1.2) AS total WHERE total > 100), causing syntax error.