Did you always think that the SELECT statement gets executed first? Well, no.
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.
- FROM
- JOIN
- WHERE
- GROUP BY
- HAVING
- SELECT
- ORDER BY
- 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.
Consider this problem statement
Write a Query to find out the total order count in India, Germany and USA on the first 2 days of the year. Also, give the top 2 countries by order count.
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
For more SQL tutorials and examples, visit SQL Online.