Do you feel you have a decent understanding of SQL but often find yourself scratching your head trying to understand why a query doesn't work?

Don't feel bad, it's not your fault!

No one ever taught you how your queries are processed and why they work that way.

The SQL query processing order:

1. Source Data - First, the FROM clause is evaluated, and then the resulting data set is passed on to the next processing phase. Note that the JOINS clauses are all processed at this stage in the FROM clause.

2. Row Filter - The WHERE clause evaluates every row using the logical predicate; only rows for which the logical predicate is evaluated to be true will move to the next processing phase.

3. Grouping - The GROUP BY clause evaluates every record in the specified column(s) as a separate group based on the group by expression. The table is transformed into a hybrid grouped structure.

4. Group Filter - The HAVING clause is similar to the WHERE clause in the sense that both consist of a logical predicate; only groups for which the logical predicate is evaluated to true will move to the next processing phase, and it will only be used after the GROUP BY clause.

5. Return Expression - The SELECT clause evaluates each row of the data set that was handed over to it, and it's utilized to indicate the columns that will be returned within the query results.

6. Presentation Order - The ORDER BY clause is used to specify the order in which the query results should be returned.

7. Pagination - The The OFFSET FETCH clause is used to separate large row sets into smaller chunks, known as pages.

SQL processing order is a fundamental aspect of SQL, but I see way too many SQL developers dive right into getting their hands dirty as a starting point and get stuck at that level for the rest of their careers.

In the next series of posts, we will explore more details on how the queries are processed at each of the steps mentioned above.

Have a productive week!