Oracle 表连接(内连接、外连接(左连接、右连接)、隐式连接、表并集、表交集
一、内连接 inner join在内连接中,数据库一般会主动选择数据量少的表作为驱动表,然后循环驱动每一条数据,按照on后面的关联字段,和被驱动表举行匹配,举行数据的拼接。
SELECT T1.EMPNO,T1.JOB,T2.DEPTNO,T2.DNAME,T2.LOC
FROM EMP T1 --被驱动表
INNER JOIN DEPT T2--驱动表
ON T1.DEPTNO = T2.DEPTNO
https://i-blog.csdnimg.cn/blog_migrate/3e7d4601fcd709d9d50627f51a8cb85c.png#pic_center
通过部分编号将员工表和部分信息表举行关联连接,得到两表合并之后的数据。
1.and 和 where 的区别
例:查出员工SCOTT所在的部分的部分编号,部分名称和所在地域
---用and过滤
select e.*,t.dname,t.loc
from emp e inner join dept t
on e.deptno=t.deptno
and e.ename = 'SCOTT';
---where过滤
select e.*,t.dname,t.loc
from emp e inner join dept t
on e.deptno=t.deptno
where e.ename = 'SCOTT';
https://i-blog.csdnimg.cn/blog_migrate/0ae142659d56f86da002bd5911011380.png#pic_center
and为表连接前的过滤,where为表关联后的过滤
2.内关联 隐式写法(Oracle 专有)
语法:
select 查询字段
from 表1,表2
where 关联字段(此处where相当于 on)
and 过滤条件
注:虽然在隐式写法中,where 相称于 on ,但在隐式写法中不能将两者做等价使用,隐式写法只允许用where 作为两表的关联条件,用on为错误的写法。
---正确写法
select e.*,t.dname,t.loc
from emp e ,dept t
where e.deptno=t.deptno
and e.ename = 'SCOTT'
---错误写法
select e.*,t.dname,t.loc
from emp e ,dept t
on e.deptno=t.deptno --on 错误
and e.ename = 'SCOTT'
3.交叉连接–笛卡尔积式连接
[*]注意:关联查询(针对隐式写法),一定要写关联字段,否则会产生笛卡尔积(查询出来的结果会相乘);
[*]若两表的数据量大,会造成数据库服务器高负载;
比如:
SELECT * FROM EMP Ecross joinDEPT D ;-- cross join 显式交叉连接写法
SELECT * FROM EMP E ,DEPT D ;--隐式交叉连接写法
https://i-blog.csdnimg.cn/blog_migrate/306a857dca1d948051646be53b9387c2.png#pic_center
总结一下:
[*]我们的内关联求的结果集是两张表的交集(根据关联字段,互相匹配的表现出来)。
[*]一定要写关联字段,否则会产生笛卡尔积(查询出来的结果会相乘)。
[*]尺度写法中表之间的关联字段用ON,Oracle专有隐式写法中的表之间的关联字段用WHERE。
二、外连接
外连接区别与内连接:
6. 语法结构上,内连接是 JOIN,左外连接是LEFT JOIN。
7. 结果上,内连接取的是两个表的交集,左外连接取得是主表的所有信息加上从表的信息,主表的数据就算不满足关联条件也可以被查询出来,从表不匹配的表现为空。
8. 主表、从表。按照左连接的语法结构:表1就是主表,表2就是从表。
1. 左连接 left join
语法:
SELECT 要查询的字段/列
FROM 表1 ---主表
LEFT JOIN 表2 ---从表,
ON 关联字段
AND 过滤条件;
注:
9. 主表的数据就算不满足关联条件也可以被查询出来,也就是说主表的数据会全部展示
10. 从表数据,匹配的正常展示,不匹配的表现为空。
SELECT *
FROM DEPT D --主表
LEFT JOIN EMP E --从表
ON E.DEPTNO = D.DEPTNO;
https://i-blog.csdnimg.cn/blog_migrate/82eccbd1c8fb72843f22cbb312ea2c6d.png#pic_center
2. 右连接 right join
语法:
SELECT 要查询的字段/列
FROM 表1 ---从表
RIGHT JOIN 表2 ---主表,
ON 关联字段
AND 过滤条件;
右连接以表 2 为主表,表现表 2 的所有字段内容,不匹配的则表现为null。
SELECT *
FROM EMP E --从表
RIGHT JOINDEPT D --主表
ON E.DEPTNO = D.DEPTNO;
https://i-blog.csdnimg.cn/blog_migrate/58dc227b8842b076c6820f693689ade0.png#pic_center
3. 全连接 full join
全外关联 ,没有主从表,两张表互相比较,相同的部分正常表现,差别的部分互相为 NULL。
语法:
SELECT 要查询的字段/列
FROM 表1
FULL JOIN 表2
ON 关联字段
AND 过滤条件;
全外连接- -展示两张表的全部数据
SELECT e.*,t.*
FROM EMP e
FULLJOIN DEPT t
ON e.deptno = t.deptno
https://i-blog.csdnimg.cn/blog_migrate/d6b1bf0adbaec37174d90bbc9af96f5a.png#pic_center
4. Oracle外连接隐式写法
[*]左连接
SELECT *
FROM emp e,dept f
where e.deptno=f.deptno(+)-- (+)在右边,另一侧的emp表就是主表
--AND 过滤条件;--是表连接完成后的过滤
https://i-blog.csdnimg.cn/blog_migrate/6d97d6178a7a9fb9d2eb62ae2a7e157c.png#pic_center
2. 右连接
SELECT *
FROMemp e,dept f
where e.deptno(+)=f.deptno-- (+)在哪一侧,另一侧的dept表就是主表
--AND 过滤条件 --是表连接完成后的过滤
https://i-blog.csdnimg.cn/blog_migrate/8ad0978a0d37eb5f803b6c515c70fc0e.png#pic_center
5. 重点
[*]用外连接的时候需要注意:主表跟从表的取舍(要判断好到底哪张表作为主表,哪张表作为从表)。
[*]在外连接的时候。用and过滤条件不会过滤主表的信息。用where的时候,会过滤掉主表的部分信息。
[*]在用外连接连接表的时候,大部分都是用and来当做过滤条件 --重点
连接查询总结:
[*]内连接:取两张表的交集。没有主从表之分
[*]外连接:
[*]a.左外连接:关键字LEFT左边的那个表是主表(有主从表之分),表现主表的所有信息以及从表的关联得上那部分信息,从表不匹配的部分用null补全。
[*]b.右外连接:关键字RIGHT右边的那个表是主表(有主从表之分),表现主表的所有信息以及从表的部分信息,从表不匹配的部分用null补全
[*]c.全外连接full:表现两张表的相同的信息,差别的部分互相用null补全,两张表的数据就算有关联不上的也可以被查询出来,会展示出两张表的全部信息。
三、自连接
内连接自关联 与 外连接自关联的差异。
例:通过上级领导编号举行表自连接关联。
[*]内连接-表自关联
SELECT T1.EMPNO,T2.EMPNO,T2.ENAME--上级领导员工编号,姓名
FROM EMP T1 --取员工的数据
INNER JOIN EMP T2 --取领导数据
ON T1.MGR = T2.EMPNO--用内连接会过滤掉了董事长那条数据
https://i-blog.csdnimg.cn/blog_migrate/a938090594144b5c28bb41326a7b6d4f.png#pic_center
2. 外连接-表自关联
SELECT T1.EMPNO,T2.EMPNO,T2.ENAME--上级领导员工编号,姓名
FROM EMP T1 ---取员工的数据
LEFT JOIN EMP T2 ---取领导数据
ON T1.MGR = T2.EMPNO --改为左连接可得到董事长那条数据
https://i-blog.csdnimg.cn/blog_migrate/a94d903801b53dc68d2009178e188370.png#pic_center
四、表并集 UNION
1. UNION ALL
在emp表中共有14条数据,表全并集后,得到28条数据。
SELECT * FROM EMP--注意上下2个SQL结果集字段顺序,数量要保持一致
UNION ALL
SELECT * FROM EMP;
https://i-blog.csdnimg.cn/blog_migrate/9d2891414f58e6afcab5a9ea8eb4af5d.png#pic_center
2. UNION
SELECT * FROM EMP
UNION --拼接上下2个SQL结果集,会进行去重排序(默认是升序排序);
SELECT * FROM EMP;
https://i-blog.csdnimg.cn/blog_migrate/bf8c7a69dc851ea37f3e7544556f85d5.png#pic_center
3. UNION ALL 和 UNION 区别
[*]UNION ALL 简单拼接上下2个SQL结果集,不会举行去重排序。 --效率更高
[*]UNION 拼接上下2个SQL结果集,会举行去重排序(默认是升序排序); --效率更低
注: 上下2个SQL结果集字段次序,数量要保持同等
五、表交集 INTERSECT
表交集 INTERSECT 会返回两个查询中共有的查询记录
SELECT DEPTNO FROM EMP
INTERSECT
SELECT DEPTNO FROM DEPT;
https://i-blog.csdnimg.cn/blog_migrate/5314f0ab2a4fb62445cdcbc07263faf3.png#pic_center
六、表补集 MINUS
表补集 也可以为具有减的功能;
[*]MINUS 返回第一个查询的记录 减去 第二个查询的记录之后 剩余的记录。
[*]也可理解为取出第一个记录中有的,但是第二个记录没有的记录;
--emp表中只有部门(10、20、30),而部门表中有(10、20、30、40),故补集结果为空
SELECT DISTINCT DEPTNO FROM EMP
MINUS
SELECT DEPTNO FROM DEPT;
--dept部门表中只有部门(10、20、30、40),而emp员工表中有(10、20、30),故补集结果为 40
SELECT DEPTNO FROM DEPT
MINUS
SELECT DISTINCT DEPTNO FROM EMP;
https://i-blog.csdnimg.cn/blog_migrate/b7ae8ed3d3b2c6b0dc489b52d9680993.png#pic_center
免责声明:如果侵犯了您的权益,请联系站长,我们会及时删除侵权内容,谢谢合作!更多信息从访问主页:qidao123.com:ToB企服之家,中国第一个企服评测及商务社交产业平台。
页:
[1]