Order of execution of SQL statements

SQL language is a very simple language, write SQL as long as you understand its execution logic can write the correct SQL statement!

The most obvious difference between SQL statements and other programming languages is the order in which code is executed, most programming languages, where code is processed in coded order, but the order in which the SQL statements are executed is different.

Example:

1 ? SELECT [DISTINCT] 2 ?  from 3 ? WHERE 4 ? GROUP  by 5 ?  having 6 ? UNION 7 ? ORDER  by

The above example does not list all of the SQL syntax structures, but suffice to note that the syntax order and execution order of SQL are completely different, and the above statements are executed in the following order:

1 ?  from 2 ? WHERE 3 ? GROUP  by 4 ?  having 5 ? SELECT 6 ? DISTINCT 7 ? UNION 8 ? ORDER  by

The order of execution of SQL statements is notable:

1.from is the first step in SQL statement execution, and the first step in the database execution SQL statement is to load the data from the hard disk into a data buffer for easy manipulation of the data.

2.select is executed after most statements are executed, strictly after the From and group by, and this is the reason why you cannot use the field that sets the alias in select in the Where to determine the condition.

1 SELECT +  as Z 2  from A 3 WHERE = Ten -- Z is not available here, because select is the last executed statement! 

If you want to use alias Z, there are two choices. or write again the expression Z represents:

1 SELECT +  as Z 2  from A 3 WHERE + = Ten

or ask for derivative tables, generic data expressions, or views to avoid alias reuse.

3. In both syntax and execution order, the Union is always ranked before order by. Many people think that each UNION segment can use ORDER by ordering, but this is not true according to the SQL language standard and the differences in the execution of each database SQL. Although some databases allow for the ordering of SQL statement subqueries (subqueries) or derived tables (derived tables), this does not mean that the sort order will remain sorted after the UNION operation.

Note: Not all databases use the same parsing method for SQL statements. such as MySQL, PostgreSQL, and SQLite will not be executed as described in the 2nd above.

Summarize it in a few words:

The full execution order of the SQL SELECT statement:
1. The FROM clause assembles data from different data sources;
2. The WHERE clause filters the record rows based on the specified criteria;
3. The GROUP BY clause divides the data into multiple groupings;
4, using the aggregation function to calculate;
5, using having clauses to filter the grouping;
6, the calculation of all expressions;
7. Use order by to sort the result set.
8, select Set output.

Order of execution of SQL statements