多表连接查询

多表连接查询

1 笛卡尔积：交叉连接

1.1 笛卡尔积现象

如下实验：

select * from emp;
select * from dept;

create table emp_test as select e.ename,e.deptno,d.dname from emp e,dept d;
select count(*) from emp_test;

1.2 笛卡尔积触发的条件

① 连接条件被遗漏

② 连接条件不正确

③ 所有表中的行互相连接

1.3 笛卡尔积避免方法

在查询语句后加入where正确有效的连接条件

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

 

2 多表连接类型

等连接

非等连接

外连接

自连接

 

3 等连接

(1) 传统oracle连接语法：

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

(2) SQL1999语法（标准SQL）： 

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

 

4 非等连接

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

 

5 外连接

5.1 左外连接

需求：列出所有员工及其对应部门信息， 包括没有部门的员工

(1) 传统oracle连接语法：

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

(2) SQL1999语法（标准SQL）：

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

 

5.2 右外连接

需求： 列出所有部门及其对应的员工信息， 包括没有员工的部门

(1) 传统oracle连接语法：

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

(2) SQL1999语法（标准SQL）：

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

 

5.3 全外连接

需求： 查询员工对应的部门信息， 包括没有部门的员工和没有员工的部门

select e.empno,e.ename,e.sal,d.deptno,d.dname from emp e full join dept d on (e.deptno=d.deptno);

 

6 自连接

表里的从属关系：员工的经理号 = 经理的员工号

需求： 查询员工的姓名以及员工的经理姓名

select e.empno,e.ename,e.mgr,m.empno mgr_empno,m.ename mgr_ename from emp e,emp m where e.mgr=m.empno;

 

7 总结

多表连接查询类型：等值连接、 不等值连接、 外连接、 自连接

笛卡尔积： 忽略了连接条件

 

左外连接： 列出所有员工的部门信息， 包括没有部门的员工

e.deptno=d.deptno(+);                                              传统 oracle 连接语法

left (outer) join dept d on (e.deptno=d.deptno);        SQL:1999 连接语法

右外连接： 列出所有部门的员工信息， 包括没有员工的部门

e.deptno(+)=d.deptno;                                              传统 oracle 连接语法

right (outer) join dept d on (e.deptno=d.deptno);     SQL:1999 连接语法

全外连接

full (outer) join dept d on (e.deptno=d.deptno);

【注意】

同一个外连接查询需求，使用左外连接或者右外连接都可以实现

左外连接是把左边表里数据显示完整，包括不符合连接条件的行

右外连接是把右边表里数据显示完整，包括不符合连接条件的行

 

如何区分左、 右连接？

传统 oracle 连接语法看 where 子句中比较符，标准 SQL 看 from 子句中 join