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(+);--错误 语法错误