oracle多表查询

朴灿烈づ我的快乐病毒、 2022-08-01 11:16 308阅读 0赞

多表查询基本语法

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

SouthEast

  1. -- 使用公共字段，去掉笛卡尔积
  2. select * from emp, dept where emp.deptno = dept.deptno;

SouthEast 1

  1. -- 查询出每个雇员的姓名，工作，雇员的直接上级领导的姓名
  2. -- 在emp表中的MGR表示一个雇员的上级领导的编号，如果现在要想查询一个雇员的上级领导姓名，则需要用emp表与emp表进行自连接操作
  3. select e.ename, e.job, s.ename from emp s, emp e where s.empno = e.mgr; 
  5. -- 查询出每个雇员的姓名，工作，雇员的直接上级领导的姓名，雇员所在的部门名称
  6. 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;
  8. -- 查询出每个雇员的姓名，工资，部门名称，工资在公司的等级及其领导的姓名，领导的工资，以及领导所对应的等级
  9. SELECT e.ename, e.sal, d.dname,
  10. decode(g.grade,5,'第一等工资',4,'第二等工资',3,'第三等工资',2,'第四等工资',1,'第五等工资')
  11. e_grade, s.ename mgr_name, s.sal mgr_sal,
  12. decode(g2.grade,5,'第一等工资',4,'第二等工资',3,'第三等工资',2,'第四等工资',1,'第五等工资') m_grade
  13. FROM emp e,dept d,salgrade g,emp s,salgrade g2
  14. WHERE (e.deptno=d.deptno) AND (e.sal BETWEEN g.losal AND g.hisal)
  15. AND (s.empno=e.mgr) AND (s.sal BETWEEN g2.losal AND g2.hisal);

左右连接

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

SouthEast 2 

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

SouthEast 3

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

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

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

  1. -- 左连接以左表为基准，右连接以右表为基准
  2. select e.empno, e.ename, d.deptno, d.dname, d.loc
  3. from emp e, dept d
  4. where e.deptno = d.deptno(+);
  9. -- 查询雇员的编号，姓名，及其领导的编号，姓名
  10. select e.empno, e.ename, s.empno, s.ename
  11. from emp e, emp s
  12. where e.mgr = s.empno;
  17. -- 但我们经查询知道emp表中应该有14条记录,在以上结果中缺少了KING,因为KING是最高领导，所以他的领导编号为空，故没办法查询出来.为了显示出来，我们用左/右连接来实现
  18. select e.empno, e.ename, s.empno, s.ename
  19. from emp e, emp s
  20. where e.mgr = s.empno(+);

SouthEast 4

  1. -- 左右连接的连接方式不是固定的，具体采用左连接还是右连接取决于基准条件在等式的左边还是右边，上面的左连接把基准条件放在右边，就变为了右连接
  2. select e.empno, e.ename, s.empno, s.ename
  3. from emp e, emp s
  4. 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)]

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

SouthEast 5

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

SouthEast 6

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

SouthEast 7

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

SouthEast 8

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

SouthEast 9

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

SouthEast 10

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

SouthEast 11

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

SouthEast 12

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

SouthEast 13

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

SouthEast 14

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

SouthEast 15 

  1. -- UNION 取并集 去掉重复记录
  2. select * from emp
  3. union
  4. select * from empbak;

Center

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

Center 1

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

发表评论

表情：
评论列表 （有 0 条评论，308人围观）

还没有评论，来说两句吧...

相关阅读

    相关 Oracle-查询-多表连接查询

    左外连接是以join左边作为主表，右连接以join右边做为主表 外连接查询出来的结果相当于两个部分，一个部分是交集部分（相当于利用等值活非等值连接查询出来的结果），

    秒速五厘米秒速五厘米/ 0 赞/ 510 阅读

    相关 Oracle多表关联查询

    在实际的应用系统开发中会涉及多个数据表，每个表的信息不是独立存在的，而是若干个表之间的信息存在一定的关联，这样当用户查询某一个表的信息时，很可能需要查询关联表的信息，这就是多表

    青旅半醒青旅半醒/ 0 赞/ 748 阅读