ORACLE 外连接及(+) 说明

ORACLE 外连接及(+) 说明

Table of Contents

• 1. 外连接说明
• 2. 示例
  • 2.1. 生成测试数据
  • 2.2. 测试

1 外连接说明

(1）左外连接 (左边的表不加限制), 使用关键词 left [outer] join 表示，查询结果为左侧表全量数据输出，右侧表进行匹配输出，未匹配到的数据为空。

(2）右外连接(右边的表不加限制), 使用关键词 rigth [outer] join 表示，查询结果为右侧表全量数据输出，左侧表进行匹配输出，未匹配到的数据为空。

(3）全外连接(左右两表都不加限制), 使用关键词 full [outer] join 表示,查询结果为左右两侧表全量输出, 无法匹配的数据，也全部输出。

另外一种左右外连接的表示法为：(+)

当(+) 在"="左侧时，代表以右侧为全量输出，即为右外连接

当(+) 在"="右侧时，代表以左侧为全量输出，即为左外连接

总结一句话时，就是(+)在哪一侧，对应的表输出的未匹配到的数据为空，即为反方面的外连接。

使用(+)有以下限制:

1.（+）操作符只能出现在where子句中，并且不能与outer join语法同时使用。

1. 当使用（+）操作符执行外连接时，如果在where子句中包含有多个条件，则必须在所有条件中都包含（+）操作符

3.（+）操作符只适用于列，而不能用在表达式上。

4.（+）操作符不能与or和in操作符一起使用。

5.（+）操作符只能用于实现左外连接和右外连接，而不能用于实现完全外连接。

NOTE

在外连接中使用 and 条件(非关联条件)，Oracle 会先执行外连接，然后以外连接的结果为基础，执行 and 条件的过滤，而不是在原表基础上执行and条件过滤。

2 示例

 

2.1 生成测试数据

-- 生成数据
create table ta(id number,name varchar2(10));
create table tb(id number,name varchar2(10));

--相同数据
insert into ta values(1,'ha');
insert into ta values(2,'ha');
insert into ta values(3,'ha');
insert into tb values(1,'ha');
insert into tb values(2,'ha');
insert into tb values(3,'ha');
-- 不同数据
insert into ta values(4,'h4');
insert into tb values(5,'h5');
insert into ta values(6,'h6');
insert into tb values(7,'h7');
commit;

2.2 测试

• (+)

  • 左外连接，（+）在右侧

    select *
    from ta,tb
    where ta.id=tb.id(+);
    
            ID NAME               ID NAME
    ---------- ---------- ---------- ----------
             1 ha                  1 ha
             2 ha                  2 ha
             3 ha                  3 ha
             6 h6
             4 h4
    
    

  • 右外连接，（+）在左侧

    select *
    from ta,tb
    where ta.id(+)=tb.id
    and ta.id is null; -- 注释掉该查看结果有什么不同。这里的and 条件是针对哪个结果集操作的
    
            ID NAME               ID NAME
    ---------- ---------- ---------- ----------
                                   7 h7
                                   5 h5
    

• 关键词

  • left join

    select *
    from ta left join tb
    on ta.id = tb.id;
    
            ID NAME               ID NAME
    ---------- ---------- ---------- ----------
             1 ha                  1 ha
             2 ha                  2 ha
             3 ha                  3 ha
             6 h6
             4 h4
    
    SQL>
    

  • right join

    select *
    from ta right join tb
    on ta.id = tb.id;
    
    
            ID NAME               ID NAME
    ---------- ---------- ---------- ----------
             1 ha                  1 ha
             2 ha                  2 ha
             3 ha                  3 ha
                                   7 h7
                                   5 h5
    

  • full out join

    select * from ta full join tb
    on ta.id = tb.id;
    
            ID NAME               ID NAME
    ---------- ---------- ---------- ----------
             1 ha                  1 ha
             2 ha                  2 ha
             3 ha                  3 ha
                                   5 h5
                                   7 h7
             6 h6
             4 h4
    
    7 rows selected.
    

Author: halberd.lee

Created: 2020-05-26 Tue 16:06

Validate