Understanding SQL Query Execution Order

When you write an SQL query, you start it with a SELECT, then FROM and JOIN and eventually WHERE, GROUP BY, HAVING and ORDER BY.

But, while it gets executed, the interpreter executes it in the most optimized way and this is the order of Execution:

1. FROM
2. JOIN
3. WHERE
4. GROUP BY
5. HAVING
6. SELECT
7. ORDER BY
8. LIMIT / OFFSET

Let's understand it through an example

Consider a company having stores across multiple countries. We have a table country which has all the country names. We have another table orders that has a day-wise order count for the first 3 days in January.

SQL Query

SELECT
    ct.country,
    SUM(orders.order_count) AS total_orders
FROM
    country ct
LEFT JOIN
    orders ON ct.id = orders.c_id
WHERE
    orders.date BETWEEN '2024-01-01' AND '2024-01-02'
    AND upper(ct.country) IN ('INDIA', 'GERMANY', 'USA')
GROUP BY
    ct.country
ORDER BY
    total_orders DESC
LIMIT 2;

Execution Breakdown

FROM, JOIN and WHERE

FROM the two tables, the primary key and the respective foreign key, ID and C_ID JOIN and the WHERE Condition get executed. First the Country condition, i.e India, Germany and USA.

The WHERE conditions get executed simultaneously one after another.

GROUP BY / HAVING, SELECT

As we have an aggregation statement i.e SUM (orders.order_count) the GROUP BY acts and the aggregation happens. The orders get summed up.

But, given the sum happens only against the country column, the SELECT statement selects the country and the order_count columns and meanwhile the grouping column works on the submission.

Ultimately, the entire output is limited to the user's request by the LIMIT keyword.

Understanding this execution order helps you write more efficient queries and debug performance issues more effectively. For more SQL tutorials and examples, visit SQL Online.