Oracle入门基础四（多表连接）

Oracle入门基础四（多表连接）

———————————————————————-

– Oracle自有连接语法： N张表连接，一定有N-1个连接条件
– 等值连接
– 非等值连接
– 外部连接

– 内部连接（自身连接）

---

–笛卡尔积： 一般出现在缺省连接条件 或 连接条件失效

select * from emp,dept;--员工表15条数据  部门表4条数据  = 15*4 = 60

---

–等值连接：等号连接表

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

1.查询所有员工编号，姓名，部门编号，工作地点

select empno,ename,deptno,loc from emp,dept where emp.deptno = dept.deptno;--未明确定义的列  

select emp.empno,emp.ename,emp.deptno,dept.loc from emp,dept where emp.deptno = dept.deptno; 
--指定列名使用表名作为前缀，效率高 

–使用AND运算符增加其它查询条件
1.现在只想查询工作地点在NEW YORK的员工编号，姓名，部门编号，工作地点

select * from emp,dept where emp.deptno = dept.deptno and lower(dept.loc)=lower('new york');

select * from emp,dept where emp.deptno = dept.deptno and dept.loc='NEW YORK';--效率高：表与表之间先等值连接，且数据量多的表关联条件优先写
select * from emp,dept where dept.loc='NEW YORK' and emp.deptno = dept.deptno;--效率低

–使用表别名

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

–多表连接

select c.name, c.custid, o.custid, o.ordid, i.ordid, i.itemid
  from customer c, ord o, item i
 where c.custid = o.custid
   and o.ordid = i.ordid;

---

–非等值连接：非等号连接表

1.查询员工姓名、薪资、薪资等级

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

---

–外部连接

1.查询没有雇员工作的部门

select * from emp e,dept d where e.deptno(+) = d.deptno; --emp从表（增加万能空行）  dept主表（数据全部显示）

2.查询没有部门的雇员

select * from emp e,dept d where e.deptno = d.deptno(+); --dept从表（增加万能空行）  emp主表（数据全部显示）

---

–内部连接(自身连接)

1.查询每个员工的姓名和直接上级姓名

select e.empno 员工编号,e.ename 员工姓名,m.empno 经理编号,m.ename 经理姓名 from emp e,emp m where e.mgr = m.empno(+);

———————————————————————-

– SQL99标准连接语法： N张表连接，一定有N-1个连接条件
– 交叉连接
– 自然连接
– using子句

– on子句： 左外连接 右外连接 全外连接

---

–cross join 交叉连接:会产生笛卡尔积

select * from emp cross join dept;
select * from emp,dept;--同上

–natural join自然连接：自动匹配等值连接的列
– 1.自动匹配名称相同，数据类型相同的列
– 2.等值连接列仅显示一次

select * from emp natural join dept;
select * from emp,dept where emp.deptno=dept.deptno;--同上

–using子句
– 注意：
1.using子句中的列不能使用表名或表别名作为前缀
2.using子句不能与natural join同时使用

select * from emp join dept using(deptno);

–on子句

select * from emp e join dept d on (e.deptno = d.deptno);

–on子句多表连接

select *
  from emp e
  join dept d on (e.deptno = d.deptno)
  join emp m on (e.mgr = m.empno);

–left outer join 左外连接

select * from emp left outer join dept on (emp.deptno = dept.deptno);--左边emp为主表（显示所有数据）  右边dept为从表（增加万能空行）
select * from emp,dept where emp.deptno = dept.deptno(+);--同上      --emp为主表（显示所有数据）  dept为从表（增加万能空行）

–right outer join 右外连接

select * from emp right outer join dept on (emp.deptno = dept.deptno);--右边dept为主表（显示所有数据）  左边emp为从表（增加万能空行）
select * from emp,dept where emp.deptno(+) = dept.deptno;--同上       --dept为主表（显示所有数据）  emp为从表（增加万能空行）

–full outer join全外连接

select * from emp full outer join dept on (emp.deptno = dept.deptno);
--select * from emp,dept where emp.deptno(+) = dept.deptno(+);--错误  语法错误