当前位置:网站首页>Execution of SQL statement

Execution of SQL statement

2020-11-08 21:14:05 Let me love you

SQL, Script query language , The order in which the code is processed is not in the order of the scripting language , This is the most obvious feature different from other programming languages .

SQL Language is common, such as ,Mysql,HiveQL,Oracle etc. , Although there are some grammatical differences , But they're interpreting query scripts , In particular, the execution sequence of parsing statements has commonness . If you decompose a scripting language into a series of statements , So what is the order of execution of these statements ?

Select Statement execution order
select The execution order of the query statement , It can be seen that the first execution is FROM Clause , Finally, execute ORDER BY
Execution order :

(1) FROM
(2) ON  
(3) JOIN  
(4) WHERE  
(5) GROUP BY  
(6) WITH {CUBE | ROLLUP}  
(7) HAVING  
(8) SELECT   
(9) DISTINCT   
(10) ORDER BY  
(11) LIMIT  
7 SELECT ( Query content )
1 FROM <left table> ( Query the main table )
3 <join type> JOIN <right table> ( Associated table )
2 ON <join condition> ( The condition of two tables Association )
4 WHERE ( Conditions for filtering query results )
5 GROUP BY ( Grouping conditions )
6 HAVING ( Conditions for filtering groups )
8 ORDER BY ( Sorting conditions )
#  The preceding number is the order in which the actual program is executed ,
#  To develop a good code format , The empty line, the empty line , It should be capitalized ( Try to keep the words in uppercase for database operations , Keep the contents of library, table and column in lowercase as much as possible )

A brief introduction to the above logical sequence
Each of the above steps produces a virtual table , The virtual table is used as input for the next step . Only the table generated in the last step is returned to the caller .

  1. FROM: Yes FROM The first two tables in the clause perform the cartesian product ( Cross connection ), Generate virtual tables VT1, Select a relatively small table as the base table .
  2. ON: Yes VT1 application ON Filter , Only those that are true are inserted VT2.
  3. OUTER (JOIN): If you specify OUTER JOIN( be relative to CROSS JOIN or INNER JOIN), No matching rows were found in the reservation table to be added as external rows to VT2, Generate TV3.
  4. WHERE: Yes VT3 application WHERE Filter , Only the emissary true Rows of VT4.
  5. GROUP BY: Press GROUP BY Column pairs in clause VT4 Group the rows in , Generate VT5.
  6. CUTE|ROLLUP: Insert the supergroup into VT5, Generate VT6.
  7. HAVING: Yes VT6 application HAVING Filter , Only the emissary true The group inserted into VT7.
  8. SELECT: Handle SELECT list , produce VT8.
  9. DISTINCT: Repeat lines from VT8 Delete in , obtain VT9.
  10. ORDER BY: take VT9 The lines in the press ORDER BY Column list order in the clause , Generate a cursor (VC10).
  11. LIMIT(TOP): from VC10 Selects the rows at the beginning of , To generate table VT11, And return it to the caller .

版权声明
本文为[Let me love you]所创,转载请带上原文链接,感谢