Execution sequence of each SQL statement clause

An SQL statement contains many clauses. When writing SQL statements, it is very important to understand the priority of executing SQL clauses. Otherwise, a simple SQL statement may become multiple nested SQL statements, it is more likely to cause incorrect return results.

The following example illustrates the priority of each clause:

 

Select a, B, count (sum (c) over ()
From t
Where T. D = 1
Start with E is not null connect by prior F = G
Group by a, B
Having sum (c)> 100
Order by 3;

This SQL includes:
1. Select clause
2. Group by clause
3. analysis functions
4. From clause
5. Where clause
6. Start with... connect by clause
7. Having clause
8. Order by clause

For the from clause, it can be simply understood as a table or scan (if multi-Table Association is involved, it will be complicated and will not be discussed here );
For the select clause, the execution plan is executed at the end. I understand it as returning data to the user rather than reading data. Reading data is performed at the from step, so I think the order is:

4-> 6-> 5-> 2-> 7-> 3-> 8-> 1

From clause-> start with... connect by clause-> where clause-> group by clause-> having clause-> analysis function-> order by clause-> select clause

Note that the above sequence only indicates the general situation. Not all situations follow the execution sequence, especially when group by and order by are both present. For example:

Select a, B, count (sum (c) over ()
From t
Where T. D = 1
Start with E is not null connect by prior F = G
Group by a, B
Having sum (c)> 100
Order by;

The execution sequence of this SQL statement may be 4-> 6-> 5-> 2 + 8-> 7-> 3-> 1. The actual execution order is based on the execution plan. However, the priority of the following parts is determined regardless of the SQL statement:

Start with... connect by clause-> where clause-> group by clause-> having clause-> Analysis Function