oracle多表查询

多表查询基本语法

笛卡尔积在SQL中的实现方式既是交叉连接(Cross Join)。所有连接方式都会先生成临时笛卡尔积表，笛卡尔积是关系代数里的一个概念，表示两个表中的每一行数据任意组合。

-- 笛卡尔积 
select * from emp, dept;

<喎?http://www.Bkjia.com/kf/ware/vc/" target="_blank" class="keylink">vcD4KCjxwcmUgY2xhc3M9"brush:sql;">-- 使用公共字段，去掉笛卡尔积 select * from emp, dept where emp.deptno = dept.deptno;

-- 查询出每个雇员的姓名，工作，雇员的直接上级领导的姓名
-- 在emp表中的MGR表示一个雇员的上级领导的编号，如果现在要想查询一个雇员的上级领导姓名，则需要用emp表与emp表进行自连接操作
select e.ename, e.job, s.ename from emp s, emp e where s.empno = e.mgr; 

-- 查询出每个雇员的姓名，工作，雇员的直接上级领导的姓名，雇员所在的部门名称
select e.ename, e.job,s.ename mgr_name, d.dname from emp e, emp s, dept d where e.mgr = s.empno and e.deptno = d.deptno;

-- 查询出每个雇员的姓名，工资，部门名称，工资在公司的等级及其领导的姓名，领导的工资，以及领导所对应的等级
SELECT e.ename, e.sal, d.dname,
decode(g.grade,5,'第一等工资',4,'第二等工资',3,'第三等工资',2,'第四等工资',1,'第五等工资')
e_grade, s.ename mgr_name, s.sal mgr_sal,
decode(g2.grade,5,'第一等工资',4,'第二等工资',3,'第三等工资',2,'第四等工资',1,'第五等工资') m_grade
FROM emp e,dept d,salgrade g,emp s,salgrade g2
WHERE (e.deptno=d.deptno) AND (e.sal BETWEEN g.losal AND g.hisal)
AND (s.empno=e.mgr) AND (s.sal BETWEEN g2.losal AND g2.hisal);

左右连接

-- 利用emp 和 dept 做一个连接查询，查询结果包括雇员编号，雇员姓名，部门编号，部门名称，部门所在位置
select e.empno, e.ename, d.deptno, d.dname, d.loc
from emp e,dept d
where e.deptno = d.deptno;

-- 通过观察不难发现，在以上结果中部门编号40没有出现,这是因为在进行普通连接时两边都有的才出现在结果中,在emp表中没有部门40,所以结果中也就没有40
select e.empno, e.ename, d.deptno, d.dname, d.loc
from emp e, dept d
where e.deptno(+) = d.deptno;

部门40就出现在结果中了.此时我们使用的是右连接。

(+)在=左边 表示右连接
(+)在=右边 表示左连接

左连接以左表为基准，右连接以右表为基准

-- 左连接以左表为基准，右连接以右表为基准
select e.empno, e.ename, d.deptno, d.dname, d.loc
from emp e, dept d
where e.deptno = d.deptno(+);

-- 查询雇员的编号，姓名，及其领导的编号，姓名
select e.empno, e.ename, s.empno, s.ename
from emp e, emp s
where e.mgr = s.empno;

-- 但我们经查询知道emp表中应该有14条记录,在以上结果中缺少了KING,因为KING是最高领导，所以他的领导编号为空，故没办法查询出来.为了显示出来，我们用左/右连接来实现
select e.empno, e.ename, s.empno, s.ename
from emp e, emp s
where e.mgr = s.empno(+);

-- 左右连接的连接方式不是固定的，具体采用左连接还是右连接取决于基准条件在等式的左边还是右边，上面的左连接把基准条件放在右边，就变为了右连接
select e.empno, e.ename, s.empno, s.ename
from emp e, emp s
where s.empno(+) = e.mgr;

得到的同样是上图的结果值，这里就不重复贴图了。

SQL:1999SQL定义

语法格式

SELECT table1.column,table2.column

FROM table1 [CROSS JOIN table2]|

[NATURAL JOIN table2]|

[JOIN table2 USING(column_name)]|

[JOIN table2 ON(table1.column_name=table2.column_name)]|

[LEFT|RIGHT|FULL OUTER JOIN table2 ON (table1.column_name=table2.column_name)]

-- 交叉连接(CROSS JOIN):迪卡尔积运算
select * from emp cross join dept;

select * from emp cross join dept where emp.empno = 7369;

-- 自然连接 NATURAL JOIN
select * from emp natural join dept;

-- 根据结果可以发现，自然连接按公共字段相等进行连接，并且产生的结果会自动消除重复的列
select * from emp e, dept d
where e.deptno = d.deptno;

-- USING(column_name) 用于指定两个表之间的连接字段
select * from emp join dept using(deptno);

-- ON(tab1.column_name=tab2.column_name) 用于指定两表的连接条件
select * from emp join dept on emp.deptno = dept.deptno;

-- LEFT[OUTER] JOIN 左连接 OUTER可有可无
select e.empno, e.ename, d.deptno, d.dname, d.loc
from emp e left join dept d
on e.deptno = d.deptno;
select e.empno, e.ename, d.deptno, d.dname, d.loc
from emp e left outer join dept d
on e.deptno = d.deptno;

-- RIGHT JOIN 右连接
select e.empno, e.ename, d.deptno, d.dname, d.loc
from emp e right join dept d
on e.deptno = d.deptno;

-- INNER JOIN 取交集
create table empbak as select * from emp where emp.empno in (7369, 7499, 7521, 7566, 7654, 7698);
select e1.empno, e1.empno, e1.job, e1.mgr, e1.hiredate, e1.sal, e1.comm, e1.deptno 
from emp e1 inner join empbak e2 on e1.empno = e2.empno;

-- FULL OUTER JOIN 取并集
update empbak e set e.empno = 8369 where e.empno = 7369;
update empbak e set e.empno = 8499 where e.empno = 7499;
update empbak e set e.empno = 8521 where e.empno = 7521;
select * from emp e full join empbak s on e.empno = s.empno;

-- 对于没有匹配的记录，则会以null做为值
select * from emp e full join empbak s on e.empno = s.empno
where e.empno is not null and s.empno is not null;

-- UNION 取并集 去掉重复记录
select * from emp
union
select * from empbak;

-- UNION ALL 取并集 不去除重复记录
select * from emp
union all
select * from empbak;

最下面的三条记录是重复记录，在上面的union连接中，多余的三条重复记录是被去掉的。