《SQL高级应用和数据仓库基础（MySQL版）》学习笔记 ·009【多表连接查询】

一、多表连接

1、应用场景

常常用于主表和从表的连接上。

• 对于并发量特别巨大的表或数据库，建议取消主从关系，也就是不设置外键。
• 阿里规范：数据库设计时，可以使用主从关系，但是建立物理数据库时，必须删除。

2、等值连接

格式

select 列名列表
from  表1, 表2
where 表1.列1 = 表2.列2[ and 条件表达式];

使用示例

select
	empno, ename, job, mgr, hiredate, sal, comm,
	d.deptno, dname, loc
from emp e, dept d
where
	e.deptno = d.deptno
	and job = 'CLEAR'
	and dname = 'RESEARCH';

注意点

• 没有任何条件的多表查询，结果将会是所有被查询列的笛卡尔积

3、非等值连接

使用示例

select
	e.ename, e.sal, s.grade
from emp e, salgrade s
where e.sal between s.losal and s.hisal;

4、自连接

应用场景

往往用于树形表（用来描述目录或类别）

eg：

id	name	fid
$-1$	药品目录	$-1$
$1$	西药	$-1$
$2$	中药	$-1$
$3$	抗生素	$1$
$4$	青霉素	$3$

使用示例

select
	e.empno, e.ename, e.job, e.hiredate, e.sal, e.comm, e.mgr,
	m.ename as MGR_Name
from emp e, emp m
where e.mgr = m.empno;

5、标准连接

(1).交叉连接

说明

cross join

查询的结果即为各列的笛卡尔积

使用示例

select ename, dname
from emp
cross join dept;

-- 多表查询的效果与上述语句效果相同
select ename, dname
from emp, dept;

(2).自然连接

说明

natural join

自然连接是对两个表之间相同名字和数据类型的列进行的等值连接，查询的效果相当于等值连接

注意点

• 如果两个表之间相同名称的列的数据类型不同，则会产生错误

使用示例

select
	empno, ename, job, mgr, hiredate, sal, comm,
	d.deptno, dname, loc
from emp e
natural join dept d;

-- 多表查询的效果与上述语句效果相同
select
	empno, ename, job, mgr, hiredate, sal, comm,
	d.deptno, dname, loc
from emp e, dept d
where
	e.deptno = d.deptno

(3).using子句连接

使用示例

-- 用using子句指定连接与(2)中示例效果相同
select
	empno, ename, job, mgr, hiredate, sal, comm,
	d.deptno, dname, loc
from emp e
join dept d using (deptno);

(4).on子句连接

使用示例

-- 用on子句指定连接与(2)中示例效果相同
select
	empno, ename, job, mgr, hiredate, sal, comm,
	d.deptno, dname, loc
from emp e
join dept d on e.deptno = d.deptno;

-- 三表查询
select e.empno, e.ename, d.dname, m.ename
from emp e
join dept d on e.deptno = d.deptno
join emp m on e.mgr = m.empno;

-- 可以用外连接弥补没有显示“老板”的缺点

(5).外连接

说明

外连接类型	说明
left	左表列的数据全部显示，其余表无数据列显示null
right	右表列的数据全部显示，其余表无数据列显示null，实际上往往改写为left

使用示例

-- 左外连接示例
select
	e.empno, e.name,
	d.deptno, d.dname
from emp e
left join dept d on e.deptno = d.deptno

-- 右外连接示例
select
	e.empno, e.name,
	d.deptno, d.dname
from emp e
left join dept d on e.deptno = d.deptno

-- 查询没有员工的部门信息
select d.deptno, dname, loc
from dept d
left join emp e on d.deptno = e.deptno
where e.deptno is null;

-- 查询存在员工的部门信息
select distinct d.deptno, dname, loc
from dept d
join emp e on d.deptno = e.deptno;

-- 显示所有员工的[编号、项目、领导姓名、部门名称]
select e.empno, e.ename, d.dname, m.ename
from emp e
left join dept d on e.deptno = d.deptno
left join emp m on e.mgr = m.empno;

注意点

• 连接查询通常建议使用关键字distinct来消除重复行
• 注意是否需要连续left join或者连续right join

二、知识点补充

1、自动行号

格式

select
	(@id := @id + 1) autoline, 列名列表
from 表名, (select @id := 0) t;