用实验步骤加深理解Oracle的外连接(left/right/full)和内连接(inner)

用实验方法加深理解Oracle的外连接(left/right/full)和内连接(inner)

总是对Oracle的左连接、右连接以及(+)对应的外连接类型糊涂，通过实验加深对连接类型语法的理解。外连接分为三种：

1. 左外连接，对应SQL关键字：LEFT (OUTER) JOIN

2. 右外连接，对应SQL关键字：RIGHT (OUTER) JOIN

3. 全外连接，对应SQL关键字：FULL (OUTER) JOIN

左右外连接都是以一张表为基表，在显示基表所有记录外，加上另外一张表中匹配的记录。如果基表的数据在另一张表中没有记录，那么相关联的结果集行中显示为空值。

精确点说，引用MOS：

对于左连接，将会返回join连接条件中第一次提到的表(或者”左边“的表)。

对于右连接，将会返回join连接条件中第二次提到的表(或者”右边“的表)。

实验：

1. 准备：

SQL> create table left_tbl(id number);
Table created.

SQL> create table right_tbl(id number);
Table created.

insert into left_tbl values(1);

insert into left_tbl values(2);

insert into left_tbl values(3);

insert into right_tbl values(2);

insert into right_tbl values(3);

insert into right_tbl values(6);

SQL> select * from left_tbl;
        ID
----------
         1
         2
         3

SQL> select * from right_tbl;
        ID
----------
         2
         3
         6

2. 左连接

SQL> select * from left_tbl l left join right_tbl r on l.id = r.id;
        ID         ID
---------- ----------
         2          2
         3          3
         1

从排序看，左右表匹配的记录排在前面，并且是升序。

也可以这样写：select * from left_tbl l left join right_tbl r on l.id = r.id(+);

SQL> select * from left_tbl l, right_tbl r where l.id = r.id(+);
        ID         ID
---------- ----------
         2          2
         3          3
         1

使用(+)，放右表表示左表所有记录，再加上右表的记录。

SQL> select * from left_tbl l, right_tbl r where r.id(+) = l.id;
        ID         ID
---------- ----------
         2          2
         3          3
         1

将where条件中左右表互换，结果还是左连接，因为(+)在左边，表示加上right表的记录。

SQL> select * from left_tbl left join right_tbl using (id);
        ID
----------
         2
         3
         1

此处使用using，只显示了左表的记录。

3. 右连接

SQL> select * from left_tbl l right join right_tbl r on l.id = r.id;
        ID         ID
---------- ----------
         2          2
         3          3
                    6

和左右连接相同，左右表匹配的记录排在前面，并且是升序。

也可以这样写：select * from left_tbl l right join right_tbl r on l.id(+) = r.id;

SQL> select * from left_tbl l, right_tbl r where l.id(+) = r.id;

        ID         ID
---------- ----------
         2          2
         3          3
                    6

使用(+)，放左表表示右表所有记录，再加上左表的记录。

SQL> select * from left_tbl l, right_tbl r where r.id = l.id(+);

        ID         ID
---------- ----------
         2          2
         3          3
                    6

将where条件中左右表互换，结果还是右连接，因为(+)在右边，表示加上left表的记录。

4. 全外连接

SQL> select * from left_tbl l full join right_tbl r on l.id = r.id;或select * from left_tbl l full outer join right_tbl r on l.id = r.id;
        ID         ID
---------- ----------
         2          2
         3          3
         1
                    6
会显示左右表的记录，不匹配的用NULL。

5. 内连接

说了外连接，再看下内连接，innser join或join。

SQL> select * from left_tbl l inner join right_tbl r on l.id = r.id;
        ID         ID
---------- ----------
         2          2
         3          3
用inner join只显示左右表都匹配的记录。

SQL> select * from left_tbl l join right_tbl r on l.id = r.id;
        ID         ID
---------- ----------
         2          2
         3          3

直接用join和inner join相同。

SQL> select * from left_tbl l, right_tbl r where l.id = r.id;
        ID         ID
---------- ----------
         2          2
         3          3

用=代替on是内连接另外一种用法。

总结：
1. (+)在哪个表的旁边，就表示基表是另外一张表，结果集还需要加上(+)表中不匹配的数据。
2. 内连接inner关键字可省，外连接outer关键字可省。
3. 用on和where =都可以使用(+)方式。

用实验步骤加深理解Oracle的外连接(left/right/full)和内连接(inner)

相关推荐