Oracle的连接详解(左连接、右连接、全连接...)
来源:互联网 发布:求50个的素数编程 编辑:程序博客网 时间:2024/06/11 22:19
详解Oracle多种表连接方式
1. 内连接(Inner Join/Join)
1.1. Inner Join
Inner join逻辑运算符返回满足第一个(顶端)输入与第二个(底端)输入联接的每一行。这个和用select查询多表是一样的效果,所以内连接用的很少。
还有一点要说明的就是Join 默认就是inner join。 所以我们在写内连接的时候可以省略inner这个关键字。
1.2. 下面举例来说明内连接
1.2.1. 先创建2张测试表并插入数据
1 SQL> select *from dave;
2 ID NAME
3 ---------- ----------
4 1 dave
5 2 bl
6 1 bl
7 2 dave
8
9
10 SQL> select *from bl;
11 ID NAME
12 ---------- ----------
13 1 dave
14 2 bl
1.2.2. 用内链接进行查询
1 SQL> Select a.id,a.name,b.name from dave a inner join bl bon a.id=b.id; --标准写法
2 ID NAME NAME
3 ---------- ---------- ----------
4 1 dave dave
5 2 bl bl
6 1 bl dave
7 2 dave bl
8
9
10 SQL> Select a.id,a.name,b.name from dave a join bl b on a.id=b.id; --这里省略了inner关键字
11 ID NAME NAME
12 ---------- ---------- ----------
13 1 dave dave
14 2 bl bl
15 1 bl dave
16 2 dave bl
17
18
19 SQL> Select a.id,a.name,b.name from dave a,bl b where a.id=b.id; -- select多表查询
20 ID NAME NAME
21 ---------- ---------- ----------
22 1 dave dave
23 2 bl bl
24 1 bl dave
25 2 dave bl
从这三个SQL 的结果我们也可以看出,他们的作用是一样的。
1.3. 自然连接(Natural join)
自然连接是在两张表中寻找那些数据类型和列名都相同的字段,然后自动地将他们连接起来,并返回所有符合条件按的结果。
先看一下自然连接的例子:
1 SQL> Select id,name from dave a naturaljoin bl b;
2 ID NAME
3 ---------- ----------
4 1 dave
5 2 bl
这里我们并没有指定连接的条件,实际上oracle为我们自作主张的将,dave表中的id和name字段与bl表中的id和name字段进行了连接。也就是实际上相当于
6 SQL> Select dave.id,bl.name
7 From davejoin bl on dave.id = bl.idand dave.name=bl.name;
8 ID NAME
9 ---------- ----------
10 1 dave
11 2 bl
因此,我们也可以将自然连接理解为内连接的一种。
有关自然连接的一些注意事项:
(1).如果做自然连接的两个表的有多个字段都满足有相同名称和类型,那么他们会被作为自然连接的条件。
(2).如果自然连接的两个表仅是字段名称相同,但数据类型不同,那么将会返回一个错误。
2. 外连接(Outer Join)
outer join则会返回每个满足第一个(顶端)输入与第二个(底端)输入的联接的行。它还返回任何在第二个输入中没有匹配行的第一个输入中的行。外连接分为三种: 左外连接,右外连接,全外连接。 对应SQL:LEFT/RIGHT/FULL OUTER JOIN。 通常我们省略outer 这个关键字。 写成:LEFT/RIGHT/FULL JOIN。
在左外连接和右外连接时都会以一张表为基表,该表的内容会全部显示,然后加上两张表匹配的内容。如果基表的数据在另一张表没有记录。 那么在相关联的结果集行中列显示为空值(NULL)。
对于外连接,也可以使用“(+) ”来表示。 关于使用(+)的一些注意事项:
1.(+)操作符只能出现在where子句中,并且不能与outer join语法同时使用。
2. 当使用(+)操作符执行外连接时,如果在where子句中包含有多个条件,则必须在所有条件中都包含(+)操作符
3.(+)操作符只适用于列,而不能用在表达式上。
4.(+)操作符不能与or和in操作符一起使用。
5.(+)操作符只能用于实现左外连接和右外连接,而不能用于实现完全外连接。
在做实验之前,我们先将dave表和bl里加一些不同的数据。 以方便测试。
1 SQL> select *from bl;
2 ID NAME
3 ---------- ----------
4 1 dave
5 2 bl
6 3 big bird
7 4 exc
8 9 怀宁
9 SQL> select *from dave;
10 ID NAME
11 ---------- ----------
12 8 安庆
13 1 dave
14 2 bl
15 1 bl
16 2 dave
17 3 dba
18 4 sf-express
19 5 dmm
2.1. 左外连接(Left outer join/ left join)
left join是以左表的记录为基础的,示例中Dave可以看成左表,BL可以看成右表,它的结果集是Dave表中的数据,在加上Dave表和BL表匹配的数据。换句话说,左表(Dave)的记录将会全部表示出来,而右表(BL)只会显示符合搜索条件的记录。BL表记录不足的地方均为NULL.
示例:
20 SQL> select *from dave a left join bl b on a.id = b.id;
21 ID NAME IDNAME
22 --------- ---------- ---------- ----------
23 1 bl 1 dave
24 1 dave 1 dave
25 2 dave 2 bl
26 2 bl 2 bl
27 3 dba 3 big bird
28 4 sf-express 4 exc
29 5 dmm --此处B表为null,因为没有匹配到
30 8 安庆 -- 此处B表为null,因为没有匹配到
31
32 SQL> select *from dave a left outer join bl bon a.id = b.id;
33 ID NAME IDNAME
34 ---------- ---------- ---------- ----------
35 1 bl 1 dave
36 1 dave 1 dave
37 2 dave 2 bl
38 2 bl 2 bl
39 3 dba 3 big bird
40 4 sf-express 4 exc
41 5 dmm
42 8 安庆
用(+)来实现, 这个+号可以这样来理解: + 表示补充,即哪个表有加号,这个表就是匹配表。所以加号写在右表,左表就是全部显示,故是左连接。
43 SQL> Select *from dave a,bl b where a.id=b.id(+); --注意: 用(+) 就要用关键字where
44 ID NAME IDNAME
45 ---------- ---------- ---------- ----------
46 1 bl 1 dave
47 1 dave 1 dave
48 2 dave 2 bl
49 2 bl 2 bl
50 3 dba 3 big bird
51 4 sf-express 4 exc
52 5 dmm
53 8 安庆
2.2. 右外连接(right outer join/ right join)
和left join的结果刚好相反,是以右表(BL)为基础的, 显示BL表的所以记录,在加上Dave和BL 匹配的结果。 Dave表不足的地方用NULL填充.
示例:
54 SQL> select *from dave a right join bl b on a.id = b.id;
55 ID NAME IDNAME
56 ---------- ---------- ---------- ----------
57 1 dave 1 dave
58 2 bl 2 bl
59 1 bl 1 dave
60 2 dave 2 bl
61 3 dba 3 big bird
62 4 sf-express 4 exc
63 9 怀宁 --此处左表不足用Null填充
已选择7行。
64 SQL> select *from dave a right outer join bl bon a.id = b.id;
65 ID NAME IDNAME
66 ---------- ---------- ---------- ----------
67 1 dave 1 dave
68 2 bl 2 bl
69 1 bl 1 dave
70 2 dave 2 bl
71 3 dba 3 big bird
72 4 sf-express 4 exc
73 9 怀宁 --此处左表不足用Null填充
已选择7行。
用(+)来实现, 这个+号可以这样来理解: + 表示补充,即哪个表有加号,这个表就是匹配表。所以加号写在左表,右表就是全部显示,故是右连接。
74 SQL> Select *from dave a,bl b where a.id(+)=b.id;
75 ID NAME IDNAME
76 ---------- ---------- ---------- ----------
77 1 dave 1 dave
78 2 bl 2 bl
79 1 bl 1 dave
80 2 dave 2 bl
81 3 dba 3 big bird
82 4 sf-express 4 exc
83 9 怀宁
2.3. 全外连接(full outer join/ full join)
左表和右表都不做限制,所有的记录都显示,两表不足的地方用null 填充。 全外连接不支持(+)这种写法。
示例:
84 SQL> select *from dave a full join bl bon a.id = b.id;
85 ID NAME IDNAME
86 ---------- ---------- ---------- ----------
87 8 安庆
88 1 dave 1 dave
89 2 bl 2 bl
90 1 bl 1 dave
91 2 dave 2 bl
92 3 dba 3 big bird
93 4 sf-express 4 exc
94 5 dmm
95 9 怀宁
已选择9行。
96 SQL> select *from dave a full outer join bl bon a.id = b.id;
97 ID NAME IDNAME
98 ---------- ---------- ---------- ----------
99 8 安庆
100 1 dave 1 dave
101 2 bl 2 bl
102 1 bl 1 dave
103 2 dave 2 bl
104 3 dba 3 big bird
105 4 sf-express 4 exc
106 5 dmm
107 9 怀宁
已选择9行。
3. 自连接(self join)
自连接(self join)是SQL语句中经常要用的连接方式,使用自连接可以将自身表的一个镜像当作另一个表来对待,从而能够得到一些特殊的数据。
示例:
在oracle的scott的schema中有一个表是emp。在emp中的每一个员工都有自己的mgr(经理),并且每一个经理自身也是公司的员工,自身也有自己的经理。
下面我们需要将每一个员工自己的名字和经理的名字都找出来。这时候我们该怎么做呢?
如果我们有两张这样的表分别教worker和mgr,那么我们就很好写SQL语句。
Select worker.name,
Mgr.name
From worker,mgr
Where worker.id = mgr.id;
但现在我们只有一张emp表。所以我们可以采用自连接。自连接的本意就是将一张表看成多张表来做连接。我们可以这样来写SQL语句:
1 SQL> select work.ename worker,mgr.ename managerfrom scott.emp work, scott.emp mgr
2 2 where work.mgr = mgr.empno(+)
3 3 order by work.ename;
4 WORKER MANAGER
5 ---------- ----------
6 ADAMS SCOTT
7 ALLEN BLAKE
8 BLAKE KING
9 CLARK KING
10 FORD JONES
11 JAMES BLAKE
12 JONES KING
13 KING --此处右表不足用Null填充
14 MARTIN BLAKE
15 MILLER CLARK
16 SCOTT JONES
17 SMITH FORD
18 TURNER BLAKE
19 WARD BLAKE
已选择14行。
- Oracle的连接详解(左连接、右连接、全连接...)
- oracle 左连接 右连接 全连接
- ORACLE 左连接/右连接/全连接
- oracle的左连接右连接全连接
- Oracle 中的JOIN(左连接、右连接、全连接)
- Oracle的左连接、右连接、(+)
- oracle连接(内连接,左连接,右连接,全连接,交叉连接)
- oracle连接(内连接,左连接,右连接,全连接,交叉连接)
- Oracle 内连接,左连接 右连接 全连接
- Oracle 的四种连接-左外连接、右外连接、内连接、全连接
- Oracle 的四种连接-左外连接、右外连接、内连接、全连接
- Oracle 的四种连接-左外连接、右外连接、内连接、全外连接
- SQL连接方式(左连接、右连接、全连接)
- SQL连接方式(左连接、右连接、全连接)
- SQL连接方式(左连接、右连接、全连接)
- 【Oracle】多表连接查询——内连接、左连接、右连接、全连接(外连接)、完全连接
- Oracle 左连接,右连接,全连接,sql实例
- Oracle左外连接、右外连接、全外连接
- ios实现qq空间飘落的花瓣(swift—粒子发射器)
- 省份-城市-区/县 下拉选
- 【十八掌●武功篇】第八掌:HBase之Shell
- Leetcode_189 Rotate Array
- Centos7及以下版本防火墙的打开关闭和查看开放端口
- Oracle的连接详解(左连接、右连接、全连接...)
- 第十二周——项目一最短路径(3)Dijkstra算法的验证(从一个顶点到其余各顶点的最短路径)
- 支付宝的服务器端的开发流程
- 【第十一周】项目4
- fstream,sstream的学习记录(备忘)
- FPGA 学习笔记
- redis数据备份,迁移
- 第十一周——项目一—图及其存储结构(邻接矩阵、邻接表)算法库
- ios获取网络请求失败相关数据