SQL Execution Order

SQL queries perform a three-step operation to filter data:

1. Row filtering – This step triggers the FROM, WHERE, GROUP BY, and HAVING clauses.
2. Column filtering – Columns are usually filtered with the SELECT statement.
3. Row filtering – The DISTINCT, ORDER BY, and LIMIT clauses are executed.

Yes, you read that correctly: row filtering happens twice. This occurs if your SQL query demands it or if clauses like DISTINCT, ORDER BY, LIMIT, and others are present in the query. Let’s write a query and analyze how this works:

SELECT id, full_name, university, gpa
FROM student
WHERE gpa > 3.00
ORDER BY full_name;

FROM, WHERE Execution

First, the FROM and WHERE clauses are executed. But why? Imagine you’re asked to bring a sanomara (a specific product). What’s the first thing you’d ask? At the very least, you’d ask "From which store?" This is similar to how SQL works. It first runs the FROM and WHERE clauses to filter the rows. It selects users whose GPA is greater than 3.00 from the student table. This selection is made through some kind of loop mechanism where each row is filtered. If the GPA is greater than 3.00, the row is selected; if not, it moves on to the next one.

GROUP BY, HAVING Execution

After selecting students with a GPA greater than 3.00, the loop finishes. If you’ve requested data to be grouped, the GROUP BY clause will be triggered. If you need to apply additional conditions to the grouped data, you’ll use HAVING (not WHERE). This is because WHERE operates before grouping. After the data is grouped, you’ll need to use the HAVING clause to filter, delete, or perform other operations on the grouped data.

SELECT Execution

The main task of SELECT is to return the columns you need. In other words, whatever columns you specify in the SELECT statement are what will be shown. If you’re wondering why it’s bad to use SELECT * FROM ..., you can read more about it in this article.

ORDER BY, LIMIT Execution

Now that we’ve identified the columns, tables, and rows we want, it’s time to sort them. If you use ORDER BY, the query will sort the selected elements by the specified column. If you’ve also applied a LIMIT, after sorting, it will return only the number of rows you’ve specified.

As you can see, the speed of an SQL query depends on its various stages, and knowing when to use specific clauses is crucial to optimizing performance. Understanding these operations and practicing with them will help you become more efficient. Keep learning and continue following us!

Telegram: @otabekswe