Oracle的left join中on和where的区别以及与(+)的区别

自从用了oracle数据库之后，基本上都是用(+)的方式来替代了left join,前几日执行一个语句发现有问题，仔细检查发现oracle的连接用法left join与 (+)的方式还是有点区别的。刚好看到了 left join的文章，遂引用如下：

外关联是Oracle数据库的专有语句
Left Outer Join则是SQL-92的标准语句
通常认为这两个SQL是可以等效的，但还是有些细微的差别。
一般说来，外关联的等值条件等效于Left Outer Join中的on语句；两个where中其他语句是一样的。
但是Left Outer Join中的其他条件（非表连接）出现在On关键词后面还是出现在Where关键词后面是不一样的，这样的语句很难用外关联进行同义转义。

下面我们先看一个测试数据，和一个测试案例
创建一个部门表和员工表

CREATE TABLE departments ( depID   NUMBER(38,0), depName VARCHAR2(20), delFlag NUMBER(1,0) ); CREATE TABLE employees ( empID   NUMBER(38,0), empName VARCHAR2(20), depID   NUMBER(38,0), delFlag NUMBER(1,0) ); INSERT INTO departments VALUES(1,'Finacle',0); INSERT INTO departments VALUES(2,'Marketing',0); INSERT INTO departments VALUES(3,'HR',1); INSERT INTO departments VALUES(4,'IT',0); INSERT INTO employees VALUES(1,'wbq',1,0); INSERT INTO employees VALUES(2,'czh',2,0); INSERT INTO employees VALUES(3,'chh',1,0); INSERT INTO employees VALUES(4,'wal',2,0); INSERT INTO employees VALUES(5,'ddd',3,0); COMMIT;

以下为测试例子

--列出部门ID为3的部门和员工信息，不管该部门是否有员工 SELECT d.depID,d.depName,e.empName   FROM departments d   LEFT OUTER JOIN employees e     ON d.depID = e.depID WHERE d.depID =3 ORDER BY d.depID; --和上面等效 SELECT d.depID,d.depName,e.empName   FROM departments d, employees e WHERE d.depID = e.depID(+)    AND d.depID =3 ORDER BY d.depID; --该例子可能不是很恰当，d.depID=3表示首先进行全左连接，然后查找depID=3的纪录，如果该部门中找不到对应的员工，则该部门员工则为NULL，同时都显示部门信息。 SELECT d.depID,d.depName,e.empName d.delflag   FROM departments d   LEFT OUTER JOIN employees e     ON d.depID = e.depID AND e.depID=3 ORDER BY d.depID; --和上面不等价，差别在于能够找到无对应员工的部门和有对应员工的部门，却找不到该部门无员工的部门。 SELECT d.depID,d.depName,e.empName,d.delflag   FROM departments d, employees e WHERE d.depID = e.depID(+)    AND (e.depID=3 or e.depID is NULL) ORDER BY d.depID;

 

而对于left join 中where 与and的区别如下：

数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。

      在使用left jion时，on和where条件的区别如下：

1、 on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。

2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

       假设有两张表：

表1 tab1：

id size

1 10

2 20

3 30

表2 tab2：

size name

10 AAA

20 BBB

20 CCC

两条SQL:
1、select * form tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name=’AAA’
2、select * form tab1 left join tab2 on (tab1.size = tab2.size and tab2.name=’AAA’)

第一条SQL的过程：

1、中间表
on条件:
tab1.size = tab2.size

tab1.id    tab1.size    tab2.size     tab2.name

1               10                   10               AAA

2              20                     20             BBB

2             20                      20               CCC

3             30                    (null)              (null)

2、再对中间表过滤
where 条件：
tab2.name=’AAA’

tab1.id       tab1.size        tab2.size     tab2.name

1                  10                  10              AAA

第二条SQL的过程：

1、中间表
on条件:
tab1.size = tab2.size and tab2.name=’AAA’
(条件不为真也会返回左表中的记录)

tab1.id      tab1.size         tab2.size       tab2.name

1               10                     10                   AAA

2               20                   (null)               (null)

3               30                    (null)                 (null)

     其实以上结果的关键原因就是left join,right join,full join的特殊性，不管on上的条件是否为真都会返回left或right表中的记录，full则具有left和right的特性的并集。 而inner jion没这个特殊性，则条件放在on中和where中，返回的结果集是相同的。