3.3 在两个表中查找共同行
首先创建视图
create or replace view v1 as
select ename, job, sal
from emp
where job = 'clerk'
由于此视图只包含了ename, job, sal,但在查询时还需要获得empno,deptno
如果想获得正确的结果,必须按照所有必要的列进行联接。或者,可以使用集合操作INTERSECT返回两个表的交集(
共同的行)
1、普通联接
select e.empno, e.ename, e.job, e.sal, e.deptno
from emp e, v1
where e.ename = v1.ename and e.job = v1.job and e.sal = v1.sal
2、通过JOIN子句执行联接
select e.empno, e.ename, e.job, e.sal, e.deptno
from emp e inner join v1 on (
e.ename = v1.ename and e.job = v1.job and e.sal = v1.sal
)
3、使用集合操作INTERSECT
select empno, ename, job, sal, deptno
from emp
where (ename, job, sal) in (
select ename, job, sal from emp
intersect
select ename, job, sal from v1
)
3.6 向查询中增加联接而不影响其它联接
例如:要获得所有的员工信息、他们的工作部门的地点以及所获得的奖励
select e.ename, d.loc, eb.received
from emp e, dept d, emp_bonus eb
where e.deptno = d.deptno and e.empno = eb.empno
这样的查询结果,如果员工没有奖金,则无法显示该员工的信息,那么,无论有无奖金都要显示员工信息,就要使用到
外部链接
select e.ename, d.loc, eb.received
from emp e join dept d
on (e.deptno = d.deptno)
left outer join emp_bonus eb
on (e.empno = eb.empno)
order by 2
Oracle9i的表示方式
select e.ename, d.loc, eb.received
from emp e, dept d, emp_bonus eb
where e.deptno = d.deptno
and e.empno = eb.empno (+)
order by 2
第四章 插入更新与删除
4.11 合并记录
merge参考 http://jinjiabao.iteye.com/blog/481477