oracle join及where 的执行次序

oracle 的各种join及where的执行次序  

 

内联接：

select * from a inner join b on a.aa=b.bb;

等价于 select * from a,b where a.aa=b.bb; 即内联接相当于一般的等值连接。

外连接：

分left/right/full outer join等几种。

貌似outer可省略，即left join，right join，full join等三种。

左联接：

select * from a left join b on a.aa=b.bb;

等价于另一种写法：

select * from a,b where a.aa=b.bb(+);

若左边的表a有5条，则结果将显示5条。

右联接：

select * from a right join b on a.aa=b.bb;

等价于另一种写法：

select * from a,b where a.aa(+)=b.bb;

若表a有5条而表b有3条，则同右边的表b条数一样，只显示3条。

全联接：

select * from a full join b on a.aa=b.bb;

将展示所有a和b对应列相等及a中和b中相等之外的行。

自联接：

self join

自联接并非一个sql关键词。

而是用类似于select * from aa a,aa b的形式实现的。主要用于对同一个表中的两列进行联接的特殊情况。

例子：

select * from a1;

    AA BB
1 1 1
2 2 2
3 3 3
4 4 4
5 5 5

select * From a2;

    CC DD
1 2 2
2 3 3
3 5 5
4 9 9
内联接结果:

   select * from a1 a,a2 b where a.aa=b.cc;

等价于：

   select * from a1 a inner join a2 b on a.aa=b.cc;

    AA BB CC DD
1 2 2 2 2
2 3 3 3 3
3 5 5 5 5
左外连接结果：

   select * from a1 a left join a2 b on a.aa=b.cc;

等价于：

   select * from a1 a , a2 b where  a.aa=b.cc(+);
    AA BB CC DD
1 2 2 2 2
2 3 3 3 3
3 5 5 5 5
4 1 1  
5 4 4  
右连接结果：

   select * from a1 a right join a2 b on a.aa=b.cc;

等价于：

      select * from a1 a , a2 b where  a.aa(+)=b.cc;

    AA BB CC DD
1 2 2 2 2
2 3 3 3 3
3 5 5 5 5
4       9 9

全连接结果 ：

   select * from a1 a full join a2 b on a.aa=b.cc;

等价于先左连接再右连接然后联合：

    select * from a1 a , a2 b where  a.aa(+)=b.cc union select * from a1 a,a2 b where a.aa=b.cc(+);

    AA BB CC DD
1 1 1  
2 2 2 2 2
3 3 3 3 3
4 4 4  
5 5 5 5 5
6       9 9

。

注意（+）的方式是oracle专用语法，不建议使用，原因如下：

建议你用在From语句后用Outer Join语法 而不是Oracle的Join操作符(+).而且(+)是要受下面的规则限制的，但Outer Join语法则不受的～～

　　1）你不可以在查询块中使用(+) 当它同时包含 join的from语句中

　　2）(+)只是在where语句中,并且只能对应一个表或视图的一行字段

　　3）如果A和B做联接时有多个条件，那么(+)必须完善所有的匹配条件，

　　如果没有 ,oracle不会警告你～只是结果自然不同的

　　4）不可以在作出一个表外查询 另张表内查询的(+)联接操作～～

　　5）不可以用（+）外联接到自己 当然Self Join是可以的

　　6）含（+）的Where后的注意

　　OR不可用

　　IN不可用

　　子查询不可用

备注， sql语句执行次序的资料，有点儿象是机器翻译的，不清楚是不是适用于oracle，但MSDN中sql 2008的执行次序与其类似。
      SQL说话不合于其他编程说话的最明显特点是处理惩罚代码的次序。在大多半据库说话中，代码按编码次序被处理惩罚。但在SQL语句中，第一个被处理惩罚的子句是FROM，而不是第一呈现的SELECT。SQL查询处理惩罚的步调序号： 
（8） SELECT （9） DISTINCT （11） <TOP_specification> <_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、 FROM：对FROM子句中的前两个表履行笛卡尔积（交叉联接），生成虚拟表VT1。表名履行次序是从后往前，所以数据较少的表尽量放后。 
　　2、 ON：对VT1应用ON筛选器，只有那些使为真才被插入到TV2。 
　　3、 OUTER （JOIN）:若是指定了OUTER JOIN（相对于CROSS JOIN或INNER JOIN），保存表中未找到匹配的行将作为外部行添加到VT2，生成TV3。若是FROM子句包含两个以上的表，则对上一个联接生成的成果表和下一个表反复履行步调1到步调3，直处处理惩罚完所有的表地位。 
　　4、 WHERE：对TV3应用WHERE筛选器，只有使为true的行才插入TV4。履行次序为畴前去后或者说从左到右。 
　　5、 GROUP BY：按GROUP BY子句中的列列表对TV4中的行进行分组，生成TV5。履行次序从左往右分组。 
　　6、 CUTE|ROLLUP：把超组插入VT5，生成VT6。 
　　7、 HAVING：对VT6应用HAVING筛选器，只有使为true的组插入到VT7。Having语句很耗资料，尽量罕用　　    8、 SELECT：处理惩罚SELECT列表，产生VT8。 
　　9、 DISTINCT：将反复的行从VT8中删除，产品VT9。 
　　10、ORDER BY：将VT9中的行按ORDER BY子句中的列列表次序，生成一个游标（VC10）。履行次序从左到右，是一个很耗资料的语句。 
       11、TOP：从VC10的开端处选择指定命量或比例的行，生成表TV11，并返回给调用者。

http://msdn.microsoft.com/en-us/library/ms189499(v=SQL.100).aspx

Processing Order of the SELECT statement

The following steps show the processing order for a SELECT statement.

 

1.FROM

 

2.ON

 

3.JOIN

 

4.WHERE

 

5.GROUP BY

 

6.WITH CUBE or WITH ROLLUP

 

7.HAVING

 

8.SELECT

 

9.DISTINCT

 

10.ORDER BY

 

11.TOP

 

 

 

也就是说, 先进行on的过滤, 而后才进行join, 这样就避免了两个大表产生全部数据的笛卡尔积的庞大数据.

经测试，

1 oracle中，先on 后join可以确认。即数据库处理是根据on的条件组合来产生最终join的结果的。

2 on在前，where在后可确认，若先where ,则应不会影响join的结果。而只有先on 并join后，产生结果然后再过滤，才会产生where的效果。亦测试过，但其它有无更复杂的情况就不得而知了。

所以需在使用中注意！注意！