Oracle多表连接查询举例

--多表查询，单张表中的数据不齐全，不方便阅读
--1992标准,select 字段列表 from 表列表 where 连接条件（避免笛卡尔积）and 过滤条件
--如果有N张表，应该至少有N-1个用于避免笛卡尔积的连接条件
--如果两个表中有同名字段，需要在字段前加上表名区分。加上可以提高效率
--可以给表起别名
select e.ename,d.dname 
from emp e,dept d
where e.deptno=d.deptno;

--故意制造笛卡尔积
--面包店，tab1保存口味选择，tab2保存装饰的水果选择
select t2.n2||t1.n1||'蛋糕'
from tab1 t1,tab2 t2;

--练习，列出工资在2000以上的员工的姓名，工资，部门名称，部门地址
SELECT e.ename,e.sal,d.dname,d.loc
from emp e,dept d
where e.deptno=d.deptno
and e.sal>2000;

--查询学生姓名，科目名称，上课老师
--当查询数据不需要某一张表时，他也可能作为联系表被用到
select st.s_name,su.su_name,t.t_name
from student st,subject su,teacher t,score sc
where su.t_id=t.t_id and st.s_id=sc.s_id and sc.su_id=su.su_id;

--查询分数大于80分的学生姓名，科目名称，分数，上课老师，上课时间 
select st.s_name,su.su_name,sc.sco,t.t_name,su.su_time
from student st,subject su,score sc,teacher t
where st.s_id=sc.s_id and su.su_id=sc.su_id and t.t_id=su.t_id
and sc.sco>80;

--非等值连接，获取处于某一个区间所对应的值
--查询员工姓名，工资，工资等级
select e.ename,e.sal,s.grade
from emp e,salgrade s
where e.sal between s.losal and s.hisal;

--内连接：符合连接条件的数据，才会显示出来
--查询员工姓名和所属部门名称
select e.ename,d.dname
from emp e,dept d
where e.deptno=d.deptno;
--查询所有员工，以及他们的部门
--外连接，可以将某一个表中的数据完全显示，另一个表补全可以连接的数据，没有连接的为空
--(+)出现在哪边，哪边是补充的表
select e.ename,d.dname
from emp e,dept d
where e.deptno(+)=d.deptno;

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

--自连接，表中的数据自己跟自己比较，通过别名把一张表当做两张表使用
--查询比某个员工工资高的人

select e1.ename,e1.sal
from emp e2,emp e1
where e2.ename='MILLER' and e2.sal<e1.sal

--查询上司叫做KING的所有员工的姓名
select e1.ename
from emp e1,emp e2
where e2.ename='KING' and e2.empno=e1.mgr;

--1999标准的语法
--产生笛卡尔积
select ename,dname
from emp
cross join dept;

--内连接
select ename,dname
from emp e
join dept d on e.deptno=d.deptno;
--查询学生姓名，科目名称，上课老师
select st.s_name,su.su_name,t.t_name
from student st
join score sc
on st.s_id=sc.s_id
join subject su
on su.su_id=sc.su_id
join teacher t
on su.t_id=t.t_id


--外连接
--左连接，以左表为主，右表补充
select ename,dname
from emp e
left join dept d on e.deptno=d.deptno;

--右连接，以右表为主，左表补充
select ename,dname
from emp e
right join dept d on e.deptno=d.deptno;

--全连接
select ename,dname
from emp e
full join dept d on e.deptno=d.deptno;