oracle中表的(+)左外连接,右外连接,全外连接

最新推荐文章于 2022-02-24 18:40:00 发布
最新推荐文章于 2022-02-24 18:40:00 发布
阅读量837 收藏
点赞数
分类专栏： Oracle Oracle EBS 文章标签： oracle access table join sql insert

 

oracle中表的左外连接,右外连接,全外连接

 

左外连接的driver table是from后面的第一个表(外连接的表) 

右外连接的driver table是from后面的第二个表(外连接的表) 

 

 

create table employees(
last_name varchar2(20),
department_id number(6)
)
/

insert into employees(last_name,department_id)
values('eygle',1);
insert into employees(last_name,department_id)
values('bity_rainy',2);
insert into employees(last_name,department_id)
values('piner',3);
insert into employees(last_name,department_id)
values('coolyl',5);

create table departments(
department_id number(6),
department_name varchar2(20)
)
/
insert into departments(department_id,department_name)
values(1,'alibaba');
insert into departments(department_id,department_name)
values(2,'oracle');
insert into departments(department_id,department_name)
values(3,'ibm');
insert into departments(department_id,department_name)
values(4,'sun');


---1. 普通的相等连接
select e.last_name, d.department_name
from employees e, departments d
where e.department_id = d.department_id;

---2 左外连接 LEFT OUTER JOIN
select e.last_name,e.department_id,d.department_name
from employees e
left outer join departments d
on (e.department_id = d.department_id);

select e.last_name, d.department_name
from employees e, departments d
where e.department_id = d.department_id(+);
结果为：所有员工及对应部门的记录，包括没有对应部门编号department_id的员工记录。

 

SQL> set autotrace on
SQL> select e.last_name,e.department_id,d.department_name
  2  from employees e
  3  left outer join departments d
  4  on (e.department_id = d.department_id)
  5  /

LAST_NAME            DEPARTMENT_ID DEPARTMENT_NAME
-------------------- ------------- --------------------
eygle                            1 alibaba
bity_rainy                       2 oracle
piner                            3 ibm
coolyl                           5


执行计划
----------------------------------------------------------
Plan hash value: 2296652067

--------------------------------------------------------------------------------
--

| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time
 |

--------------------------------------------------------------------------------
--

|   0 | SELECT STATEMENT   |             |     4 |   200 |     7  (15)| 00:00:01
 |

|*  1 |  HASH JOIN OUTER   |             |     4 |   200 |     7  (15)| 00:00:01
 |

|   2 |   TABLE ACCESS FULL| EMPLOYEES   |     4 |   100 |     3   (0)| 00:00:01
 |

|   3 |   TABLE ACCESS FULL| DEPARTMENTS |     4 |   100 |     3   (0)| 00:00:01
 |

--------------------------------------------------------------------------------
--


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"(+))

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
          7  recursive calls
          0  db block gets
         31  consistent gets
          0  physical reads
          0  redo size
        547  bytes sent via SQL*Net to client
        377  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          4  rows processed

SQL> select e.last_name,e.department_id, d.department_name
  2  from employees e, departments d
  3  where e.department_id = d.department_id(+)
  4  /

LAST_NAME            DEPARTMENT_ID DEPARTMENT_NAME
-------------------- ------------- --------------------
eygle                            1 alibaba
bity_rainy                       2 oracle
piner                            3 ibm
coolyl                           5


执行计划
----------------------------------------------------------
Plan hash value: 2296652067

--------------------------------------------------------------------------------
--

| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time
 |

--------------------------------------------------------------------------------
--

|   0 | SELECT STATEMENT   |             |     4 |   200 |     7  (15)| 00:00:01
 |

|*  1 |  HASH JOIN OUTER   |             |     4 |   200 |     7  (15)| 00:00:01
 |

|   2 |   TABLE ACCESS FULL| EMPLOYEES   |     4 |   100 |     3   (0)| 00:00:01
 |

|   3 |   TABLE ACCESS FULL| DEPARTMENTS |     4 |   100 |     3   (0)| 00:00:01
 |

--------------------------------------------------------------------------------
--


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"(+))

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
          7  recursive calls
          0  db block gets
         31  consistent gets
          0  physical reads
          0  redo size
        547  bytes sent via SQL*Net to client
        377  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          4  rows processed

SQL>

 


----3. 右外连接 right outer join
select e.last_name,e.department_id,d.department_name
from employees e
right outer join departments d
on (e.department_id = d.department_id);

select e.last_name, d.department_id,d.department_name
from employees e, departments d
where e.department_id(+) = d.department_id;
结果为：所有员工及对应部门的记录，包括没有任何员工的部门记录。

 

SQL> select e.last_name, d.department_id,d.department_name
  2  from employees e, departments d
  3  where e.department_id(+) = d.department_id
  4  /

LAST_NAME            DEPARTMENT_ID DEPARTMENT_NAME
-------------------- ------------- --------------------
eygle                            1 alibaba
bity_rainy                       2 oracle
piner                            3 ibm
                                 4 sun


执行计划
----------------------------------------------------------
Plan hash value: 2392326604

--------------------------------------------------------------------------------
--

| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time
 |

--------------------------------------------------------------------------------
--

|   0 | SELECT STATEMENT   |             |     4 |   200 |     7  (15)| 00:00:01
 |

|*  1 |  HASH JOIN OUTER   |             |     4 |   200 |     7  (15)| 00:00:01
 |

|   2 |   TABLE ACCESS FULL| DEPARTMENTS |     4 |   100 |     3   (0)| 00:00:01
 |

|   3 |   TABLE ACCESS FULL| EMPLOYEES   |     4 |   100 |     3   (0)| 00:00:01
 |

--------------------------------------------------------------------------------
--


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("E"."DEPARTMENT_ID"(+)="D"."DEPARTMENT_ID")

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
          7  recursive calls
          0  db block gets
         31  consistent gets
          0  physical reads
          0  redo size
        544  bytes sent via SQL*Net to client
        377  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          4  rows processed

SQL> select e.last_name,e.department_id,d.department_name
  2  from employees e
  3  right outer join departments d
  4  on (e.department_id = d.department_id);

LAST_NAME            DEPARTMENT_ID DEPARTMENT_NAME
-------------------- ------------- --------------------
eygle                            1 alibaba
bity_rainy                       2 oracle
piner                            3 ibm
                                   sun


执行计划
----------------------------------------------------------
Plan hash value: 2392326604

--------------------------------------------------------------------------------
--

| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time
 |

--------------------------------------------------------------------------------
--

|   0 | SELECT STATEMENT   |             |     4 |   200 |     7  (15)| 00:00:01
 |

|*  1 |  HASH JOIN OUTER   |             |     4 |   200 |     7  (15)| 00:00:01
 |

|   2 |   TABLE ACCESS FULL| DEPARTMENTS |     4 |   100 |     3   (0)| 00:00:01
 |

|   3 |   TABLE ACCESS FULL| EMPLOYEES   |     4 |   100 |     3   (0)| 00:00:01
 |

--------------------------------------------------------------------------------
--


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("E"."DEPARTMENT_ID"(+)="D"."DEPARTMENT_ID")

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
          7  recursive calls
          0  db block gets
         31  consistent gets
          0  physical reads
          0  redo size
        542  bytes sent via SQL*Net to client
        377  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          4  rows processed

SQL>
SQL>

 

----4.FULL OUTER JOIN：全外关联
select e.last_name, e.department_id, d.department_name
from employees e
full outer join departments d
on (e.department_id = d.department_id);

 

SQL> l
  1  select e.last_name, e.department_id, d.department_name
  2  from employees e
  3  full outer join departments d
  4* on (e.department_id = d.department_id)
SQL> /

LAST_NAME            DEPARTMENT_ID DEPARTMENT_NAME
-------------------- ------------- --------------------
eygle                            1 alibaba
bity_rainy                       2 oracle
piner                            3 ibm
coolyl                           5
                                   sun


执行计划
----------------------------------------------------------
Plan hash value: 804703286

------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     5 |   185 |    13   (8)| 00:00:01 |
|   1 |  VIEW                |             |     5 |   185 |    13   (8)| 00:00:01 |
|   2 |   UNION-ALL          |             |       |       |            |          |
|*  3 |    HASH JOIN OUTER   |             |     4 |   608 |     7  (15)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| EMPLOYEES   |     4 |   404 |     3   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| DEPARTMENTS |     4 |   204 |     3   (0)| 00:00:01 |
|*  6 |    HASH JOIN ANTI    |             |     1 |    38 |     7  (15)| 00:00:01 |
|   7 |     TABLE ACCESS FULL| DEPARTMENTS |     4 |   100 |     3   (0)| 00:00:01 |
|   8 |     TABLE ACCESS FULL| EMPLOYEES   |     4 |    52 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"(+))
   6 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         29  consistent gets
          0  physical reads
          0  redo size
        558  bytes sent via SQL*Net to client
        377  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed

SQL>

 

SQL>
SQL> select nvl(e.last_name,'NO value!!'), nvl(e.department_id,999), nvl(d.department_name,'NO value Too!!')
  2  from employees e
  3  full outer join departments d
  4  on (e.department_id = d.department_id);

NVL(E.LAST_NAME,'NOV NVL(E.DEPARTMENT_ID,999) NVL(D.DEPARTMENT_NAM
-------------------- ------------------------ --------------------
eygle                                       1 alibaba
bity_rainy                                  2 oracle
piner                                       3 ibm
coolyl                                      5 NO value Too!!
NO value!!                               999 sun


执行计划
----------------------------------------------------------
Plan hash value: 804703286

------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     5 |   185 |    13   (8)| 00:00:01 |
|   1 |  VIEW                |             |     5 |   185 |    13   (8)| 00:00:01 |
|   2 |   UNION-ALL          |             |       |       |            |          |
|*  3 |    HASH JOIN OUTER   |             |     4 |   608 |     7  (15)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| EMPLOYEES   |     4 |   404 |     3   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| DEPARTMENTS |     4 |   204 |     3   (0)| 00:00:01 |
|*  6 |    HASH JOIN ANTI    |             |     1 |    38 |     7  (15)| 00:00:01 |
|   7 |     TABLE ACCESS FULL| DEPARTMENTS |     4 |   100 |     3   (0)| 00:00:01 |
|   8 |     TABLE ACCESS FULL| EMPLOYEES   |     4 |    52 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"(+))
   6 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
         13  recursive calls
          0  db block gets
         61  consistent gets
          0  physical reads
          0  redo size
        637  bytes sent via SQL*Net to client
        377  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
          5  rows processed

SQL>

 

 

 


结果为：所有员工及对应部门的记录，包括没有对应部门编号department_id的员工记录和没有任何员工的部门记录。

 

from:http://hi.baidu.com/changkongyinxue/blog/item/e32c38faffc23c9058ee90a3.html

aicon
关注
专栏目录
oracle左外连接，右外连接，全外连接--实例讲解
03-14 4068
看了网上一些例子，自己改了改，并做了实验，希望能把这个东西说明白。 先创建表，并插入数据： -- 部门表 CREATE TABLE departments ( depID NUMBER(38,0), depName VARCHAR2(20), delFlag NUMBER(1,0) ); -- 员工表 CREATE TABLE employees ( empID
oracle左右外连接,Oracle中左右外连接详解
weixin_30800961的博客
04-13 979
1、内连接(自然连接):只有两个表相匹配的行才能在结果集中出现2、外连接: 包括(1)左外连接(左边的表不加限制)(2)右外连接(右边的表不加限制)(3)全外连接(左右两表都不加限制)3、自连接(连接发生在一张基表内)以下是三种连接的区分：select a.studentno, a.studentname, b.classnamefrom students a, classes bwhere a....
Oracle的内连接、左外连接、右外连接及全外连接
thy822的专栏
06-28 824
理解Oracle的各种连接方法的最有效的方法就是“躬亲”，在实践中去深刻理解内连接，左外连接，右外连接，全外连接的概念的和效果。1.创建测试表并准备测试数据sec@ora10g> create table a (a number(1),b number(1),c number(1));sec@ora10g> create table b (a number(1),d number(1),e num
oracle中表的左外连接,右外连接,全外连接
ocean1010的专栏
10-09 859
<br />--建立测试表<br />create table employees(<br />last_name varchar2(20),<br />department_id number(6)<br />)<br />/<br />insert into employees(last_name,department_id)<br />values('eygle',1);<br />insert into employees(last_name,department_id)<br />values('
oracle中左外连接和右外连接
zhengqiqiqinqin的专栏
06-05 1654
oracle中左外连接和右外连接 为了方便理解，举例说明是最好的了，如下： 外连接分为：①左外连接②右外连接③完全外连接 create table stu( id number, name varchar2(32) ); insert into stu values(1,'Jack'); insert into stu values(2,'Tome'); insert int
oracle中表的左外连接,右外连接,全外连接(转)
Anssin007的专栏
03-30 1138
 oracle中表的左外连接,右外连接,全外连接 create table employees( last_name varchar2(20), department_id number(6) ) / insert into employees(last_name,department_id) values(eygle,1); insert into employees(last_name,d
oracle 中(+) 左连接、右连接
YJB666的专栏
12-24 1405
oracle 中(+) 左连接、右连接 oracle中的(+)是一种特殊的用法，(+)表示外连接，并且总是放在非主表的一方。 例如 左外连接：select A.a,B.a from A LEFT JOIN B ON A.b=B.b; --A表为主表B标为副表，连接后进行A.b=B.b条件匹配 等价于select A.a,B.a from A,B where A.b = B.b(...
oracle创建视图 左连接,Oracle 左连接、右连接、全外连接、（+）号作用
weixin_34239718的博客
04-12 993
Oracle 外连接(1)左外连接 (左边的表不加限制)(2)右外连接(右边的表不加限制)(3)全外连接(左右两表都不加限制)外连接(Outer Join)outer join则会返回每个满足第一个(顶端)输入与第二个(底端)输入的联接的行。它还返回任何在第二个输入中没有匹配行的第一个输入中的行。外连接分为三种： 左外连接，右外连接，全外连接。 对应SQL：LEFT/RIGHT/FULL OUT...
Oracle - 【连接2】自然连接+左外连接+右外连接
⚡
04-02 1548
=>【连接1】广义笛卡儿积+一般连接+自连接 自然连接（Natural Join） select * FROM COURSE NATURAL JOIN SCORE; Result（取相同字段并混合该相同字段）： >>> 自然连接后只能得到一条数据：小明 2019001 左外连接（LEFT OUTER JOIN） =&gt...
Oracle 外连接实现代码
09-11
而在旧版Oracle中，右外关联的等价写法为： ```sql SELECT e.last_name, e.department_id, d.department_name FROM employees e, departments d WHERE e.department_id(+)=d.department_id; ``` RIGHT OUTER JOIN...
温故而知新：Oracle的表连接
某技术爱好者的专栏
12-17 1070
Oracle的表连接(joins)： 交叉连接（Crossjoin）：笛卡尔积 内连接（Inner join）：-------------------->解决匹配的问题 分类：1.等值连接(Equijoins)； 2.非等值连接(Non-Equijoins)； 3.自连接(SelfJoins)。 外连接（Outer join）：-----
MySQL查询（2022-02-24）
qq_45753423的博客
02-24 501
-- 学生表对应是班级表，关系为一对多 alter table student add FOREIGN key(sid) REFERENCES classes(cid); create table stu_cour( scid int(10) primary key, sno int(10), cno int(10), score int(255) ) alter table stu_cour add FOREIGN key(sno) RE...
oracle中括号里加号,Oracle连接说明-及另外表示方法-括号加号(+)
weixin_32535389的博客
04-03 1778
Oracle中可以使用“(+) ”来表示，9i可以使用LEFT/RIGHT/FULL OUTER JOINLEFT OUTER JOIN：左外关联SELECT e.last_name, e.department_id, d.department_nameFROM employees eLEFT OUTER JOIN departments dON (e.department_id = d.depa...
Oracle连接查询，彻底搞懂外连接（左外连接&右外连接）
热门推荐
ljinxin的博客
10-11 5万+
Oracle连接查询有3种：交叉连接、内连接、外连接。 交叉连接结果是其他连接结果的超集，外连接结果是内连接结果的超集。 接下的例子以departments_v、employees_v两个视图数据为例（4条部门数据，9条人员数据） 1.交叉连接：又称笛卡尔积连接，是两个或多个表间的无条件连接，因此它会将表1的每一条数据与表2的每一条数据连接，因此结果会有4*9=36条数据
Oracle外连接
u012289399的博客
05-28 5671
外连接不但返回符合连接和查询条件的数据行，还返回不符合条件的一些行。外连接可以是左向外连接、右向外连接或完整外部连接。 -- 使用RIGHT OUTER JOIN SELECT e.employee_id, e.last_name, d.department_id,d.department_name FROM employees e RIGHT OUTER JOIN departments...
oracle 外连接
Dark丶照萤映雪
12-21 427
左外连接：左表A与左表B左外连接时，A表显示全部数据(符合与不符合都显示)，B表只显示符合条件的数据 1、select e.empno,e.ename,d.deptno,d.dname from emp e left outer join dept d on e.deptno = d.deptno; 2、select e.empno,e.ename,d.deptno,d.dname from...
oracle3表连接方式,oracle三张表左连接
weixin_42503008的博客
04-02 1421
三张表，要查询出结果是：第一张表全显示，其余两张表显示对应连接的信息三张表分别是SQL> select * from zr_stu;STUID STUNAME STUSEX----------- -------------------- ------1 付政委 男2 江风 男3 郝忠军 ...
oracle 三个表怎么连接,oracle三张表左连接语句
weixin_39644952的博客
04-03 2103
oracle三张表左连接语句三张表，要查询出结果是：第一张表全显示，其余两张表显示对应连接的信息三张表分别是[sql]SQL> select * from zr_stu;www.2cto.comSTUID STUNAME STUSEX----------- -------------------- ------1 付政委 男2 江风 ...
Oracle外连接查询详解：左、右、全外连接
2. **右外连接 (RIGHT OUTER JOIN)**：与左外连接相反，右外连接关注右表，确保所有右表的记录都被包含，即使在左表中没有匹配的记录。这在需要显示所有产品，无论是否有人购买时很有用。 3. **全外连接 (FULL ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值