SQL99多表连接查询

1. cross join(交叉连接)

交叉连接会产生一个笛卡尔积

select * from emp cross join dept; 

在笛卡尔积中, 有很多数据是无意义的, 所以需要消除, 可以通过 where 子句来消除

select * from emp cross join dept where 
emp.deptno=dept.deptno; 

可以在查询时, 给表格起别名（如果不是e.*，则后面必须直接跟from，否则会报错）

select e.*, dname, loc from emp e cross join dept d 
where e.deptno=d.deptno; 

2. natural join(自然连接)

natural join 用于针对多张表的同名字段进行等值连接

select * from emp e natural join dept d; 

特点:
a) 在自然连接时, 自动进行所有同名列的等值连接, 不需
要写连接的条件
b) 同名列只显示一列, 而且在使用时, 不能加表前缀（e.、d.、）
查询所有员工的姓名, 部门编号和部门名称

select e.ename, deptno, d.dname from emp e natural join dept d;

3. inner join(内连接)

inner可以省略不写

select * from emp inner join dept 
on emp.deptno=dept.deptno; 

4. using 子句

用来指定进行等值连接的同名字段, 针对自然连接提供的. 同名字段依然不能使用表前缀.

a) 查询 20 部门员工的姓名, 工资, 部门编号和部门名称

select e.ename, e.sal, deptno, d.dname from emp e 
natural join dept d where deptno=20; 

select e.ename, e.sal, deptno, d.dname from emp e 
join dept d using (deptno) where deptno=20;

5. on 子句

on 子句是使用非常广泛的子句, 它可以被用来指定连接的（特别是非等值连接）
条件. 用于将过滤条件和关联条件分开.

a) 查询所有员工的姓名, 工资和工资等级

select e.ename, e.sal, s.grade from emp e join 
salgrade s on e.sal>=s.losal and e.sal<=s.hisal; 

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

b) 查询 30 部门员工的编号, 姓名, 部门名称和所在地

select e.empno, e.ename, d.dname, d.loc 
from emp e 
join dept d 
on e.deptno=d.deptno 
where d.deptno=30; 

c) 查询所有员工的姓名, 部门名称, 工资及工资等级

N 张表连接, 至少需要 N-1 个连接条件

select e.ename, d.dname, e.sal, s.grade 
from emp e 
join dept d 
on e.deptno=d.deptno 
join salgrade s 
on e.sal between s.losal and s.hisal;

6. 外连接

外连接除了能显示满足连接条件的数据以外, 还用于显示不
满足连接条件的数据

左外连接

left [outer] join, 表示左外连接, 可以显示左表中不满足
连接条件的数据

select e.ename, e.job, d.deptno, d.dname, d.loc 
from dept d 
left join emp e 
on e.deptno=d.deptno; 

右外连接

right [outer] join, 表示右外连接, 可以显示右表中不满
足连接条件的数据

select e.ename, e.job, d.deptno, d.dname, d.loc 
from emp e 
right join dept d 
on e.deptno=d.deptno; 

全外连接

full [outer] join, 表示全外连接, 可以显示左右两表中不满足连接条件的数据

select e1.empno, e1.ename, e2.empno, e2.ename 
from emp e1 
full join emp e2 
on e1.mgr=e2.empno 
order by e1.empno;

自连接

自连接是发生在同一个表格中的连接

a) 查询所有员工的编号, 姓名和领导的编号及姓名

select e1.empno, e1.ename, e2.empno, e2.ename 
from emp e1 
join emp e2 
on e1.mgr=e2.empno 
order by e1.empno; 

b) 查询所有员工的编号, 姓名和领导的编号及姓名, 并显示
没有领导的员工信息

select e1.empno, e1.ename, e2.empno, e2.ename 
from emp e1 
left join emp e2 
on e1.mgr=e2.empno 
order by e1.empno;

SQL92多表连接查询

1 笛卡尔积

多张表通过逗号分隔

select * from emp e, dept d; 

消除无意义数据

select * from emp e, dept d where e.deptno=d.deptno; 

2 等值连接

a) 查询 10 部门员工的姓名, 职位和部门名称

select e.ename, e.job, d.dname from emp e, dept d where 
e.deptno=d.deptno and d.deptno=10; 

3 非等值连接

a) 查询所有员工的姓名, 工资和工资等级

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

4 外连接

通过”(+)”符号实现外连接

左外连接

select e.ename, d.deptno, d.dname, d.loc 
from emp e, dept d 
where d.deptno=e.deptno(+); 

右外连接

select e.ename, d.deptno, d.dname, d.loc 
from emp e, dept d 
where e.deptno(+)=d.deptno; 

自连接

select e1.empno, e1.ename, e2.empno, e2.ename 
from emp e1, emp e2 
where e1.mgr=e2.empno(+);