Proper Query Execution Ordering
Understand the order in which SQL clauses are executed to write more efficient queries.
Understanding the order in which SQL clauses are executed helps you write more efficient queries. Filters applied earlier reduce the amount of data processed in later steps.
graph TD
A[FROM / JOIN] --> B[WHERE]
B --> C[GROUP BY]
C --> D[Aggregate Functions]
D --> E[HAVING]
E --> F[Window Functions]
F --> G[SELECT]
G --> H[DISTINCT]
H --> I[UNION / INTERSECT / EXCEPT]
I --> J[ORDER BY]
J --> K[OFFSET]
K --> L[LIMIT / FETCH / TOP]
style A fill:#1a1a1a,stroke:#00ff41,stroke-width:2px
style B fill:#1a1a1a,stroke:#00ff41,stroke-width:2px
style C fill:#1a1a1a,stroke:#00ff41,stroke-width:2px
style D fill:#252525,stroke:#00ff41,stroke-width:2px
style E fill:#1a1a1a,stroke:#00ff41,stroke-width:2px
style F fill:#252525,stroke:#00ff41,stroke-width:2px
style G fill:#1a1a1a,stroke:#00ff41,stroke-width:2px
style H fill:#1a1a1a,stroke:#00ff41,stroke-width:2px
style I fill:#1a1a1a,stroke:#00ff41,stroke-width:2px
style J fill:#1a1a1a,stroke:#00ff41,stroke-width:2px
style K fill:#1a1a1a,stroke:#00ff41,stroke-width:2px
style L fill:#1a1a1a,stroke:#00ff41,stroke-width:2px
Execution Order Details
1. FROM / JOIN
The database first identifies the source tables and joins them together. This creates the initial result set.
2. WHERE
Rows are filtered before any aggregation. Applying filters here reduces the data volume for subsequent steps.
3. GROUP BY
Rows are grouped based on the specified columns. This happens after WHERE filtering.
4. Aggregate Functions
Functions like COUNT, SUM, AVG, MAX are applied to each group.
5. HAVING
Filtered after aggregation. Use this for filtering on aggregated values (e.g., HAVING COUNT(*) > 5).
6. Window Functions
Applied after aggregation but before SELECT. Functions like ROW_NUMBER, RANK, LAG, LEAD.
7. SELECT
Columns are selected and expressions are computed.
8. DISTINCT
Duplicates are removed.
9. UNION / INTERSECT / EXCEPT
Set operations are performed.
10. ORDER BY
Results are sorted.
11. OFFSET
Rows are skipped (for pagination).
12. LIMIT / FETCH / TOP
Final row count is limited.
Performance Implications
Applying filters early (WHERE) is more efficient than applying them later (HAVING), because less data flows through the subsequent steps.
-- Efficient: Filter before aggregation
SELECT customer_id, COUNT(*) as order_count
FROM orders
WHERE status = 'completed' -- Filters 100K rows to 60K
GROUP BY customer_id
HAVING COUNT(*) > 5;
-- Less efficient: Filter after aggregation
SELECT customer_id, COUNT(*) as order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5 AND status = 'completed'; -- Can't use WHERE on non-grouped column
ℹ️ Modern database query optimizers are increasingly sophisticated and may automatically apply many of these optimizations regardless of how the query is written. Always test with EXPLAIN or EXPLAIN ANALYZE for your specific database and data distribution.
Placing Most Restrictive Filters Early
In complex queries with CTEs or multiple JOINs, place the most restrictive filter as early as possible to reduce the amount of data processed in subsequent steps.
-- Less efficient: Filter late
WITH base AS (
SELECT * FROM orders
),
aggregated AS (
SELECT customer_id, COUNT(*) as order_count
FROM base
GROUP BY customer_id
)
SELECT * FROM aggregated
WHERE customer_id > 1000 AND status = 'completed'; -- Filter applied late
-- More efficient: Filter early in CTE
WITH base AS (
SELECT * FROM orders
WHERE status = 'completed' -- Filter applied early
),
aggregated AS (
SELECT customer_id, COUNT(*) as order_count
FROM base
WHERE customer_id > 1000 -- Filter applied in CTE
GROUP BY customer_id
)
SELECT * FROM aggregated;
Logical Predicate Order
The order of execution of logical predicates follows this hierarchy:
NOT → AND → OR
-- This:
WHERE NOT status = 'cancelled' AND total > 100 OR customer_id = 5
-- Executes as:
WHERE (NOT (status = 'cancelled')) AND (total > 100) OR (customer_id = 5)
For better performance and clarity, use parentheses to group conditions explicitly.
Filter Count vs Performance
There may be a point where adding more filters actually slows query performance. This is especially true with CTEs, where each filter creates a new intermediate result set.
-- Test different filter combinations to find optimal performance
-- Too many filters in CTEs can create overhead
WITH filtered AS (
SELECT * FROM orders
WHERE status = 'completed' -- Filter 1
AND total > 100 -- Filter 2
AND created_at > '2024-01-01' -- Filter 3
AND customer_id IN (SELECT id FROM customers WHERE active = true) -- Filter 4
AND region = 'US' -- Filter 5
)
SELECT * FROM filtered;
💡 Test how many filtering conditions are needed to maximize performance. Sometimes combining filters or removing redundant ones improves speed.
CASE WHEN Evaluation Order
CASE WHEN expressions are evaluated from first to last. Place the most common conditions first for better performance.
-- More efficient: Most common condition first
CASE
WHEN status = 'completed' THEN 'Done' -- Most common
WHEN status = 'pending' THEN 'Pending'
WHEN status = 'cancelled' THEN 'Cancelled'
ELSE 'Unknown'
END
-- Less efficient: Rare condition first
CASE
WHEN status = 'cancelled' THEN 'Cancelled' -- Rare case
WHEN status = 'pending' THEN 'Pending'
WHEN status = 'completed' THEN 'Done'
ELSE 'Unknown'
END