SQL is considered to be a highly deceptive programming language. Many developers spend a lot of time just working on CRUD operations and fairly simple queries. That creates the trend that they are capable of mastering SQL and lessen the motivation to learn more about SQL while SQL is actually quite difficult to grasp.
One thing that many people overlook when considering an SQL query is the order in which the statements are executed. It is really important because it helps us better understand how the query is performed against the data.
A basic query usually looks like this
1 2 3 4 5 6 7 | <span class="token keyword">SELECT</span> <span class="token punctuation">[</span> <span class="token keyword">ALL</span> <span class="token operator">|</span> <span class="token keyword">DISTINCT</span> <span class="token punctuation">]</span> <span class="token operator"><</span> expression list <span class="token operator">></span> <span class="token keyword">FROM</span> <span class="token operator"><</span> <span class="token keyword">table</span> expression <span class="token operator">></span> <span class="token punctuation">[</span> <span class="token keyword">WHERE</span> <span class="token operator"><</span> search condition <span class="token operator">></span> <span class="token punctuation">]</span> <span class="token punctuation">[</span> <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> <span class="token operator"><</span> grouping <span class="token keyword">column</span> list <span class="token operator">></span> <span class="token punctuation">]</span> <span class="token punctuation">[</span> <span class="token keyword">HAVING</span> <span class="token operator"><</span> <span class="token keyword">group</span> condition <span class="token operator">></span> <span class="token punctuation">]</span> |
This query is written in an easy-to-read order like where to get (SELECT) with what condition (WHERE), … But when executed, it will execute in the following order.
1. Execute the FROM clause
Execute FROM clause and initialize work result table. This result table can be a single table or the result of several other tables through JOIN but its final result will always be 1 table.
2. Execute the WHERE clause (if any)
There is not always a WHERE clause in a query. But when there is a WHERE clause, it will execute immediately after the FROM clause. The WHERE clause will apply the <search condition> search condition to each row of the results table from the FROM clause. The rows checked (via <search condition>) if returned TRUE will be retained in the results table, while returning FALSE, or UNKNOWN will be discarded.
If the GROUP BY clause <column list group>, do it next. It uses the FROM and WHERE clause workbooks and divides these rows into groups where the columns in the list of <group group> all have the same value. NULLs are treated as if all are equal and form their own group. Each group is then reduced by one row in the new results table to replace the old row
3. Execute GROUP BY clause (if any)
If there is a GROUP BY clause in the query, it will execute immediately after the WHERE clause. It uses the result table from the WHERE clause, then divides the rows into groups where the columns in the list of <group groups> all have the same value. NULLs are treated as if they were equal and form their own group.
4. Execute HAVING clause (if any)
The HAVING clause will apply <group condition> to each group. TRUE test groups are retained; The test group FALSEor UNKNOWN will be deleted.
5. Finally, the SELECT clause
Finally, apply the SELECT clause to the result table. If a column does not appear in the <expression list>, it will be removed from the final result. Expressions can be constants or column names, or they can be calculations made from constants, columns, functions, and queries.
The above is the order of executing a simple query without subquery. In case of subquery, it will perform subquery in the same first and in the same order and the result of a subquery is also a table. In addition, a query when being entered into the relational database management program is automatically optimized so that the query entered and the query executed may also be different for optimization.