多表联合查询
SQL9
- 笛卡儿积(a*b):将多个表的数据进行一一对应,所得的结果为多表的笛卡儿积
结果的数量为多表之积
select * from emp,dept where emp.deptno = dept.deptno
- 等值连接筛选:先做表的笛卡儿积,然后筛选,筛选条件为等值筛选
可以在select 子句中使用字段获取数据,但是效率较低
如果是公共字段,必须声明表名 - 不等值连接
select * from emp as e,salgrade as s where e.sal>=s.losal and e.sal<=s.hisal
- 自连接
查询员工的姓名、工作、薪资、及上级领导姓名
select e1.ename,e1.job,e1.sal,e2.ename from emp e1,emp e2 where e1.mgr = e2.empno
注:自己与自己做笛卡儿积
- 外连接:
左外连接:可以看到左表为空的内容
select * from emp e,dept d where e.deptno=d.deptno(+)
右外连接:可以看到右表为空的内容
select * from emp e,dept d where e.deptno(+)=d.deptno
SQL99
-
笛卡儿积:使用 cross 和 join 关键字
select 内容 from 表名 cross join 表名
select * from emp cross join dept -
筛选
- 自然连接:使用关键字natural join
- 使用:
select * from emp natural join dept
- 特点:底层为笛卡儿积,然后按照同名同值字段自动进行筛选
- 缺点:如果想按照字段名不同,但是值相同进行筛选怎么办
- 如果只想按照部分字段筛选怎么办
- 解决1:使用using关键字
作用:指明使用指定字段对联合查询结果进行等值筛选
注意:指定字段必须是两表同名同值字段
使用:select * from emp inner join dept using (deptno)
- 解决1:使用using关键字
- 解决2:使用on关键字进行自定义连接条件筛选(等值、不等值筛选)
注:普通筛选条件使用where进行筛选,不要使用on 。好处,sql语句的阅读行
使用:select 内容 from 表名 inner join dept on emp.deptno=dept.deptno
- 如果只想按照部分字段筛选怎么办
- 使用:
- 自然连接:使用关键字natural join
-
外连接:
- 左外连接
select * from emp left outer join dept on emp.deptno = dept.deptno - 右外连接
select * from emp right outer join dept emp.deptno=dept.deptno - 全外连接
select * from emp full outer join dept emp.deptno = dept.deptno
- 左外连接
-
自连接:
查询员工及其上级领导姓名
select * from emp e1 inner join emp e2 on e1.msr=m2.empno
SQL92实现:查询员工信息及部门名称及所在城市名称
特点:易于书写、难于阅读
使用:
select * from emp e,dept d,city c where (e.deptno=d.deptno and d.loc=c.cid and sal>2000) or (e.deptno=d.deptno and d.loc=c.cid and comm is not null)
SQL99实现:查询员工信息及部门名称及所在城市名称
特点:难于书写,易于阅读
使用:
select * from emp e inner join dept d on e.deptno = d.deptno
inner join city c on d.loc = c.cid
where sal>2000 or comm is not null
子查询:
使用时机:当查询的筛选条件不明确时,考虑使用子查询
- 单行子查询
- 多行子查询
单行子查询:
- 使用时机:筛选条件不明确,查询结果仅有一个 数据
- 注:where语句中允许出现查询语句,该查询语句被称为子查询
- 使用:
select * from emp where sal > (select sal from emp where ename='CLARK')
多行子查询:
- 使用动机,子查询结果只有一个字段,但是有n个值,考虑使用多行查询
- 关键字:any 任意 、all 所有 、in 任意存在,相当于=any
- 使用:
select * from emp where sal > any (select sal from emp where job='CLERK')