oracle中外关联的解惑
一、left join的on条件：
1、简单的left join（right join同理）。
01
SQL> select * from p1;
02 www.2cto.com
03
A B C
04
-------------------- -------------------- --------------------
05
1 1 1
06
2 2 2
07
3 3 3
08
4 s d
09
5 f f
10
6 s
11
7 d
12
8 d
13
14
已选择8行。
15
16
SQL> select * from p3;
17
18
A3 B3 C3
19
-------------------- -------------------- --------------------
20
1 1 1
21
2 2 2
22
3 3 3
23
4
24
5 d d
25
26
SQL> select * from p1 left join p3 on p1.a=p3.a3;
27 www.2cto.com
28
A B C A3 B3 C3
29
----- ----- ----- ----- ----- -----
30
1 1 1 1 1 1
31
2 2 2 2 2 2
32
3 3 3 3 3 3
33
4 s d 4
34
5 f f 5 d d
35
6 s
36
7 d
37
8 d
38
39
已选择8行。
40
41
42
执行计划
43
----------------------------------------------------------
44
Plan hash value: 3306833788
45
46
---------------------------------------------------------------------------
47
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
48
---------------------------------------------------------------------------
49
| 0 | SELECT STATEMENT | | 8 | 336 | 6 (17)| 00:00:01 |
50
|* 1 | HASH JOIN OUTER | | 8 | 336 | 6 (17)| 00:00:01 |
51
| 2 | TABLE ACCESS FULL| P1 | 8 | 168 | 2 (0)| 00:00:01 |
52
| 3 | TABLE ACCESS FULL| P3 | 5 | 105 | 3 (0)| 00:00:01 |
53 www.2cto.com
---------------------------------------------------------------------------
54
55
Predicate Information (identified by operation id):
56
---------------------------------------------------
57
58
1 - access("P1"."A"="P3"."A3"(+))
2、如果在on中写主表的过滤条件，会怎么样呢？
01
SQL> select * from p1 left join p3 on p1.a=p3.a3 and p1.b='s';
02
03
A B C A3 B3 C3
04
----- ----- ----- ----- ----- -----
05
1 1 1
06
2 2 2
07
3 3 3
08
4 s d 4
09
5 f f
10
6 s
11
7 d
12
8 d
13
14
已选择8行。
15
16
17
执行计划
18
----------------------------------------------------------
19 www.2cto.com
Plan hash value: 2614770449
20
21
----------------------------------------------------------------------------
22
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
23
----------------------------------------------------------------------------
24
| 0 | SELECT STATEMENT | | 8 | 336 | 26 (0)| 00:00:01 |
25
| 1 | NESTED LOOPS OUTER | | 8 | 336 | 26 (0)| 00:00:01 |
26
| 2 | TABLE ACCESS FULL | P1 | 8 | 168 | 2 (0)| 00:00:01 |
27
| 3 | VIEW | | 1 | 21 | 3 (0)| 00:00:01 |
28
|* 4 | TABLE ACCESS FULL| P3 | 1 | 21 | 3 (0)| 00:00:01 |
29
----------------------------------------------------------------------------
30
31
Predicate Information (identified by operation id):
32
---------------------------------------------------
33
34
4 - filter("P1"."A"="P3"."A3" AND "P1"."B"='s')
结果不是我们以为会对主表做过滤，而是对子表的过滤，含义是：外关联的时候，除了"P1"."A"="P3"."A3"的条件外，还要判断关联的主表记录"P1"."B"='s'是否成立，如果成立则关联子表记录，否则添加空记录。而且从hash外关联变成了nested loops外关联。
3、那么，如果在on中写子表的过滤条件，又会怎么样呢？
01
SQL> select * from p1 left join p3 on p1.a=p3.a3 and p3.b3='2';
02
03
A B C A3 B3 C3
04
----- ----- ----- ----- ----- -----
05
2 2 2 2 2 2
06
1 1 1
07
6 s
08
3 3 3
09
5 f f
10
4 s d
11
7 d
12
8 d
13
14
已选择8行。
15
16
17
执行计划
18
----------------------------------------------------------
19
Plan hash value: 3306833788
20
21
---------------------------------------------------------------------------
22
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
23
---------------------------------------------------------------------------
24
| 0 | SELECT STATEMENT | | 8 | 336 | 6 (17)| 00:00:01 |
25
|* 1 | HASH JOIN OUTER | | 8 | 336 | 6 (17)| 00:00:01 |
26
| 2 | TABLE ACCESS FULL| P1 | 8 | 168 | 2 (0)| 00:00:01 |
27
|* 3 | TABLE ACCESS FULL| P3 | 1 | 21 | 3 (0)| 00:00:01 |
28
---------------------------------------------------------------------------
29
30
Predicate Information (identified by operation id):
31
---------------------------------------------------
32
33
1 - access("P1"."A"="P3"."A3"(+))
34
3 - filter("P3"."B3"(+)='2')
这个结果才是我们想要的，是先对子表做了过滤，然后在做hash外关联。
结论：on中的所有条件只对子表过滤和关联，不影响主表的记录。
二、left join的where条件：
1、如果在where中写主表的过滤条件。
01
SQL> select * from p1 left join p3 on p1.a=p3.a3 where p1.b='s';
02 www.2cto.com
03
A B C A3 B3 C3
04
----- ----- ----- ----- ----- -----
05
4 s d 4
06
6 s
07
08
09
执行计划
10
----------------------------------------------------------
11
Plan hash value: 3306833788
12
13
---------------------------------------------------------------------------
14
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
15
---------------------------------------------------------------------------
16
| 0 | SELECT STATEMENT | | 2 | 84 | 6 (17)| 00:00:01 |
17
|* 1 | HASH JOIN OUTER | | 2 | 84 | 6 (17)| 00:00:01 |
18
|* 2 | TABLE ACCESS FULL| P1 | 2 | 42 | 2 (0)| 00:00:01 |
19
| 3 | TABLE ACCESS FULL| P3 | 5 | 105 | 3 (0)| 00:00:01 |
20
---------------------------------------------------------------------------
21
22
Predicate Information (identified by operation id):
23
---------------------------------------------------
24
25
1 - access("P1"."A"="P3"."A3"(+))
26
2 - filter("P1"."B"='s')
先过滤主表，然后在hash外关联，和预想的一样。
2、在where条件中过滤子表。
01
SQL> select * from p1 left join p3 on p1.a=p3.a3 where p3.b3='2';
02
03
A B C A3 B3 C3
04
----- ----- ----- ----- ----- -----
05
2 2 2 2 2 2
06
07
08
执行计划
09
----------------------------------------------------------
10
Plan hash value: 195079442
11
12
---------------------------------------------------------------------------
13
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
14
---------------------------------------------------------------------------
15
| 0 | SELECT STATEMENT | | 1 | 42 | 6 (17)| 00:00:01 |
16
|* 1 | HASH JOIN | | 1 | 42 | 6 (17)| 00:00:01 |
17
|* 2 | TABLE ACCESS FULL| P3 | 1 | 21 | 3 (0)| 00:00:01 |
18
| 3 | TABLE ACCESS FULL| P1 | 8 | 168 | 2 (0)| 00:00:01 |
19
---------------------------------------------------------------------------
20
21
Predicate Information (identified by operation id):
22
---------------------------------------------------
23
24
1 - access("P1"."A"="P3"."A3")
25
2 - filter("P3"."B3"='2')
出乎预料了，虽然过滤了子表，但是变成了普通的hash关联。这是为什么呢？猜测是：外关联一般是先关联后过滤的，但是为了效率，简化处理，把普通过滤提前。主表过滤，记录中子表有可能为空，所以保留外关联；子表过滤，那么子表对应的主表一定是普通关联上的记录，所以简化为普通关联。根据这个思路，那么is null的过滤条件应该会出现在外关联以后，看例子：
01
SQL> select * from p1 left join p3 on p1.a=p3.a3 where p3.b3 is null;
02 www.2cto.com
03
A B C A3 B3 C3
04
----- ----- ----- ----- ----- -----
05
4 s d 4
06
6 s
07
7 d
08
8 d
09
10
11
执行计划
12
----------------------------------------------------------
13
Plan hash value: 2883136784
14
15
----------------------------------------------------------------------------
16
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
17
----------------------------------------------------------------------------
18
| 0 | SELECT STATEMENT | | 8 | 336 | 6 (17)| 00:00:01 |
19
|* 1 | FILTER | | | | | |
20
|* 2 | HASH JOIN OUTER | | 8 | 336 | 6 (17)| 00:00:01 |
21
| 3 | TABLE ACCESS FULL| P1 | 8 | 168 | 2 (0)| 00:00:01 |
22
| 4 | TABLE ACCESS FULL| P3 | 5 | 105 | 3 (0)| 00:00:01 |
23
----------------------------------------------------------------------------
24
25
Predicate Information (identified by operation id):
26
---------------------------------------------------
27
28
1 - filter("P3"."B3" IS NULL)
29
2 - access("P1"."A"="P3"."A3"(+))
果然和预想的一样，先外关联，再判断is null的条件。
3、在where条件中添加关联条件，结果将会如何？
01
SQL> select * from p1 left join p3 on p1.a=p3.a3 where p1.b=p3.b3;
02
03
A B C A3 B3 C3
04
----- ----- ----- ----- ----- -----
05
1 1 1 1 1 1
06
2 2 2 2 2 2
07
3 3 3 3 3 3
08
09
10
执行计划
11
----------------------------------------------------------
12
Plan hash value: 195079442
13 www.2cto.com
14
---------------------------------------------------------------------------
15
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
16
---------------------------------------------------------------------------
17
| 0 | SELECT STATEMENT | | 1 | 42 | 6 (17)| 00:00:01 |
18
|* 1 | HASH JOIN | | 1 | 42 | 6 (17)| 00:00:01 |
19
| 2 | TABLE ACCESS FULL| P3 | 5 | 105 | 3 (0)| 00:00:01 |
20
| 3 | TABLE ACCESS FULL| P1 | 8 | 168 | 2 (0)| 00:00:01 |
21
---------------------------------------------------------------------------
22
23
Predicate Information (identified by operation id):
24
---------------------------------------------------
25
26
1 - access("P1"."B"="P3"."B3" AND "P1"."A"="P3"."A3")
结果变成了普通关联，因为如果添加空行，后面普通关联的条件一定不成立，null不等于任何值。而且where的关联条件不能再使用"+"做外关联，两种书写方式不能同时使用。
结论：where条件的所有过滤可以认为是外关联后的过滤，如果有关联条件则会降级为普通关联。
三、"+"形式的外关联：
1、简单关外关联。
01
SQL> select * from p1,p3 where a=a3(+);
02
03
A B C A3 B3 C3
04
----- ----- ----- ----- ----- -----
05
1 1 1 1 1 1
06
2 2 2 2 2 2
07
3 3 3 3 3 3
08
4 s d 4
09
5 f f 5 d d
10
6 s
11
7 d
12
8 d
13
14
已选择8行。
15
16
17
执行计划
18
----------------------------------------------------------
19 www.2cto.com
Plan hash value: 3306833788
20
21
---------------------------------------------------------------------------
22
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
23
---------------------------------------------------------------------------
24
| 0 | SELECT STATEMENT | | 8 | 336 | 6 (17)| 00:00:01 |
25
|* 1 | HASH JOIN OUTER | | 8 | 336 | 6 (17)| 00:00:01 |
26
| 2 | TABLE ACCESS FULL| P1 | 8 | 168 | 2 (0)| 00:00:01 |
27
| 3 | TABLE ACCESS FULL| P3 | 5 | 105 | 3 (0)| 00:00:01 |
28
---------------------------------------------------------------------------
29
30
Predicate Information (identified by operation id):
31
---------------------------------------------------
32
33
1 - access("A"="A3"(+))
2、主表过滤条件。
01 www.2cto.com
SQL> select * from p1,p3 where a=a3(+) and b='s';
02
03
A B C A3 B3 C3
04
----- ----- ----- ----- ----- -----
05
4 s d 4
06
6 s
07
08
09
执行计划
10
----------------------------------------------------------
11
Plan hash value: 3306833788
12
13
---------------------------------------------------------------------------
14
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
15
---------------------------------------------------------------------------
16
| 0 | SELECT STATEMENT | | 2 | 84 | 6 (17)| 00:00:01 |
17
|* 1 | HASH JOIN OUTER | | 2 | 84 | 6 (17)| 00:00:01 |
18
|* 2 | TABLE ACCESS FULL| P1 | 2 | 42 | 2 (0)| 00:00:01 |
19
| 3 | TABLE ACCESS FULL| P3 | 5 | 105 | 3 (0)| 00:00:01 |
20
---------------------------------------------------------------------------
21
22
Predicate Information (identified by operation id):
23 www.2cto.com
---------------------------------------------------
24
25
1 - access("A"="A3"(+))
26
2 - filter("B"='s')
和left join在where条件中加主表过滤结果一样。
3、子表加过滤条件。
01
SQL> select * from p1,p3 where a=a3(+) and b3 is null;
02
03
A B C A3 B3 C3
04
----- ----- ----- ----- ----- -----
05
4 s d 4
06
6 s
07
7 d
08
8 d
09
10
11
执行计划
12
----------------------------------------------------------
13
Plan hash value: 2883136784
14 www.2cto.com
15
----------------------------------------------------------------------------
16
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
17
----------------------------------------------------------------------------
18
| 0 | SELECT STATEMENT | | 8 | 336 | 6 (17)| 00:00:01 |
19
|* 1 | FILTER | | | | | |
20
|* 2 | HASH JOIN OUTER | | 8 | 336 | 6 (17)| 00:00:01 |
21
| 3 | TABLE ACCESS FULL| P1 | 8 | 168 | 2 (0)| 00:00:01 |
22
| 4 | TABLE ACCESS FULL| P3 | 5 | 105 | 3 (0)| 00:00:01 |
23
----------------------------------------------------------------------------
24
25
Predicate Information (identified by operation id):
26
---------------------------------------------------
27
28
1 - filter("B3" IS NULL)
29
2 - access("A"="A3"(+))
30
31
SQL> select * from p1,p3 where a=a3(+) and b3 ='2';
32 www.2cto.com
33
A B C A3 B3 C3
34
----- ----- ----- ----- ----- -----
35
2 2 2 2 2 2
36
37
38
执行计划
39
----------------------------------------------------------
40
Plan hash value: 195079442
41
42
---------------------------------------------------------------------------
43
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
44
---------------------------------------------------------------------------
45
| 0 | SELECT STATEMENT | | 1 | 42 | 6 (17)| 00:00:01 |
46
|* 1 | HASH JOIN | | 1 | 42 | 6 (17)| 00:00:01 |
47
|* 2 | TABLE ACCESS FULL| P3 | 1 | 21 | 3 (0)| 00:00:01 |
48
| 3 | TABLE ACCESS FULL| P1 | 8 | 168 | 2 (0)| 00:00:01 |
49
---------------------------------------------------------------------------
50
51
Predicate Information (identified by operation id):
52
---------------------------------------------------
53
54
1 - access("A"="A3")
55
2 - filter("B3"='2')
和left join在where条件中加子表过滤结果一样。
4、那么，和left join 在on中过滤子表一样的是什么？
01
SQL> select * from p1,p3 where a=a3(+) and b3(+) ='2';
02 www.2cto.com
03
A B C A3 B3 C3
04
----- ----- ----- ----- ----- -----
05
2 2 2 2 2 2
06
1 1 1
07
6 s
08
3 3 3
09
5 f f
10
4 s d
11
7 d
12
8 d
13
14
已选择8行。
15
16
17
执行计划
18
----------------------------------------------------------
19
Plan hash value: 3306833788
20
21
---------------------------------------------------------------------------
22
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
23
---------------------------------------------------------------------------
24
| 0 | SELECT STATEMENT | | 8 | 336 | 6 (17)| 00:00:01 |
25
|* 1 | HASH JOIN OUTER | | 8 | 336 | 6 (17)| 00:00:01 |
26
| 2 | TABLE ACCESS FULL| P1 | 8 | 168 | 2 (0)| 00:00:01 |
27
|* 3 | TABLE ACCESS FULL| P3 | 1 | 21 | 3 (0)| 00:00:01 |
28
---------------------------------------------------------------------------
29
30
Predicate Information (identified by operation id):
31
---------------------------------------------------
32
33
1 - access("A"="A3"(+))
34
3 - filter("B3"(+)='2')
就是它了，那和left join在on过滤主表一样的呢？我也不知道。
结论：oracle的"+"外关联，写法简单，但是，简单是有代价的，除了不能实现left join在on过滤主表的效果外，oracle对"+"外关联作了N多限制：
1、不能和join一起使用；
2、只能用在where子句或table子句中；
3、如果有多个关联条件，必须每个多使用"+"，否则会当做普通关联，且没有提示；
4、如果你外关联的子表，又和另一个表做普通关联，那么也会变成普通关联；
5、不能单表自外关联，虽然自关联是可以的，可以以别名的方式，实现两张相同的表外关联；
6、"+"只能用于一个列名，不能是表达式，但是表达式中的列名可以用"+"； www.2cto.com
7、不能和or同时使用；
8、不能和in同时使用；
9、不能将一个标有"+"的列和一个子查询比较。
最后说明：外关联子表先过滤is null的写法：sub_table.column1 (+) is null 。
作者 hulubo
oracle中外关联的解惑 
