oracle join、inner join、left join、right join、full join

最新推荐文章于 2023-10-31 14:33:42 发布

feier7501

最新推荐文章于 2023-10-31 14:33:42 发布

阅读量685

点赞数

分类专栏： oracle

本文链接：https://blog.csdn.net/feier7501/article/details/23296075

版权

oracle 专栏收录该内容

103 篇文章 2 订阅

订阅专栏

搭建数据

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>

优化器把它们转换成了相同的语句。