oracle组合表查询,Oracle高级查询之多表查询

SQL表连接

多表查询分为 内连接、外连接和交叉连接。现在有表A和表B：TableATableBidnameidage

1n1118

2n2220

4n4319

一、外连接

外连接分为左连接(left join 或left outer join)、右连接(right join 或者 right outer join)、和全外部连接 (full join 或者 full outer join)

1. 左外连接

左连接的结果就是left join子句中的左表的所有行，而不仅仅是连接列所匹配的行，如果左表中的某行在右表中没有匹配，则在相关联的结果行中右表的所有选择列均为空值(NULL)1select * from TableA left join TableB on TableA.id = TableB.id;

结果：TableA(基表)都显示，TableB(外表)第三行条件不匹配，都为nullidnameidage1n1118

2n2220

4n4nullnull

2. 右外连接

右连接就是和左连接相反1select * from TableA right join TableB on TableA.id = TableB.id;

结果：TableB(基表)都显示，TableA(外表)第三行条件不匹配，都为nullidnameidage1n1118

2n2220

nullnull319

3. 全外连接

显示左右表中的所有行，当某一个表中没有匹配的行时，则另一个表的选择列表列显示空值(NULL)如果有则显示全部数据1select * from TableA full join TableB on TableA.id = TableB.id;

结果：TableA和TableB都显示，条件不匹配的都为nullidnameidage1n1118

2n2220

nullnull319

4n4nullnull

二、内连接

内连接(join 或者inner join)是用比较运算符比较要连接的列的值，不匹配的行不被显示1select * from TableA join TableB on TableA.id = TableB.id;

结果：只返回条件匹配的行idnameidage1n1118

2n2220

等价于：1select * from TableA,TableB where TableA.id = TableB.id;1

2select * from TableA cross join TableB where TableA.id = TableB.id;

三、交叉连接

交叉连接(cross join)没有where条件的交叉连接产生连接表的笛卡尔积1select * from TableA cross join TableB;

结果：返回笛卡儿积idnameidage1n1118

2n2118

4n4118

1n1220

2n2220

4n4220

1n1319

2n2319

4n4319

等价于：1select * from TableA,TableB;

四、合并

合并(union和union all)用于合并多个select语句的结果集union：消除重复行

union all：不消除重复行

注：使用union时，两张表查询的结果有相同的列，并且列的类型相同

五、自然连接

自然连接(natural join)也叫相等连接，两张表通过natural join连接的时候，相当于有个隐含的WHERE子句，对两张表中同名的对应列相比较看是否相等1select * from TableA natural join TableB;

等价于：1select * from TableA , TableB where TableA.depno = TableB.depno;

六、不相等连接

两个表中的相关的两列进行不等连接，比较符号一般为>,

七、自连接

自连接是数据库中经常要用的连接方式，使用自连接可以将自身表的一个镜像当作另一个表来对待，从而能够得到一些特殊的数据例：有一个学生表Table，里面有学号、功课编号和学生成绩三个字段。用一个SQL查询语句得出每门功课成绩最好的前两名学号nnum功课编号pnum成绩score1199

2198

31100

4288

5287

6288

7399

8388

931001

2

3

4

5

6

7SELECT DISTINCT Table1.*

FROM Table Table1

INNER JOIN Table Table2 ON Table1.nnum IN(

SELECT TOP 2 Table.nnum

FROM Table

WHERE Table.pnum = Table1.pnum

ORDER BY score DESC)

八、自连接存在的问题

自连接会产生笛卡儿积，不适合操作大表。如：查询员工姓名和员工老板的姓名：1

2

3SQL> select e.ename 员工姓名, b.ename 老板姓名

from emp e, emp b

where e.mgr = b.empno --员工的老板号等于老板的员工号

解决方法：层次查询(单表查询)：

1

2

3

4

5SQL> select level,empno,ename,sal,mgr --level是伪例

from emp

connect by prior empno = mgr --上一层的员工号等于老板号，prior是关键字，表示上一层

start with mgr is null --开始条件，只有根节点可以这样表示，start with empno = 7839

order by level; --按照level排序

自连接和层次查询优缺点：自连接得到的结果直观，不适合操作大表

层次查询是单表查询，不会得到笛卡尔积。得到结果不直观

九、SQL连接和多表查询的区别

无区别，写法不同而已

十、on 和 where 的区别

基本没区别，在执行顺序上on比where先执行