Technical

Sneak Peak of the SQL Order Of Execution

Istock 1360521207

After writing SQL codes for a few years now, I noticed I used to make mistakes in the order of execution of SQL Queries. So here I am with my new blog on the same topic.

Let’s jump in to understand the SQL order of execution & learn practical, correct ways to write SQL Codes.

Flowchart

10

Let’s take an example to understand this better.

To do that, I am going to use two simple unnormalized form tables: Citizen and CityThey are described as followed:

1

The citizen table contains data on distinguished citizens and the identification number of the city they live in, and City is the table with city names and their respective identification numbers.

1

Let’s say that we want to know the name of only two city names, except San Bruno, where two or more citizens are living on. We also want the result ordered alphabetically.

This is the query to get the required information.

SELECT city.city_name AS “City”
FROM citizen
JOIN city
ON citizen.city_id = city.city_id
WHERE city.city_name != ‘San Bruno’
GROUP BY city.city_name
HAVING COUNT(*) >= 2
ORDER BY city.city_name ASC
LIMIT 2

Query Process Steps

1. Getting Data (From, Join)
2. Row Filter (Where)
3. Grouping (Group by)
4. Group Filter (Having)
5. Return Expressions (Select)
6. Order & Paging (Order by & Limit / Offset)

Step 1: Getting Data (From, Join)

FROM citizen
JOIN city

The first step in the process is the execution of the statements in From clause, followed by the Join clause. The result of these operations is getting a cartesian product of our two tables.

22

Cartesian Product

Once the From and Join were executed, the processor will get the qualified rows based on the condition On.

ON citizen.city_id = city.city_id

3

Step 2: Row Filter (Where)

After getting qualified rows, it is passed on to the Where clause. This evaluates every row using conditional expressions. When rows do not evaluate to true, they will be removed from the set.

WHERE city.city_name != ‘San Bruno’

4

Step 3: Grouping (Group by)

The next step is to execute Group by clause; it will group rows that have the same values into summary rows. After this point, all Select expressions will be evaluated per group instead of being evaluated per row.

GROUP BY city.city_name

W

Step 4: Group Filter (Having)

The Having clause consists of a logical predicate; it is processed after the Group by and can no longer refer to individual rows, only to groups of rows.

HAVING COUNT(*) >= 2

The result of executing this operation will keep the set, as shown in the figure above. This is because there are two or more elements in every group.

Step 5: Return Expressions (Select)

During this step, the processor evaluates what will be printed as a result of the query and if there are some functions to run on data like DistinctMaxSqrtDate, Lower, etc. In this case, the select clause just prints the city names and alias the city_name column with the identifier “City.”

SELECT city.city_name AS “City”

5

Step 6: Order (Order by) and Paging (Limit / Offset)

The final processing steps of the query deal with presentation ordering and the ability to limit the size of the result set. In our example, it is required to present a maximum of two records ordered alphabetically.

ORDER BY city.city_name ASC
LIMIT 2

6

We got the desired output ( Name of only two city names, except San Bruno, where two or more citizens are living on, alphabetically)

Conclusion

Knowing this order will help in writing the right way of code & helps in understanding too in troubleshooting any error.

I wish you the best of luck in your Data science / Data analysis endeavors!

Thoughts on “Sneak Peak of the SQL Order Of Execution”

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Arpit Malviya

Arpit Malviya has worked at Perficient as a technical consultant. He has been working in the Power BI & Data Analysis field since 2019.

More from this Author

Categories
Follow Us
TwitterLinkedinFacebookYoutubeInstagram