常用SQL系列之（三）:记录叠加、匹配、外连接及笛卡尔等

传新视界说 04-10 2385

前言：

当前同学们对“oracle 外连接”大约比较讲究，大家都想要知道一些“oracle 外连接”的相关知识。那么小编在网上汇集了一些对于“oracle 外连接””的相关内容，希望我们能喜欢，兄弟们一起来了解一下吧！

本系统为@牛旦教育IT课堂在微头条上的内容，

为便于查阅，特辑录于此，都是常用SQL基本用法。。

前两篇连接：

（一）：SQL点滴(查询篇)：数据库基础查询案例实战

（二）：SQL点滴(排序篇)：数据常规排序查询实战示例

（17）如何实现多表记录的叠加返回？

也就是如何将来自多个表的数据组织到一起，把各个表的结果叠加起来。这些表不必要有相同的关键字，但各表对应的列的数据类型应相同。例如我们现实部门编号为2的所有员工的名字和编号，以及部门的名字和编号，实现参考SQL如下：

select ename , emid from employee where departid=2

union all

select '-----------', null

union all

select dpname,dpid from department

中间select横线是可选的，以示多表间结果的分立。这样关键的是应用union all来实现多表结果的叠加返回。所以可能有重复的结果。若果想去重，可以结合distinct来对结果应用子查询来处理。

（18）如何基于关联列把不同表列值组合到一行返回？

也就是说通过相关联的列（多为外键），实现把不同表的行列值累加到另一表的行列值作为一行返回（若有多个行符合条件的就会返回多行）。我们以员工和部门为例来实现相关行组合，现实员工姓名、工资、部门名称和地址，SQL示例如下：

select ename,salary,d.dpname,d.dpaddress from employee e,tx_department d where e.departid = d.dpid

其实这是一种数据库关联查询的内连接，是关联列的等值查询。还有一种用inner join写法,参考如下：

select ename,salary,d.dpname,d.dpaddress from employee e inner join tx_department d on e.departid = d.dpid

那可以不等吗？

（19）如何在两个表中查找共同行？

换句话说，就是A表和B表，存在这种情况，即A表中的行值有包含于B表中行值（相当于B表行值的子行）的，比如员工表和团队表，团队表包含人名、工作、部门id，员工表中包含团队表中信息，那如何实现即返回团队的所有信息又返回员工的薪资和编号信息？我们可以这样来写SQL，参考如下：

select e.eid,e.ename,e.job,e.salary,e.departid from employee e ,team t

where e.ename =t.ename and e.job =t.job and e.departid =t.departid

这样，我们就可以实现我们想要的结果了。

当然，你可以参考值我们前面的例子（SQL点滴（18）:如何基于关联列把不同表列值组合到一行返回？也就是说通过相关联的列（多为外键），实现把不同表的行列值累...），把上面的语句改成join子句的形式。试试吧。

（20）如何从一个表中查找另一个表中没有的值？

也就是说，从一个（称为数据源）中查找另一个目标表中不存在的值。比如，从部门表department中查找在雇员表employee中不存在数据的所有部门（相当于说，若部门表有10条记录，而雇员表中的部门只用了不到10个的部门，把没有使用的部门找出来）。对于这个问题，不同的DB有不同的解决方法，比如DB2、PostgreSQL、Oracle支持差集操作，而MySQL和SQL Server不支持，因而在MySQL中，我们使用子查询来实现，参考例句如下：

select departid from department where departid not in (select departid from employee)。

此句也适用SQL Server。

那么在，支持差集操作的Oracle中，可以这样实现：

select departid from department

minus

select departid from employee

就能实现我们想要的目的。若是DB2或PostgreSQL，只是差集关键字不一样，用except替换minus即可。但需要注意的是，在except/minus差集操作中，两个select列表中值得数目和数据类型必须匹配，而且except不会返回重复行。含义就是从前一个查询结果中返回所有在后一个查询结果中没有的行。

（21）：如何利用外连接在一个表中查找与其它表不匹配的记录？

比如有这样的场景，部门表中用8部门，而员工表中仅仅只关联了6个部门，如何用外连接找出没有员工的部门？我们可以这样干：

select d.* from department d left outer join employee e on (d.departid =e.departid) where e.departid is null 。

这样我们就可以把没有员工的部门全部找出来了。以上SQL适用MySQL、BD2、SQL Server及PostgreSQL。若是Oracle 9i及之后，上述写法也适用。你还可以用Oracle独有的外连接语法（+）,比如：select d.* from department d , employee e where d.departid =e.departid (+) and e.departid is null

试试吧。

（22）：有3张关联表，如何基于前两表的全关联附加第三张表部分关联信息？

场景可以这样描述：部门表和员工表以及员工奖励表，那么要返回所有员工姓名和所在部门，并关联显示部分受到奖励的员工奖励日期（不是所有员工都得到奖励的，少数几个吧）。我们可以这样来写SQL语句：

SELECT

e.ename ,d.dpname,b.received

FROM

employee e JOIN department d

ON ( e.departid = d.dpid )

left join ebonus b

on (e.emid = b.emid)

order by 2

结果见下图。顺便一提：如果只返回有奖励的员工的上述信息，这个SQL语句怎么改写？动手试试吧^_^

（23）：多表查询时笛卡儿积及处理规则是怎样的？

场景如下，部门表中有4条记录，员工表中有部门2的记录是3条，如果我们这样查询，就会产生笛卡儿积：

SELECT

e.empname,

d.dpname

FROM

employee e,

depart d

WHERE

e.dpid = 2

ORDER BY 1

结果会返回12条记录，这显然不合期望，由于笛卡尔积出现了。

这里产生笛卡尔积的原因类似于从A地经B地到达C地，分为2段走法，A到B有3中走法，B到C有4种走法，那最终A到C就有3*4=12中方法，这在表中查询如按照这种不受约束的执行，就产生了笛卡尔积。所以，一般来说，要避免笛卡尔积，需要使用n-1规则，n为from后表的数量，n-1是避免产生笛卡尔积的最小连接数。根据表中中的关键字和关联列不同，可能需要超过n-1的个连接。如何改正上面的SQL语句以避免笛卡尔积？按规则，可以这样来：

SELECT

e.empname,

d.dpname

FROM employee e,depart d

WHERE e.dpid = 2 and e.dpid = d.dpid

ORDER BY 2

这样就可以了。试试看吧，关键是理解上面的规则。

（24）：聚集于外连接时的重复计算问题

场景是这样的，即统计部门2（主键值）中的员工的工资总额和奖金总额。这里假设每位员工的工资都是固定的，但可以有多次奖励记录，假设abc员工有两次奖励记录，我们第一次这样来写统计性SQL：

select departid,

sum(salary) as total_sal,

sum(bonus) as total_bonus