Left Join 比Join快的情况浅析_left join比join快

从原理上来讲Join在逻辑运算上比Left Join会少但有些业务情况下为什么反而会慢?
如下Sql语句：

select sonACD.ID 
    from 
        MN_ACD_DETAIL as sonACD 
    left join 
        MN_ACD_DETAIL as inbound 
            on sonACD.ANI=inbound.ANI 
    left join 
        call_detail_record as cdr 
            on sonACD.UCID=cdr.ucid 
    where 
        sonACD.ANSWER_TIME!=0 
    group by 
        sonACD.ID 

select sonACD.ID 
    from 
        MN_ACD_DETAIL as sonACD 
    inner join 
        MN_ACD_DETAIL as inbound 
            on sonACD.ANI=inbound.ANI 
    inner join 
        call_detail_record as cdr 
            on sonACD.UCID=cdr.ucid 
    where 
        sonACD.ANSWER_TIME!=0 
    group by 
        sonACD.ID 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

在数据库跑时发现前者明显比后者快
查询执行计划发现，left join时只打描MN_ACD_DETAIL一次，而inner join时MN_ACD_DETAIL扫描两次，call_detail_record 一次！而原因也就在这。
通过查询开销可以发现前者需要合并联接两次而后者在合并联接上打开销为0%，后者所有的开销都用在聚集索引扫描上，前者先查出了sonACD 与inbound 联接的结果再与call_detail_record 联接返回，
而left join必定有数据库对其优化，以sonACD 为主同时索引inbound 与cdr 查到符合条件立即返回。
这就是Left Join 有时比Join快的原因