一、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
---------------------------------------------------------------------