连接查询
连接查询分类
1.交叉连接
2.等值连接
3.内连接
4.外连接
左外连接
右外连接
全连接
5.自然连接
1.交叉连接
1.交叉连接
需求: 查询出员工的所有信息 和 部门的信息
select * from emp; -- 15条记录
select * from dept; -- 4条记录
select e.*,d.* -- 60条记录
from emp e, dept d
-- 这里交叉连接的结果是一个笛卡尔乘积
1.交叉连接会得到一个非常大的结果集
2.交叉连接得到的结果并不是我们希望看到的,有很多不需要的数据
交叉查询的问题:
1.会产生很大的结果集(15 * 4 ) 60 性能低
2. 结果集 有一些数据对我们没意义
2.等值连接
2.等值连接: 在交叉连接的基础上,加上条件,这个条件就是 "外键"关联的两张表的字段
select e.*,d.* -- 60条记录
from emp e, dept d
添加 条件 加关联字段 deptno
where e.deptno = d.deptno;
-- 还是会得到一个很大的结果集
-- 这个结果集一般是多少条? 100 200 --> 至少 100 条 4 条 15条 -- 至少15条记录
-- 隔壁老王和财务部的结果消失了? -- 可以考虑外连接
3.内连接
3.内连接 (连接查询标准的写法(a inner join b on (关联字段))) 把两边不满足条件不保留
select e.*,d.* -- 15条记录
from emp e inner join dept d
on e.deptno = d.deptno;
-- 内连接会获取到每一条记录去和另外一张表的每一条记录去匹配,满足on后面条件的就保留,依次类推
解决了查询 结果集 数据太多的问题
select * from dept;
问题: 1. 数据补全 (一些数据不全的不显示)
4.外连接
左外连接: 把左边不满足条件的数据保留(把左边表所有数据查询出来)
右外连接: 把右边不满足条件的数据保留(把右边表所有数据查询出来)
全连接: 把两边不满足条件的数据保留
左外连接
select e.*,d.*
from emp e left outer join dept d
on e.deptno = d.deptno;
右外连接
select e.*,d.*
from emp e right outer join dept d
on e.deptno = d.deptno;
全外连接 保留a full join b on 关联字段
select e.*,d.*
from emp e full outer join dept d
on e.deptno = d.deptno;
select * from emp e full join dept d on e.deptno = d.deptno;
注意: 全外连接在某些数据库中不支持
可以使用 union 关键字解决
select e.*,d.*
from emp e left outer join dept d
on e.deptno = d.deptno
union
select e.*,d.*
from emp e right outer join dept d
on e.deptno = d.deptno;
外连接中不满足条件的数据会填充 null
外连接有主次之分
左连接,那么左表是主表
右连接,右表是主表
oracle 方言查询:
对于外连接,也可以使用“(+)”来表示。但须注意:
1. (+)操作符只能出现在where子句中,并且不能与outer join语法同时使用
2. 当使用(+)操作符执行外连接时,如果在where子句中包含有多个条件,则必须所有条件中都包含(+)操作符
3. (+)操作符只适用于列,而不能用在表达式上
4. (+)操作符不能与or和in操作符一起使用
5. (+)操作符只能用于实现左外连接和右外连接,而不能用于实现完全外连接
用(+)来实现,这个+号可以这样来理解:+ 表示补充,即哪个有+,这个表就是匹配表,另外的则是基础表。
例子
select t1.c1,t2.c2 from t1,t2 where t1.c=t2.c(+) --左连接
select t1.c1,t2.c2 from t1,t2 where t1.c(+)=t2.c --右连接
左外连接:
select * from emp e,dept d where e.deptno = d.deptno(+);
右外连接:
select * from emp e,dept d where e.deptno(+) = d.deptno;
99sql写法: [JOIN 表名称 ON(条件)|USING(字段)]
select * from emp e left join dept d using(deptno);
5.自然连接
自然连接: 会多表联查 自己给我们关联数据
select e.ename,d.dname
from emp e natural join dept d;
总结:在编写连接查询的时候优化问题
1.缩小数据源
2.数据量较小的表放在连接的左边,数据量大的数据放在连接的右边
3.使用内连接或者外连接,减少使用 交叉连接或者等值连接
连接查询是横向连接两张表,一般都是通过外键连接
合并结果集是纵向连接。
经验总结:
如果写sql的时候,先要确定你要显示的数据在哪几张表中,如果有多表,可以通过连接查询将多个表的字段联合,然后通过条件筛选掉一些数据
1.确定需要显示的列
2.确定连接的表
3.确定条件