Skip to Content

MS SQL ServerSQL的执行顺序是固定的. join 是在where前面执行的

(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>

但在HANA的目前版本中(1.0.33wherejoin的执行顺序不是固定的,有下列三种情况

  1. 1. wherejoin的前面

where的后面条件跟等号的时候,会先做where后面的过滤,然后再做join

select * from employee  e left join course c on e.empid=c.studentid

where  mgrid =7

/wp-content/uploads/2012/07/1_119909.png

  1. 2. where join的后面

where的条件是判断是否为空的时候,会先做join,再where过滤

select * from employee e left join course c on e.empid=c.studentid where score is null

/wp-content/uploads/2012/07/2_119911.png

  1. 3. where joinwhere

where的条件有等号也有判断是否为空的时候,会先做where的等号过滤,再join。再做判断是否为空的过滤

select * from employee e left join course c on e.empid=c.studentid where mgrid=7 and score is null

/wp-content/uploads/2012/07/3_119912.png

这种机制让HANA在表join的时候会提高性能,但如果不清楚这些执行顺序,会让你的sql结果不是你所期望的结果。

由于HANA目前一直在开发中,新release的版本可能顺序就不是这样的,所以需要使用这样的SQL语句之前最好用执行计划查看下

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

Leave a Reply