- 浏览: 31653 次
- 性别:
- 来自: 北京
-
最新评论
Oracle学习之多表查询
- 博客分类：
- Oracle
一、笛卡尔积
笛卡尔(Descartes)乘积又叫直积。
假设有两个集合X和Y，其笛卡尔积表示为X*Y，其积表示集合X中的每一个
元素和集合Y中每一个元素的所有可能的有序对
X*Y={(x,y)|x∈X∧y∈Y}
例如：X={a,b}，Y={c,d,e}，则X*Y={(a,c),(a,d),(a,e),(b,c),
(b,d),(b,e)}
二、多表查询
当需要从多张表中获取数据(即多表联合查询)时，在不加任何限定条件的
情况下就会产生笛卡尔积，下面使用scott的用户下的emp表和dept表
进行联合查询：
SQL> select e.empno,e.ename,e.job,e.sal,e.deptno,d.dname
2 from emp e,dept d;
EMPNO ENAME JOB SAL DEPTNO DNAME
---------- ---------- --------- ---------- ---------- -------------
7369 SMITH CLERK 880 20 ACCOUNTING
7499 ALLEN SALESMAN 1600 30 ACCOUNTING
7521 WARD SALESMAN 1250 30 ACCOUNTING
7566 JONES MANAGER 2975 20 ACCOUNTING
7654 MARTIN SALESMAN 1250 30 ACCOUNTING
7698 BLAKE MANAGER 2850 30 ACCOUNTING
7782 CLARK MANAGER 2450 10 ACCOUNTING
7788 SCOTT ANALYST 3000 20 ACCOUNTING
7839 KING PRESIDENT 5000 10 ACCOUNTING
7844 TURNER SALESMAN 1500 30 ACCOUNTING
7876 ADAMS CLERK 1100 20 ACCOUNTING
7900 JAMES CLERK 950 30 ACCOUNTING
7902 FORD ANALYST 3000 20 ACCOUNTING
7934 MILLER CLERK 1300 10 ACCOUNTING
7369 SMITH CLERK 880 20 RESEARCH
7499 ALLEN SALESMAN 1600 30 RESEARCH
7521 WARD SALESMAN 1250 30 RESEARCH
7566 JONES MANAGER 2975 20 RESEARCH
7654 MARTIN SALESMAN 1250 30 RESEARCH
7698 BLAKE MANAGER 2850 30 RESEARCH
7782 CLARK MANAGER 2450 10 RESEARCH
7788 SCOTT ANALYST 3000 20 RESEARCH
7839 KING PRESIDENT 5000 10 RESEARCH
7844 TURNER SALESMAN 1500 30 RESEARCH
7876 ADAMS CLERK 1100 20 RESEARCH
7900 JAMES CLERK 950 30 RESEARCH
7902 FORD ANALYST 3000 20 RESEARCH
7934 MILLER CLERK 1300 10 RESEARCH
7369 SMITH CLERK 880 20 SALES
7499 ALLEN SALESMAN 1600 30 SALES
7521 WARD SALESMAN 1250 30 SALES
7566 JONES MANAGER 2975 20 SALES
7654 MARTIN SALESMAN 1250 30 SALES
7698 BLAKE MANAGER 2850 30 SALES
7782 CLARK MANAGER 2450 10 SALES
7788 SCOTT ANALYST 3000 20 SALES
7839 KING PRESIDENT 5000 10 SALES
7844 TURNER SALESMAN 1500 30 SALES
7876 ADAMS CLERK 1100 20 SALES
7900 JAMES CLERK 950 30 SALES
7902 FORD ANALYST 3000 20 SALES
7934 MILLER CLERK 1300 10 SALES
7369 SMITH CLERK 880 20 OPERATIONS
7499 ALLEN SALESMAN 1600 30 OPERATIONS
7521 WARD SALESMAN 1250 30 OPERATIONS
7566 JONES MANAGER 2975 20 OPERATIONS
7654 MARTIN SALESMAN 1250 30 OPERATIONS
7698 BLAKE MANAGER 2850 30 OPERATIONS
7782 CLARK MANAGER 2450 10 OPERATIONS
7788 SCOTT ANALYST 3000 20 OPERATIONS
7839 KING PRESIDENT 5000 10 OPERATIONS
7844 TURNER SALESMAN 1500 30 OPERATIONS
7876 ADAMS CLERK 1100 20 OPERATIONS
7900 JAMES CLERK 950 30 OPERATIONS
7902 FORD ANALYST 3000 20 OPERATIONS
7934 MILLER CLERK 1300 10 OPERATIONS
已选择56行。
从查询结果中可以发现有许多重复项，这就是产生了笛卡尔积，可以通过在
WHERE子句中增加合适的连接条件来消除笛卡尔积，一下几种方式消除笛
卡尔积：
2.1、Equijoin：等值连接
2.2、Non-equijoin：不等值连接
2.3、Outer join：外连接
2.4、Self join：自连接
上面两张表即可通过等值连接来消除笛卡尔积
SQL> select e.empno,e.ename,e.job,e.sal,e.deptno,d.dname
2 from emp e,dept d
3 where e.deptno=d.deptno;
EMPNO ENAME JOB SAL DEPTNO DNAME
---------- ---------- --------- ---------- ---------- --------------
7782 CLARK MANAGER 2450 10 ACCOUNTING
7839 KING PRESIDENT 5000 10 ACCOUNTING
7934 MILLER CLERK 1300 10 ACCOUNTING
7566 JONES MANAGER 2975 20 RESEARCH
7902 FORD ANALYST 3000 20 RESEARCH
7876 ADAMS CLERK 1100 20 RESEARCH
7369 SMITH CLERK 880 20 RESEARCH
7788 SCOTT ANALYST 3000 20 RESEARCH
7521 WARD SALESMAN 1250 30 SALES
7844 TURNER SALESMAN 1500 30 SALES
7499 ALLEN SALESMAN 1600 30 SALES
7900 JAMES CLERK 950 30 SALES
7698 BLAKE MANAGER 2850 30 SALES
7654 MARTIN SALESMAN 1250 30 SALES
已选择14行。
不等值连接：
SQL> select e.empno,e.ename,e.job,e.sal,s.grade,s.losal,s.hisal
2 from emp e,salgrade s
3 where e.sal between s.losal and hisal;
EMPNO ENAME JOB SAL GRADE LOSAL HISAL
---------- ---------- --------- ---------- ---------- ---------- ----------
7369 SMITH CLERK 880 1 700 1200
7900 JAMES CLERK 950 1 700 1200
7876 ADAMS CLERK 1100 1 700 1200
7521 WARD SALESMAN 1250 2 1201 1400
7654 MARTIN SALESMAN 1250 2 1201 1400
7934 MILLER CLERK 1300 2 1201 1400
7844 TURNER SALESMAN 1500 3 1401 2000
7499 ALLEN SALESMAN 1600 3 1401 2000
7782 CLARK MANAGER 2450 4 2001 3000
7698 BLAKE MANAGER 2850 4 2001 3000
7566 JONES MANAGER 2975 4 2001 3000
7788 SCOTT ANALYST 3000 4 2001 3000
7902 FORD ANALYST 3000 4 2001 3000
7839 KING PRESIDENT 5000 5 3001 9999
已选择14行。
外连接有三种类型：
2.3.1、左外连接：连接A和B两张表进行查询，返回A表中所有的记录
(即以A表为参考表，将A表中的所有记录与B表进行连接)，可
以在FROM语句中使用LEFT [OUTER] JOIN语法对两张表
进行连接，或者在WHERE子句中的右边使用外连接操作符
(+)对两张表进行连接
--方式一:使用左外连接语法LEFT [OUTER] JOIN进行连接
select e.empno,e.ename,e.job,e.sal,e.deptno,d.dname,d.loc,d.deptno
from emp e
left outer join
dept d
on e.deptno=d.deptno;
--方式二:使用外连接符(+)进行连接
select e.empno,e.ename,e.job,e.sal,d.dname,d.loc,d.deptno
from emp e,dept d
where e.deptno=d.deptno(+);
两种方式执行的结果相同，如下所示：
EMPNO ENAME JOB SAL DEPTNO DNAME LOC DEPTNO
---------- ---------- --------- ---------- ---------- -------------- ------------- ----------
7934 MILLER CLERK 1300 10 ACCOUNTING NEW YORK 10
7839 KING PRESIDENT 5000 10 ACCOUNTING NEW YORK 10
7782 CLARK MANAGER 2450 10 ACCOUNTING NEW YORK 10
7902 FORD ANALYST 3000 20 RESEARCH DALLAS 20
7876 ADAMS CLERK 1100 20 RESEARCH DALLAS 20
7788 SCOTT ANALYST 3000 20 RESEARCH DALLAS 20
7566 JONES MANAGER 2975 20 RESEARCH DALLAS 20
7369 SMITH CLERK 880 20 RESEARCH DALLAS 20
7900 JAMES CLERK 950 30 SALES CHICAGO 30
7844 TURNER SALESMAN 1500 30 SALES CHICAGO 30
7698 BLAKE MANAGER 2850 30 SALES CHICAGO 30
7654 MARTIN SALESMAN 1250 30 SALES CHICAGO 30
7521 WARD SALESMAN 1250 30 SALES CHICAGO 30
7499 ALLEN SALESMAN 1600 30 SALES CHICAGO 30
已选择14行。
2.3.2、右外连接：连接A和B两张表进行查询，返回B表中的所有记录
(即以B表为参考，将B表中的所有记录与A表进行连接)，可
以在FROM语句中使用RIGHT [OUTER] JOIN语法对两张
表进行连接，或者在WHERE子句的左边使用外连接操作符
(+)对两张表进行连接
--方式一:使用右外连接语法RIGHT [OUTER] JOIN进行连接 select e.empno,e.ename,e.job,e.sal,e.deptno,d.dname,d.loc,d.deptno from emp e right outer join dept d on e.deptno=d.deptno;
--方式二:使用外连接符(+)进行连接 select e.empno,e.ename,e.job,e.sal,e.deptno,d.dname,d.loc,d.deptno from emp e,dept d where e.deptno(+)=d.deptno;
两种方式执行的结果相同，如下所示：
EMPNO ENAME JOB SAL DEPTNO DNAME LOC DEPTNO
---------- ---------- --------- ---------- ---------- -------------- ------------- ----------
7782 CLARK MANAGER 2450 10 ACCOUNTING NEW YORK 10
7839 KING PRESIDENT 5000 10 ACCOUNTING NEW YORK 10
7934 MILLER CLERK 1300 10 ACCOUNTING NEW YORK 10
7566 JONES MANAGER 2975 20 RESEARCH DALLAS 20
7902 FORD ANALYST 3000 20 RESEARCH DALLAS 20
7876 ADAMS CLERK 1100 20 RESEARCH DALLAS 20
7369 SMITH CLERK 880 20 RESEARCH DALLAS 20
7788 SCOTT ANALYST 3000 20 RESEARCH DALLAS 20
7521 WARD SALESMAN 1250 30 SALES CHICAGO 30
7844 TURNER SALESMAN 1500 30 SALES CHICAGO 30
7499 ALLEN SALESMAN 1600 30 SALES CHICAGO 30
7900 JAMES CLERK 950 30 SALES CHICAGO 30
7698 BLAKE MANAGER 2850 30 SALES CHICAGO 30
7654 MARTIN SALESMAN 1250 30 SALES CHICAGO 30
OPERATIONS BOSTON 40
已选择15行。
2.3.3、全外连接：连接A和B两张表，返回A表和B表中所有的数据进
行的连接结果，可以在FROM语句中使用全外连接语法
FULL [OUTER] JOIN对两张表进行连接
SQL> --全外连接
SQL> select e.empno,e.ename,e.job,e.sal,e.deptno,d.dname,d.loc,d.deptno
2 from emp e
3 full outer join
4 dept d
5 on e.deptno=d.deptno;
EMPNO ENAME JOB SAL DEPTNO DNAME LOC DEPTNO
---------- ---------- --------- ---------- ---------- -------------- ------------- ----------
7369 SMITH CLERK 880 20 RESEARCH DALLAS 20
7499 ALLEN SALESMAN 1600 30 SALES CHICAGO 30
7521 WARD SALESMAN 1250 30 SALES CHICAGO 30
7566 JONES MANAGER 2975 20 RESEARCH DALLAS 20
7654 MARTIN SALESMAN 1250 30 SALES CHICAGO 30
7698 BLAKE MANAGER 2850 30 SALES CHICAGO 30
7782 CLARK MANAGER 2450 10 ACCOUNTING NEW YORK 10
7788 SCOTT ANALYST 3000 20 RESEARCH DALLAS 20
7839 KING PRESIDENT 5000 10 ACCOUNTING NEW YORK 10
7844 TURNER SALESMAN 1500 30 SALES CHICAGO 30
7876 ADAMS CLERK 1100 20 RESEARCH DALLAS 20
7900 JAMES CLERK 950 30 SALES CHICAGO 30
7902 FORD ANALYST 3000 20 RESEARCH DALLAS 20
7934 MILLER CLERK 1300 10 ACCOUNTING NEW YORK 10
OPERATIONS BOSTON 40
已选择15行。
自连接：表A和自己进行连接操作，此时要为表起个别名，通过为表起
别名可将同一张表视为多张表
--自连接 SQL> select e1.ename || '的老板是: ' || e2.ename 2 from emp e1,emp e2 3 where e1.mgr=e2.empno; E1.ENAME||'的老板是:'||E2.ENAM ------------------------------ FORD的老板是: JONES SCOTT的老板是: JONES TURNER的老板是: BLAKE ALLEN的老板是: BLAKE WARD的老板是: BLAKE JAMES的老板是: BLAKE MARTIN的老板是: BLAKE MILLER的老板是: CLARK ADAMS的老板是: SCOTT BLAKE的老板是: KING JONES的老板是: KING CLARK的老板是: KING SMITH的老板是: FORD 已选择13行。
当要查询的表非常大时，自连接会以平方级数产生笛卡尔积，Oracle对
这种查询提出了一种新的查询方式，即Hierarchical Queries(层次查询)
层次查询的语法如下：
START WITH：指定了层次查询的根
CONNECT BY：指定了层次查询中父层次和子层次之间的关系
上面的查询可用层次查询解决，如下所示：
SQL> select level,empno,ename,mgr 2 from emp 3 start with mgr is null 4 connect by prior empno=mgr 5 order by level; LEVEL EMPNO ENAME MGR ---------- ---------- ---------- ---------- 1 7839 KING 2 7566 JONES 7839 2 7698 BLAKE 7839 2 7782 CLARK 7839 3 7902 FORD 7566 3 7521 WARD 7698 3 7900 JAMES 7698 3 7934 MILLER 7782 3 7499 ALLEN 7698 3 7788 SCOTT 7566 3 7654 MARTIN 7698 3 7844 TURNER 7698 4 7876 ADAMS 7788 4 7369 SMITH 7902 已选择14行。
Oracle将上面的层次查询转化成一个树，如下图所示：
Oracle解析上面的SQL语句的顺序如下：
1、第一步：首先执行start with mgr is null，找到7839即层次
树的根
2、第二步：connect by prior empno=mgr(即前一层的员工
号等于本层的老板号)，找到了7566、7698、7782
3、第三步：本层的老板号在(7566,7698,7782)
4、第四步：找到其下属后，继续做与第三步类似的操作，直到没有
下属为止
当一张表很大时，并且该表可以转化成这样的树结构时，如需要类似
的操作可考虑使用Oracle提供的层次查询，层次查询不会出现笛卡尔
积的情况，层次查询是连接前后两次查询的结果，而不是两张表
注意：在使用多表连接查询时，要连接n张表至少需要n-1个连接条件
- 大小: 18.5 KB
发表评论
相关推荐
oracle 多表查询与数据修改学习笔记，主要是让大家熟悉三个或四个表的连接查询，熟练掌握delete、update与insert的语法。
oracle经典sql语句查询，近百中查询练习，个个经典！ 适合学习！
oracle多表查询PPT课件.pptx
Oracle学习查询语句,包含的实例很多，价值很高。
深入学习oracle分区表,oracle10g支持三种分区表创建方式,创建分区表可以一定程度上提高你数据库的访问速度,关键要看你业务上频繁查询的关键字段做为分区关键字.
oracle多表查询PPT学习教案.pptx
自己总结的Oracle查询操作的一点笔记。适用于大多初学者学习查询功能。笔记内容干练不繁琐。内容不多但涉及许多初学者容易犯错误的知识点。
Oracle学习笔记 以下是我这一周学习oracle整理的笔记，包括课堂的内容和自己看额外看的视频补充的一些内容，基本上囊括了所有oracle的基本知识。主要的形式是例子代码加代码解释加运行结果，我个人认为对于没有学习...
本文档主要是网易云李兴华老师进行授课时所作笔记，从Oracle11g数据库的安装到复杂查询做了详细的文档记录。
多表查询 5.组函数 6.子查询 7.数据操作 8.创建和维护表 9.约束 10.创建视图 11.其他数据库对象 12.控制用户访问 13.SQL练习 14.使用集合运算 15.增强GROUP BY子句 16.高级子查询 17.分级取回数据 18.oracle9i对DML...
Oracle学习教程，详细文档，进阶oracle技能，十章节，重点在查询、函数、事物和锁、PL/SQL、存储过程和函数、游标及触发器
【oracle】oracle查询优化改写，oracle学习的必修课程。
25. 安装Oracle后，经常使用的修改表空间的SQL代码 26. 比较SQL Server与Oracle、DB2 27. 多个数据库时，如何设置默认数据库 28. 各种数据类型的比较 29. 漫谈oracle中的空值 30. 没有备份、只有归档日志，如何...
oracle数据库资料，oracle数据库学习笔记，SQL查询语言
主要给大家介绍了oracle查看执行最慢与查询次数最多的sql语句，文中给出完整的示例代码，相信对大家的学习或者工作具有一定的参考价值，有需要的朋友们下面来一起看看吧。
4. Select 中 避免使用*, oracle需要转换成表的列，通过查询数据字典获得，耗费时间). 5. 注意where字句写法，必须考虑语句顺序，应该根据索引顺序、范围大小来确定条件子句的前后顺序尽可能的让字段顺序与索引顺序...
oracle 查看锁表sql 及如何解锁，多给点分，为了下载别的资料。大家相互学习相互进步
oracle学习查询文档,快速查询所需要的API，方便学习， 初学者也可使用。
oracle学习 语言集成查询(LINQ)之入门篇 语言集成查询(LINQ)之LINQ to XML高级篇
1.Oracle中的一些练习_ddl语句 2.最常用的联系表，企业员工表。以后Oracle联系表就使用这个表啦... 3.查询及删除重复记录的方法大全 4.ORACLE 中ROWNUM用法总结!