Week 18 - SQL Chips and Chunks Newsletter

Order of Execution in SQL

1. SQL Chip: Think of SQL queries like a recipe. The database doesn't just read the code top to bottom; it follows a specific, predictable order of execution to cook up your results. This behind-the-scenes process is key to getting the right output. Understanding this logical order is the first step to writing powerful queries.

The database processes your query in this order:

• FROM (and JOINs): Gathers the raw ingredients (your tables).

• WHERE: Filters out rows you don't need.

• GROUP BY: Organizes the remaining rows into logical groups.

• HAVING: Filters those groups based on certain conditions.

• SELECT: Chooses the final columns and performs any calculations.

• DISTINCT: Removes any duplicate rows from the final result.

• ORDER BY: Sorts the final output for presentation.

2. SQL Chunk: Let's see this in action. The FROM clause is always the first ingredient, specifying the table. Then, WHERE filters out rows, and GROUP BY clumps the remaining rows together before the SELECT statement processes them.

SELECT
    product_category,
    COUNT(product_id) AS total_products
FROM
    products
WHERE
    unit_price > 10.00
GROUP BY
    product_category
ORDER BY
    total_products DESC;

(Here, FROM and WHERE happen first, then GROUP BY organizes the data, and finally SELECT and ORDER BY produce the final output.)

3. SQL Challenge: The HAVING clause can feel similar to WHERE, but it's not the same! HAVING is executed much later, after the GROUP BY step, and is used to filter groups of rows, not individual rows. WHERE can't filter based on aggregate functions (like COUNT() or SUM()), but HAVING can.

SELECT
    customer_id,
    SUM(order_total) AS total_spent
FROM
    orders
GROUP BY
    customer_id
HAVING
    SUM(order_total) > 500
ORDER BY
    total_spent DESC;

(The WHERE clause isn't available to filter by SUM(order_total) because the SUM hasn't been calculated yet. HAVING works here because it's executed after the GROUP BY and aggregation step.)

4. SQL Mistake: A common mistake is trying to use a column alias defined in the SELECT statement within the WHERE clause. This doesn't work because the WHERE clause is processed much earlier in the order of execution—before the SELECT statement even runs. This results in an "invalid column name" error.

-- SQL Mistake Example
SELECT
    unit_price * quantity AS total_price,
    product_name
FROM
    order_items
WHERE
    total_price > 100;
-- The `WHERE` clause processes before `total_price` even exists!

-- The FIX: Correct way to filter on a calculated value
SELECT
    unit_price * quantity AS total_price,
    product_name
FROM
    order_items
WHERE
    unit_price * quantity > 100;

5. SQL in Practice: In finance, analysts need to find which of their most valuable clients—those with high account balances—have been valuable clients with recent activity. This requires filtering by individual account activity first (WHERE) and then grouping the data to get an aggregated balance (GROUP BY). The HAVING clause is then used to identify only the most valuable clients. The order here is critical: you first filter the raw transactions, then group them, and then filter those groups.

SELECT
    account_id,
    SUM(transaction_amount) AS total_balance
FROM
    transactions
WHERE
    transaction_date >= '2025-01-01'
GROUP BY
    account_id
HAVING
    SUM(transaction_amount) > 10000;

(The WHERE clause filters by date first, then the data is grouped by account_id, and finally HAVING filters out accounts with less than a $10,000 balance.)

6. SQL Resource: SQL Joins Explained with Execution Order - Essential SQL https://www.essentialsql.com/sql-joins-explained-with-execution-order/ This article provides an excellent visual and textual breakdown of the SQL order of execution, specifically focusing on how it applies to JOIN operations. It clearly explains which steps happen first, helping you understand how tables are combined before any filtering or aggregation takes place. This will give you a more robust and complete mental model of how your queries run.