SQL "Order of execution" vs "Order of writing"

SQL is a declarative language, not a procedural language. That means that the SQL compiler and optimizer determine what operations are actually run. These operations typically take the form of a directed acyclic graph (DAG) of operations.

The operators have no obvious relationship to the original query -- except that the results it generates are guaranteed to be the same. In terms of execution there are no clauses, just things like "hash join" and "filter" and "sort" -- or whatever the database implements for the DAG.

You are confusing execution with compilation and probably you just care about scoping rules.

So, to start with SQL has a set of clauses and these are in a very specified order. Your question contains this ordering -- at least for a database that supports those clauses.

The second part is the ordering for identifying identifiers. Basically, this comes down to:

• Table aliases are defined in the FROM clause. So this can be considered as "first" for scoping purposes.
• Column aliases are defined in the SELECT clause. By the SQL Standard, column aliases can be used in the ORDER BY. Many databases extend this to the QUALIFY (if supported), HAVING, and GROUP BY clauses. In general, databases do not support them in the WHERE clause.
• If two tables in the FROM have the same column name, then the column has to be qualified to identify the table. The one exception to this is when the column is a key in a JOIN and the USING clause is used. Then the unqualified column name is fine.
• If a column alias defined in the SELECT conflicts with a table alias in a clause that supports column aliases, then it is up to the database which to choose.

The whole point of SQL is that it is a 'whole set' language and there is no particular set order to much of it. Today's DBMS evaluates each Select query as a whole to determine the best, most efficient way to assemble the data set results, in much the same way that Google Maps might determine the best path to get you home based both on where you are and ambient traffic.

Databases will provide, under their Explain Plan command, exactly the sequence they will use to process your query. This called the Execution Plan. Each of these steps are performed on entire table sets and where possible under parallel processes. The steps in each plan do not have any of your names listed above, instead a step might say "perform an index scan on table A", or "perform a nested loops join on the prior partial result set and table B". In some cases they will filter records before joining and in other cases they won't, for example.

Within those parameters there are some tasks that always come before others. For example, all Where clause filtering takes place before aggregation and summary filtering (Having clause). But there are few absolute rules here.

When writing SQL, I found that the execution order of the select statement is not the same as the order of writing.

The order in which SQL query statements are written is

SELECT

FROM

WHERE

GROUP BY

HAVING

UNION

ORDER BY

But in fact the order of execution of the SQL statement is

FROM

WHERE

GROUP BY

HAVING

SELECT

UNION

ORDER BY

1. SQL will first choose where my table is selected, including the table's restrictions, (such as connection mode JOIN and restrictions ON)

2. SQL will choose what my judgment condition is, that is, the problem of WHERE

3. Then it will group by grouping and execute the HAVING statement.

4. SELECT statement is executed after most of the statements are executed, so we must understand that the statement executed in front of it will affect it, and pay attention to the actual work. This is especially important.

5. With the execution order of the statement we can find that order by the last execution, so we can sort the new fields named in select.