执行顺序 :from > on > where > group by > having > select > distinct > order by > top
一个完整的Sql语句样例如下:
(8)SELECT (9)DISTINCT (11)Top <num> <select_list>
(1)FROM [left_table]
(3)<join_type> JOIN <right_table>
(2)ON <join_condition>
(4)WHERE <where_condition>
(5)GROUP BY <group_by_list>
(6)WITH <CUBE | RollUP>
(7)HAVING <having_condition>
(10)ORDER BY <order_by_list>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
逻辑查询执行过程
- FROM:对FROM子句中的前两个表执行笛卡尔积运算(交叉连接),生成虚拟表temp1
- ON:对temp1应用ON筛选器,只有满足<join_condition>的行才能插入到temp2
- JOIN:左表与右表进行连接运算(内连接或外连接),所得结果保存到temp3
左外连接把左表标记为保留表,右外连接把右表标记为保留表,内连接则左、右表都不是保留表 - WHERE:对temp3应用WHERE筛选器,只有满足<where_condition>的行才能插入到temp4
- GROUP BY:根据<group_by_list>对temp4进行分组,生成temp5
- WITH:把超组(Suppergroups)插入temp5,生成temp6
- HAVING:对temp6应用HAVING筛选器,只有满足<having_condition>的行才能插入到temp7
- SELECT:根据temp7和<select_list>,生成temp8
- DISTINCT:将重复的行从temp8中剔除,生成temp9
- ORDER BY:根据<order_by_list>对temp9进行排序,生成temp10
- TOP:从temp10的第一行开始选择指定数量或比例的行,生成表temp11
以上内容参考自http://msdn.microsoft.com/en-us/library/ms189499(v=SQL.100).aspx和https://blog.csdn.net/superit401/article/details/52066042