SQL Order of Operations

I’ve been trying to teach myself SQL. It’s going slower than I would like but I am pretty confident that I am making progress. One of my struggles has been that in my Data Science Bootcamp you are just thrown into the deep end of the pool and it is sink or swim. You are shown how to do things but not really the why. So, of course, this is another “what I wish I knew then what I know now” piece.

What do I wish was one of the first thing that I was taught about SQL? The order of operations! In grade school we learned how to add, subtract, multiply and divide. If that were enough, we would think that

5–3*0+30/5 = 6

But that would be incorrect! We also needed to learn PEMDAS in conjunction with learning how to add, subtract, multiply and divide to get to the correct answer:

5–3*0+30/5 = 11

Similarly, we are taught to read from left to right, top to bottom and you would think that reading and executing a SQL query would follow the same convention but that won’t help you to understand SQL queries and, more importantly, how to debug a SQL query. There is a specific order of operations with SQL and learning and understanding this makes implementing and understanding SQL syntax so much easier.

So let’s look at the order of operations:

https://www.periscopedata.com/blog/sql-query-order-of-operations

And a sample query:

Breaking down the query:

1. I will first look at the FROM statement and it is the JOIN of table_1 and table_2 that will be executed first. In my mind’s eye, I can now see what data I am working with.
2. Next I will look to the WHERE statement which is going to filter the data based on some sort of constraint.
3. If there is an aggregate in the SELECT statement, the query will then execute the GROUP BY statement, showing one value (the aggregate) per the grouped field (column_1 in this example).
4. Next would be the HAVING statement which will filter the aggregated data based on a constraint. (This is essentially a WHERE statement but only for aggregated data.)
5. Next to be executed will be the SELECT statement. This statement will return the values specified after filtering (WHERE, HAVING) and grouping (GROUP BY).
6. The ORDER BY statement returns the table ordered by the specified column. ASC and DESC is used to specify whether you want the column order in ascending or descending order. ASC is default.
7. And, finally, the LIMIT statement lets you specify how many rows you want returned in your table.

So this blog may be a bit on the shorter side but what you need to take away from it is that you need to take the time to truly memorize and understand the SQL order the operations much like so many of us still remember PEMDAS from when we were children.