Oracle-6 多表连接

————————————————————————–

Oracle自有写法：等值连接、非等值连接、外部连接、自身连接

---

笛卡尔积 ： 连接条件被省略 或 连接条件无效
select * from emp,dept;–56行 = 14*4

---

等值连接 : N张表关联，必须有N-1个连接条件，否则产生笛卡尔积

查询所有员工编号，姓名，部门编号，工作地点
select * from emp,dept where emp.deptno = dept.deptno;
select emp.,dept. from emp,dept where emp.deptno = dept.deptno;

使用AND运算符增加其它查询条件
现在只想查询工作地点在NEW YORK的员工编号，姓名，部门编号，工作地点
select empno,ename,emp.deptno,loc from emp,dept where emp.deptno = dept.deptno and loc = ‘NEW YORK’;

限制歧义列名
使用表名作为前缀
select emp.empno,emp.ename,emp.deptno,dept.loc from emp,dept where emp.deptno = dept.deptno and loc = ‘NEW YORK’;

使用表的别名
select e.empno,e.ename,e.deptno,d.loc from emp e,dept d where e.deptno = d.deptno and d.loc = ‘NEW YORK’;–效率高点
select e.empno,e.ename,e.deptno,d.loc from emp e,dept d where d.loc = ‘NEW YORK’ and e.deptno = d.deptno;–效率低点

---

非等值连接

查询每个员工的姓名，工资，工资等级
select e.ename,e.sal,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal;
select e.ename,e.sal,s.grade from emp e,salgrade s where e.sal>=s.losal and e.sal<=s.hisal;

---

外部连接

查询所有雇员姓名，部门编号，部门名称，包括没有员工的部门40也要显示出来
select e.ename,d.deptno,d.dname from emp e,dept d where e.deptno(+) = d.deptno;
+所在的emp表为从表，增加一个万能空行 dept表为主表，会显示表中所有的数据

查询所有雇员姓名，部门编号，部门名称，包括没有部门的员工也要显示出来
select e.ename,e.deptno,d.dname from emp e,dept d where e.deptno = d.deptno(+);–emp主表 dept从表

---

自身连接

查询每个员工的姓名和直接上级姓名
select e.ename 员工姓名,mgr.ename 上级姓名 from emp e,emp mgr where e.mgr = mgr.empno;

——————————————————————————–

SQL99标准

---

交叉连接(cross join)：会产生笛卡尔积
select * from emp cross join dept;
select * from emp,dept;–同上

自然连接（natural join）
– 自动连接列名相同、数据类型相同的列
– 连接的列仅显示一次，默认显示在第一列
select * from emp natural join dept;

using子句:指定连接条件
–注意： 列名不允许表名或表别名修饰
NATURAL JOIN子句和USING子句是相互排斥的，不能同时使用。
select * from emp join dept using(deptno);

on子句
select * from emp join dept on(emp.deptno = dept.deptno);
select * from emp,dept where emp.deptno = dept.deptno;

左外连接（left outer join）
select * from emp left outer join dept on(emp.deptno = dept.deptno);

右外连接（right outer join）
select * from emp right outer join dept on(emp.deptno = dept.deptno);

全外连接（full outer join）
select * from emp full outer join dept on(emp.deptno = dept.deptno);