oracle的left join，right join和full join的一点介绍(二)
ID NAME
---------- ----------
1 a
2 b
3 c
1 a
hr@ORCL> select * from b;
ID NAME
---------- ----------
1 a
2 b
4 d
--1:n用UNION不正确
SQL> SELECT a.ID,b.ID
2 FROM a,b WHERE a.ID=b.ID(+)
3 UNION
4 SELECT a.ID,b.ID
5 FROM a,b WHERE a.ID(+)=b.ID;
ID ID
---------- ----------
1 1
2 2
3
4
--正确解法有三。注意，在使用sql时，任何时候，任何地方，一定要考虑null！！！切记。
法一： www.2cto.com
SQL> SELECT a.ID,b.ID
2 FROM a,b WHERE a.ID=b.ID(+)
3 UNION ALL
4 SELECT a.ID,b.ID
5 FROM a,b WHERE a.ID(+)=b.ID AND a.ID IS NULL;
ID ID
---------- ----------
1 1
1 1
2 2
3
4
法二：
SQL> SELECT a.ID,b.ID
2 FROM a,b WHERE a.ID=b.ID(+) AND b.ID IS NULL
3 UNION ALL
4 SELECT a.ID,b.ID
5 FROM a,b WHERE a.ID(+)=b.ID;
ID ID
---------- ----------
3
1 1
1 1
2 2
4
法三：
SQL> SELECT a.ID,b.ID
2 FROM a FULL JOIN b
3 ON a.ID=b.ID;
ID ID
---------- ----------
1 1
1 1
2 2
3
4
--逗号和full join是不一样的。另外，full join须加上关键字on，才是完整的语句。
www.2cto.com
[sql]
hr@ORCL> select p.id,t.id from p,t where p.id=t.id;
ID ID
---------- ----------
1 1
3 3
hr@ORCL> select p.id,t.id from p full join t on p.id=t.id;
ID ID
---------- ----------
1 1
3 3
2
5
全外连接和union连接都可以实现相同结果。我们来看一下他们的执行计划。
[sql]
全外连接的执行计划：
hr@ORCL> select a.id,b.id from a full join b on a.id=b.id;
ID ID
---------- ----------
1 1
2 2
3
4
Execution Plan
----------------------------------------------------------
Plan hash value: 2192011130
www.2cto.com
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 104 | 13 (8)| 00:00:01 |
| 1 | VIEW | | 4 | 104 | 13 (8)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | HASH JOIN OUTER | | 3 | 312 | 7 (15)| 00:00:01 |
| 4 | TABLE ACCESS FULL| A | 3 | 195 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| B | 3 | 117 | 3 (0)| 00:00:01 |
|* 6 | HASH JOIN ANTI | | 1 | 26 | 7 (15)| 00:00:01 |
| 7 | TABLE ACCESS FULL| B | 3 | 39 | 3 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL| A | 3 | 39 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."ID"="B"."ID"(+))
6 - access("A"."ID"="B"."ID")
Note
-----
- dynamic sampling used for this statement
Statisti