DROP TABLE TEST1 PURGE;
DORP TABLE TEST2 PURGE;
-- 左表
CREATE TABLE LEFT_TAB AS
SELECT 'left_1' as name, '1' AS id FROM dual UNION ALL
SELECT 'left_2' as name, '2' AS id FROM dual UNION ALL
SELECT 'left_3' as name, '3' AS id FROM dual UNION ALL
SELECT 'left_4' as name, '4' AS id FROM dual;
-- 右表
CREATE TABLE RIGHT_TAB AS
SELECT 'right_3' as name, '3' AS id FROM dual UNION ALL
SELECT 'right_4' as name, '4' AS id FROM dual UNION ALL
SELECT 'right_5' as name, '5' AS id FROM dual UNION ALL
SELECT 'right_6' as name, '6' AS id FROM dual;
-- inner join 返回两个表中的交集，因此左表的1,2和右表的5,6没有显示
/* INNER JOIN = JOIN */
SQL> SELECT AS left_name, AS right_name FROM left_tab l INNER JOIN right_tab r ON = ;
LEFT_N RIGHT_N
------ -------
left_3 right_3
left_4 right_4
/* where 写法 */
SELECT AS left_name, AS right_name FROM left_tab l, right_tab r WHERE = ;
-- left join 左表返回的所有行，右表返回与左边匹配的行, 因此左表显示1,2,3,4，右表显示3,4
/* LEFT JOIN = LEFT OUTER JOIN */
SQL> SELECT AS left_name, AS right_name FROM left_tab l LEFT JOIN right_tab r ON = order by 1;
LEFT_N RIGHT_N
------ -------
left_1
left_2
left_3 right_3
left_4 right_4
/* where(+) 写法 */
SQL> SELECT AS left_name, AS right_name FROM left_tab l, right_tab r WHERE =(+) order by 1;
LEFT_N RIGHT_N
------ -------
left_1
left_2
left_3 right_3
left_4 right_4
/* 使用left join和right join的时候需要注意驱动表的选择 */
SQL> SELECT /*+ leading(r,l) */ AS left_name, AS right_name FROM left_tab l, right_tab r WHERE =(+) order by 1;
Execution Plan
----------------------------------------------------------
Plan hash value: 1232751584
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 92 | 8 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 4 | 92 | 8 (25)| 00:00:01 |
|* 2 | HASH JOIN OUTER | | 4 | 92 | 7 (15)| 00:00:01 |
| 3 | TABLE ACCESS FULL| LEFT_TAB | 4 | 44 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| RIGHT_TAB | 4 | 48 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
-- full join(没有+写法) 显示两个表的所有数据，但只有匹配的数据显示在同一行
SQL> SELECT AS left_name, AS right_name FROM left_tab l FULL JOIN right_tab r ON = order by 1;
LEFT_N RIGHT_N
------ -------
left_1
left_2
left_3 right_3
left_4 right_4
right_6
right_5
6 rows selected.
-- 如果多表inner join，最终结果只返回一个表的数据
SQL> SELECT , FROM left_tab l,right_tab r WHERE =(+) order by 1;
Execution Plan
----------------------------------------------------------
Plan hash value: 1232751584
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 56 | 8 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 4 | 56 | 8 (25)| 00:00:01 |
|* 2 | HASH JOIN OUTER | | 4 | 56 | 7 (15)| 00:00:01 |
| 3 | TABLE ACCESS FULL| LEFT_TAB | 4 | 44 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| RIGHT_TAB | 4 | 12 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
对于上面的语句可以使用 in或者exists改写
SQL> SELECT name,id FROM left_tab l WHERE id IN (SELECT id FROM right_tab r WHERE =) order by 1;
Execution Plan
----------------------------------------------------------
Plan hash value: 3358900048
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 56 | 8 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 4 | 56 | 8 (25)| 00:00:01 |
|* 2 | HASH JOIN SEMI | | 4 | 56 | 7 (15)| 00:00:01 |
| 3 | TABLE ACCESS FULL| LEFT_TAB | 4 | 44 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| RIGHT_TAB | 4 | 12 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"="ID")
SQL> SELECT name,id FROM left_tab l WHERE EXISTS (SELECT NULL FROM right_tab r WHERE =) order by 1;
Execution Plan
----------------------------------------------------------
Plan hash value: 3358900048
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 56 | 8 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 4 | 56 | 8 (25)| 00:00:01 |
|* 2 | HASH JOIN SEMI | | 4 | 56 | 7 (15)| 00:00:01 |
| 3 | TABLE ACCESS FULL| LEFT_TAB | 4 | 44 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| RIGHT_TAB | 4 | 12 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("R"."ID"="L"."ID")
-- 多表连接的过滤条件
/* 当过滤条件在join后和where前时可以先过滤在join */
SQL> SELECT AS left_name, AS right_name FROM left_tab l LEFT JOIN right_tab r ON = and =3 ORDER BY 1 ;
LEFT_N RIGHT_N
------ -------
left_1
left_2
left_3 right_3
left_4
/* 改写后 */
SQL> SELECT AS left_name, AS right_name FROM left_tab l LEFT JOIN (SELECT * FROM right_tab WHERE id=3) R ON = ORDER BY 1;
LEFT_N RIGHT_N
------ -------
left_1
left_2
left_3 right_3
left_4
/* 当过滤条件在join后和where里面，CBO将自动改写为INNER JOIN，从ID2可以看出*/
SQL> SELECT AS left_name, AS right_name FROM left_tab l LEFT JOIN right_tab r ON = WHERE =3 ORDER BY 1 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 901870180
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 8 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 1 | 23 | 8 (25)| 00:00:01 |
|* 2 | HASH JOIN | | 1 | 23 | 7 (15)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| RIGHT_TAB | 1 | 12 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| LEFT_TAB | 4 | 44 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("L"."ID"="R"."ID")
3 - filter(TO_NUMBER("R"."ID")=3)
-- 因此可以将上面的改写为
SQL> SELECT AS left_name, AS right_name FROM left_tab l JOIN (SELECT * FROM right_tab WHERE id=3) r ON =;
Execution Plan
----------------------------------------------------------
Plan hash value: 3321337660
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 23 | 7 (15)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| RIGHT_TAB | 1 | 12 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| LEFT_TAB | 4 | 44 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("L"."ID"="RIGHT_TAB"."ID")
2 - filter(TO_NUMBER("ID")=3)
