SQL中的集合理论与多表连接

SQL中的集合理论与多表连接

集合理论

对于SQL中一些稍微复杂的需求，往往需要使用一些常用的集合操作来连接两个或者多个表的数据，有时还可能需要从一个表中得出两个不同的结果集，然后再对这两个集合进行运算得到最后的答案。

SQL中常用的集合运算有三种，分别是交、差、并。

交(INTERSECT)——用来获取两个或多个不同集合的共同元素。

来看一个例子。

实例1：某大学某学院的龚教授主持了一项科研项目，需要挑选几个学生作为研究助手，要求学生具备地图学理论知识并且具备程序开发能力。龚教授从教务处拿来学生选课表，现在的问题就是要找出既选修了《地图学》课程又选修了《计算机程序设计》课程的学生名单。

《地图学》课程的选修者名单如下：

唐丽丽，王芮，胡彬，李鹤，韩彤，熊霖，何颖，肖鼎

《计算机程序设计》课程选修者名单如下：

张雨，韩彤，朱棣，李强，付芸，方芳，段箐，熊霖

那么符合条件的有韩彤和熊霖两位学生，这也就是上述两个名单集合的交集。

用集合图的形式可以很方便的描述交集的运算过程。如下图所示：

在实际情况下，集合中的每一个实体——元素——并非一定是单一值，在SQL中，经常是由行作为元素而组成的集合。也就是说，某些集合中的元素只有单一属性（暂且称之为简单元素），而有些集合中的元素具有多个属性（暂且称之为复杂元素）。

对于由复杂元素组成的集合，要得到其交集，必须找出所有属性项都相等的元素。在SQL中，属性对应于关系数据表的列。

实例2：下面有两个查询结果集：

这两个集合的交集就是所有属性都相等的元素。交集结果是：

差(EXCEPT)——用来查找存在于某一集合，但不存在于另一集合的元素。

差操作就是拿出一个集合，从该集合中去掉第二个集合中的值，剩下的结果集是在第一个集合中但没有出现在第二个集合中出现过的值。

仍旧以实例1的数据来做一下差运算，找出选修了《地图学》但没有选修《计算机程序设计》课程的同学。结果集为：唐丽丽，王芮，胡彬，李鹤，何颖，肖鼎。反之同理。实例2的差集略。

下面是用集合图描述的差集的运算过程。

并(UNION)——用来合并两个或多个相类似的集合。

并运算把两个具有相近信息的集合合并到一个集合中。并运算的实例略。

并运算的集合示意图如下所示。

SQL中的连接(JOIN)

前边所讲的INTERSECT要求参与运算的集合中所有列都严格匹配。JOIN也是一种交运算，与INTERSECT不同的是它可以由用户指定一些列，系统只要求这些列匹配即可，从而完成集合的连接。因此，JOIN可以对两个并不相似的表进行指定列值的匹配交运算。

数据示例

后面讲述JOIN操作需要用的几个表如下所示。

部门表DEPT

员工表EMP

内连接(INNER JOIN)

内连接是连接操作中最常用的一种。内连接只返回那些同时和两个表或结果集匹配的数据行。注意内连接中INNER关键字是可选的，如果没有明确指出连接类型，则默认为INNER连接。建议在书写SQL语句时候明确指出连接类型，以获得清晰的语义。

INNER JOIN关键字语法

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2 
ON table_name1.column_name=table_name2.column_name
--或者
SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2 
USING (column_name)

假设现在需要一张报表，内容包含员工编号、员工姓名、所在部门、工作职务这些内容，那么使用内连接即可很好的处理该问题。

SELECT e.EMPNO, e.ENAME, d.DNAME, e.JOB FROM DEPT d INNER JOIN EMP e ON d.DEPTNO = e.DEPTNO
--或者
SELECT e.EMPNO, e.ENAME, d.DNAME, e.JOB FROM DEPT d INNER JOIN EMP e USING(DEPTNO)

 查询结果如下。

外连接(OUTER JOIN)

外连接要求数据库系统返回的不仅仅是匹配所指定列的信息，同时也返回连接体中不匹配的信息。通常可以使用OUTER JOIN来获取一个表或结果集中的所有行以及另一个表或结果集中所有匹配行，为了达到这一目的，必需先确定是采用左外连接还是右外连接。如果期待的结果是第一个表中的所有行以及第二个表中的匹配行，就应该使用左外连接(LEFT OUTER JOIN)；相反的，如果希望返回第二个表中的所有行和第一个表中的匹配行，那么就应该使用右外连接(RIGHT OUTER JOIN)。注意还有一个完整外连接(FULL OUTER JOIN)，完整外连接返回第一个表和第二个表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。

外连接关键字语法同INNER JOIN类似，只需替换连接关键字即可。

现要求列出所有的部门以及各部门人员信息，可以使用外连接来处理，从下面的示例中也可以看出左/右外连接的区别与联系。

SELECT d.DNAME, e.EMPNO, e.ENAME, e.JOB FROM DEPT d LEFT OUTER JOIN EMP e USING(DEPTNO) ORDER BY d.DNAME
--或者
SELECT d.DNAME, e.EMPNO, e.ENAME, e.JOB FROM EMP e RIGHT OUTER JOIN DEPT d USING(DEPTNO) ORDER BY d.DNAME

查询结果如下，可以看出OPERATIONS是一个空置的部门。

交叉连接(CROSS JOIN)

交叉连接实际上是对两个表进行笛卡尔积运算。一般来说，在SQL中进行纯粹的交叉连接是没有什么意义的，但可以附加WHERE子句对交叉连接的结果进行筛选。如下语句其结果和上面的内连接示例结果一致。

SELECT d.DNAME, e.EMPNO, e.ENAME, e.JOB FROM DEPT d CROSS JOIN EMP e WHERE d.DEPTNO = e.DEPYNO

上面所讲到的集中连接在各数据库中都有其实现，不同的数据库可能还有各自的扩展，此文不作详细介绍。

 

 

转载于:https://www.cnblogs.com/hans_gis/archive/2012/04/06/2435107.html