搭建数据
create table tb_user1 (id integer primary key, user_name varchar2(20) not null);
create table tb_user2 (id integer primary key, user_name varchar2(20) not null);
insert into tb_user1 values (1, 'user11');
insert into tb_user1 values (2, 'user12');
insert into tb_user1 values (3, 'user13');
insert into tb_user2 values (2, 'user22');
insert into tb_user2 values (3, 'user23');
insert into tb_user2 values (4, 'user24');
commit;
然后select：
SQL> select * from tb_user1;
ID USER_NAME
---------- --------------------
1 user11
2 user12
3 user13
SQL> select * from tb_user2;
ID USER_NAME
---------- --------------------
2 user22
3 user23
4 user24
join
如果表中有至少一个匹配，则返回行
SQL> select u1.id id, u1.user_name name1, u2.user_name name2 from tb_user1 u1 join tb_user2 u2 on u1.id = u2.id;
ID NAME1 NAME2
---------- -------------------- --------------------
2 user12 user22
3 user13 user23
SQL>
此时SQL也可以这样写：
SQL> select u1.id id, u1.user_name name1, u2.user_name name2 from tb_user1 u1, tb_user2 u2 where u1.id = u2.id;
ID NAME1 NAME2
---------- -------------------- --------------------
2 user12 user22
3 user13 user23
SQL> 这是等值连接
inner join
在表中存在至少一个匹配时，INNER JOIN 关键字返回行。
SQL> select u1.id id, u1.user_name name1, u2.user_name name2 from tb_user1 u1 inner join tb_user2 u2 on u1.id = u2.id;
ID NAME1 NAME2
---------- -------------------- --------------------
2 user12 user22
3 user13 user23
SQL>
join和inner join是一样的。
left join
LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。
SQL> select u1.id id, u1.user_name name1, u2.user_name name2 from tb_user1 u1 left join tb_user2 u2 on u1.id = u2.id;
ID NAME1 NAME2
---------- -------------------- --------------------
1 user11
2 user12 user22
3 user13 user23
SQL>
right join
RIGHT JOIN 关键字会右表 (table_name2) 那里返回所有的行，即使在左表 (table_name1) 中没有匹配的行。
SQL> select u1.id id, u1.user_name name1, u2.user_name name2 from tb_user1 u1 right join tb_user2 u2 on u1.id = u2.id;
ID NAME1 NAME2
---------- -------------------- --------------------
2 user12 user22
3 user13 user23
user24
SQL>
full join
只要其中某个表存在匹配，FULL JOIN 关键字就会返回行。
SQL> select u1.id id, u1.user_name name1, u2.user_name name2 from tb_user1 u1 full join tb_user2 u2 on u1.id = u2.id;
ID NAME1 NAME2
---------- -------------------- --------------------
1 user11
2 user12 user22
3 user13 user23
user24
SQL>
下面三条SQL
select u1.id id, u1.user_name name1, u2.user_name name2 from tb_user1 u1 join tb_user2 u2 on u1.id = u2.id;
select u1.id id, u1.user_name name1, u2.user_name name2 from tb_user1 u1, tb_user2 u2 where u1.id = u2.id;
select u1.id id, u1.user_name name1, u2.user_name name2 from tb_user1 u1 inner join tb_user2 u2 on u1.id = u2.id;
执行计划
SQL> set linesize 120
SQL>
SQL> select u1.id id, u1.user_name name1, u2.user_name name2 from tb_user1 u1 join tb_user2 u2 on u1.id = u2.id;
执行计划
----------------------------------------------------------
Plan hash value: 3210170867
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 150 | 6 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 3 | 150 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | TB_USER1 | 3 | 75 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| TB_USER2 | 1 | 25 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | SYS_C005665 | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("U1"."ID"="U2"."ID")
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
13 consistent gets
0 physical reads
0 redo size
579 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
SQL> select u1.id id, u1.user_name name1, u2.user_name name2 from tb_user1 u1 inner join tb_user2 u2 on u1.id = u2.id;
执行计划
----------------------------------------------------------
Plan hash value: 3210170867
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 150 | 6 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 3 | 150 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | TB_USER1 | 3 | 75 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| TB_USER2 | 1 | 25 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | SYS_C005665 | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("U1"."ID"="U2"."ID")
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
13 consistent gets
0 physical reads
0 redo size
579 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
SQL> select u1.id id, u1.user_name name1, u2.user_name name2 from tb_user1 u1, tb_user2 u2 where u1.id = u2.id;
执行计划
----------------------------------------------------------
Plan hash value: 3210170867
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 150 | 6 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 3 | 150 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | TB_USER1 | 3 | 75 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| TB_USER2 | 1 | 25 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | SYS_C005665 | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("U1"."ID"="U2"."ID")
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
13 consistent gets
0 physical reads
0 redo size
579 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
SQL>
优化器把它们转换成了相同的语句。
