联合查询与多表查询基础详解

--------------------------------------------------------------目录---------------------------------------------------------------

一.联合查询
       1.UNION
       2.INTERSECT
       3.EXCEPT(MINUS)

二.多表查询

       1.内联结

       2.外联结

------------------------------------------------------------联合查询---------------------------------------------------------------

一.定义：
       联合查询就是可以联合两个或多个查询来生成一个结果,联合结果集可能是查询的所有记录的一个简单聚合,或者是最终结果集返回前,进行的一些与集合论有关的运算.
(PS:简单解释一下，实际上所谓联合查询就是当自己需要的数据是由多个查询的结果中得到的时候，就可以使用联合查询)

       SQL/2003标准支持使用UNION、INTERSECT和EXCEPT(或MINUS)子句合并两个或多个查询结果，语法格式见下图，后面还会再细讲：

      
(PS:所谓的SQL/2003标准是由美国国家标准化组织(ANSI)指定的最基本的SQL语法规范，最早的SQL语言是由IBM在1974年开发出来的，后来由Oracle等其他数据库厂商分别衍生出不同的SQL版本，为了统一标准，ANSI在86年制定了一个标准SQL规范(SQL/86标准)，并经过89和92年的改进后被广泛接受，当时的版本就是SQL/92标准，在此标准之上，不同的厂家根据需要对SQL进行了功能扩展，其中为大家熟知的包括Oracle的PL/SQL以及微软SQLServer(也叫MSSQL)数据库的T/SQL语言等。前面提到的SQL/2003标准就是03年制定的新标准，在中间还有一个SQLL/99,之后06年制定了SQL/2006标准并被使用至今，但06标准只是增加了SQL与XML和XQUERY的关联应用，与本文无关，所以我们使用03标准，并且这个标准在本文后面会被经常提到哦。)

二.UNION
       首先，我们先拿出2个简单的小表，来作为测试表为以后的理论提供证明，如下面2张图所示，分别建立un_temp_1和un_temp_2两张表，并为每张表插入5条数据，本文中之后见到的涉及这2张表的测试SQL统统都用的这些数据哦：

1.UNION
      
将两个或多个结果集进行并联操作，结果见下图：

2.UNION ALL
      
单纯的将两个或多个结果集连接到一起，结果见下图：

3.UNION与UNION ALL的区别

       也许初学者还无法把集合理论与SQL结合起来理解，所以对上面的UNION会有些迷惑，实际上，UNION做的事情就是把2个SQL中的所有数据全部显示出来，同时去除了重复的数据，相对的，UNION ALL做的事情就只是简单的将2个SQL的数据排在一起而不会删除重复数据，因而在上面第一张图中UNION只查出了7条数据，是因为2个表中有3条是重复数据被数据库处理掉了

(PS1:因为UNION ALL没有去重复这一步，所以从另一个方面讲，UNION ALL的效率要比UNION快)

(PS2:Oracle中的UNION ALL会将第二部分的数据简单放在第一部分数据的后面，但请注意，并非所有的数据库的UNION ALL的顺序都是这样，比如DB2的UNION ALL查出的数据顺序就是混乱的(话是这么说，但实际上DB2也是有规律的，但是那个规律查出的数据顺序没有任何意义))

(PS3:UNION ALL比较特殊，它其实并不符合集合论，它既不是交集也不是并集，所以数学很出色的读者们请不要在这个地方纠结)

4.UNION与UNION ALL的常用场景

       UNION不用解释了，就是把2个结果集放到一起显示出来，重点说下UNION ALL，在实际需求中经常会要求在查询出的数据最后面加上一条"合计"，也就是统计信息，所以经常会用一条SQL单独查出一条合计信息，然后用UNION ALL来把这一条放到最下面。当然这不是UNION ALL最复杂的应用方式，如果出现多层级的分组统计的话，就需要添加一个序号字段，利用序号，order by排序和UNION ALL之间相互结合来达到目的，顺便一提，oracle有一个rollup的语法也可以达到相同的目的，但这不是这篇文章要讲的东西。

三.INTERSECT
(PS:INFORMIX数据库没有此语法)

1.定义
       如果能够从集合的层面真正理解上面的UNION的话，INTERSECT就很容易理解了，UNION取的是并集，而它取的是交集，即，取出2个或多查询中完全相同的数据，见下图：

2.相同效果的通用写法
       上面的PS里提到了INFORMIX没有提供这种语法，那么如果使用的数据库没有此语法，应该怎么写才能达到相同效果呢？写法见下图：

3.INTERSECT常用场景
       其实说是常用背景，但INTERSECT语法本身很少用，已知的貌似也只有在删除2个表中不同数据或相同数据的时候会用这个来做判断，如有人遇到其他使用场景请回帖告知

(PS:到此为止有一个隐藏的小问题，这里的交集和并集操作，实际上是有主次关系的，即以前一个结果集为主，取相对于后一个结果集的交集或并集，前2个语法碰巧只是正确和错误的理解得到的结果是一样的，但后面这个语法就出现区别了，这个问题在下面的EXCEPT语法中细说)

四.EXCEPT(或MINUS)

1.定义：
       这2个语法其实是一样的效果，都是将两个或多个结果集进行"非交集"的操作，也就是只取两个或多个SQL查出来的数据中不相同的部分，或者说是取的INTERSECT的相反的数据，见下图：

2.EXCEPT和MINUS的区别
       他们的效果是一样的，不同只在于支持的数据库不一样，EXCEPT支持DB2和SQL Server数据库，而MINUS则支持Oracle和SQL Server数据库
(PS:INFORMIX都不支持，也包括上面提到的INTERSECT语法)

3.相同效果的通用写法
       不支持此语法的话，就要使用通用写法了，如下图：

      
4.特别说明(对上段最后注释部分的细说)
       参见定义后面给出的测试结果就可以发现，实际得到的数据并不是6条数据，即2个表的非交集，而只是第一个表的非交集，这就是上段最后的注释要说明的问题，这个EXCEPT集合运算实际上是以第一个SQL为准，取出与第二个SQL的交集，然后再取出第一个主SQL相对这部分的"非交集"，关于这个问题讲到这里如果还有不明白的人，请仔细的去看2个测试表的数据，并自己做做实验，我就不再多说了

5.EXCEPT和MINUS的常用场景
       这个基本就是用来判断第2个集合的数据是否与第一个集合完全相同而是用，但是由于上面第4条提到的问题，所以用这个判断不一定准确，慎用。

      
五.UNION与INTERSECT与EXCEPT(或MINUS)的集合方式对比

下面用图形的方式来说明一下这几种语法取得的数据集之间的不同
1.UNION

2.INTERSECT

3.EXCEPT(或MINUS)

------------------------------------------------------------多表查询---------------------------------------------------------------

一.内联接与外联接

1.内联接:
      
若连接的结果集中只保留了符合连接条件的元组，而排除了两个表中没有对应的或匹配的元组情况，这种连接称为内连接。在内连接的查询结果中，会丢失一部分信息。

2.外联接:
如果要求查询结果集中保留非匹配的元组，则为外连接。

3.合并联接(*)

(PS:稍微有些工作经验的人都应该不会混淆内联接和外联接的概念，没有实际工作经验的学生们在课本上接触的基本都是内联接，不能理解外连接主要原因也是因为需求太抽象，不知道该用在什么情况，这些我也会再后面举例，而合并联接非常特殊，属于"知道就行"的概念，在文章的最后会稍做说明，但不做具体讲解)

二.内联接

1.新语法和旧语法
       实际上标准SQL在表联接上支持2种语法，一种是92年提出的新语法，即使用JOIN连接符做表连接的新语法，并在90年代末逐渐被主流数据库使用(微软的SQL Server 7.0及以后版本，IBM的DB2 7.1及以后版本和Oracle 9i及以后版本等，顺便一提，新语法就是IBM的DB2的语法这个后面还会再说)，另一种就是将表联接条件与过滤条件全部放在WHERE条件中的写法，也是现在大多数人都习惯的写法，这就是92年以前使用的旧语法，至今还在沿用。

2.内连接语法：
       新语法：
       SELECT *
       FROM table1

            [NATURL][INNER|CROSS] JOIN

            table2

            [ON (join_condition)]|[USING <column name>,...]
      
       上面的语法中，INNER JOIN和CROSS JOIN是常用到的，前者是最普通的内连接，并且INNER关键字可以省略，后者是交叉联接，在后面还会细说
       另外，其中的NATRUL和USING子句是Oracle特有的在这个语法中的NATRUL,USING和ON关键字不能够相互同时出现。
      
       NATRUL：表示两个关系执行自然连接操作，即在两个的公共属性上作等值连接，运算结果中公共属性只出现一次（这一功能非常好当两个表的字段很多时，就可以不用写字段名称了，觉得抽象的人按照下面的写法自己去试试）。
             假设有表a和表b，都有一个叫id的字段，这个id就是公共属性：
                   SELECT * FROM a NATRUL INNER JOIN b
             上面那个SQL写法基本等价于：
                   SELECT * FROM a INNER JOIN b ON a.id = b.id 或 SELECT * FROM a,b WHERE a.id = b.id
             说是基本等价是因为有一点区别，那就是下面的SQL查询结果会显示出2个名为id的列，而上面只显示1个，自己去试试就知道了
       USING：与NATRUL基本相同，只是NATRUL会默认连接所有的公共属性，而USING则是有选择性的：
             与上面的SQL语句的等价写法：
                   SELECT * FROM a INNER JOIN b USING(id)
           
       旧语法：
       SELECT *
       FROM table1,table2
       WHERE table1.id = table2.id
(PS:旧语法中如果不写连接条件就等同于新语法的CROSS JOIN(笛卡尔积，也叫交叉联接),所以虽然有些书上讲CROSS JOIN是单独提出来，但它也是内联接的一种特殊形式)

3.内联接分类
       (1)同等联接(自然联接为其中的特例)
       (2)非同等联接

       (3)自联接

       (4)交叉联接(迪卡尔积)

      
4.同等联接和非同等联接

       还有上面有说到自然联接也是同等联接的特殊情况，我之所以没有特别列出自然联接，是因为我本人很讨厌这种用难懂的理论和看似专业的词语来将一个本来很容易理解的概念复杂化的做法。
      
       这些概念性的词语随着我们的工作经验的积累，很快就可以很自然的理解，所以我只做简单的说明，等值联接就是链接条件用等号的，非等值的就是用其他符号比如大于和小于号。
(PS:等值联接和非等值联接确实在效率等方面存在差异，将其分开说明确实是有必要的，但这涉及到真正数据库原理，绝不是大学教材里那么幼稚且书呆子式的理论能够讲明的，所以这里不做说明)

区别见下图：
       同等联接：
     

     
       非同等联接：

      
5.自联接

       自联接和exists语法是我认为在大学教材中教的对新手来说最难理解的东西，尤其是后者，老鸟都不一定用的顺利。
       综上所述，自联接这个东西我占在自己大学时的角度考虑了很久，觉得无论怎么解释都不太能让新手们理解这个概念，所以这个东西我尽力说明，实在不明白的新手就在以后的工作经验中成长吧。
      
      所谓的自联接，字面意思就是自己和自己做联接，语法就是在FROM后面出现多次同一表名，然后以别名来区分:SELECT * FROM a table1,b table2,c table3 WHERE a.id = b,id and a.id = c.id
      
       自联接难以理解的地方在于，新手认为在同一个表中的数据可以随便使用，所以不明白为什么要把一个表使用N次，原因是这样的，数据由SQL语句查询出来，而查询出正确数据所需要的过滤条件统统都会放在SQL中的WHERE关键字后面，不同条件用AND和OR关键字区分开来，每一个条件会按行来进行过滤，即有一个字段不符合条件，整行都会被过滤掉。但是，在同一个表中，一但出现相互矛盾的条件并且又要将满足矛盾条件的所有数据都呈现出来的时候，就不能够使用AND关键字了，因为这样就会让数据被矛盾的条件互相过滤掉。
       举个例子：你想把同一id的今天的数据和昨天的数据在同一行中列出来(这种做法一般都会伴随着计算出另外一个字段：增长率，这种形式在业务上被称作同环比，这个不是本文讨论的内容，只要知道这种情况是常有的就行了)，这个时候，你不可能在where条件中写上a.time = 今天 and a.time = 昨天，这样只会让所有的数据都被过滤掉，导致查询结果没有任何数据，在这个时候就会使用自联结(当然还有其他实现方式比如行列转换，这里不讨论)将同一个表使用2次，这样，WHERE条件会分别过滤2个次中的不同数据，从而消除条件之间的矛盾关系。
       自联接的工作原理是，将在内存中开辟2块(或N块)不同的存储区，将同一个表在2块区域中复制2次，然后分别处理，这就相当于将同一个表想象成了2个不同的表，只不过2个表的数据时完全一样的。

       自连接的概念就说到这里，下面给出一个简单的例子，如下图：

6.交叉联接(迪卡尔积)

      
       如果离散数学学的不错的同学，对笛卡尔积也一定会比较了解，简单的说就是当没有联接条件的时候，数据库就会将所有可能的搭配组合全部罗列出来，下图为交叉联接的基本语法：
     

     
       为了更容易说明交叉联接，我们新建了两张表，并且之后的外联接也沿用这2张表(注，外连接的话数据会有变化),如下图:
       表un_temp_3和un_temp_4分别各有2条数据：

     

     
       然后进行交叉联接:

     

       从上图看到，由于没有联接条件，所以数据库就会尝试2张表中出现的所有组合
(PS:交叉联接是开销很大的联接方式，由于没有联接条件，数据库会尝试所有的组合，这意味着，2张不算很大的表做交叉联接也可以轻易的让联接后的数据量达到上亿条甚至更多到导致崩溃，比如像上面那样简单的2个表，只要各有10000行数据，就可以有10000的平方的数据量，所以要慎用，但是存在即是合理，你也可能会遇到一定需要使用交叉联接的时候，那时候要记得将做交叉联接的2个结果集控制在最小的数据量)

三.外联接

       外联接主要分为左外联接，右外联接，和全外联接，基本语法与内联接相似，只是内联接使用的是INNER和CROSS关键字，外联接则使用LEFT OUTER JOIN, RIGHT OUTER JOIN和FULL OUTER JOIN(OUTER关键字可以省略)如下：

       FROM table1
[LEFT|RIGHT|FULL] [OUTER] JOIN
  table2
[ON (join_condition)]|[USING <column name>,...]

1.左外联接
       就是将JOIN关键字左边的表的全部数据，和右边的表中，符合连接条件的数据做关联，为了方便测试，我们重新对un_temp_3和un_temp_4这两张表插入数据见如下：
     


     
       下面是左外联接的语法和查询结果：
     

     
       如上图，我们看到了，左边表的所有数据都被列出来，而右边表中根据条件un_id_1 = un_id_2，将符合条件的数据列了出来，不符合条件的部分用null值处理
      
(PS1:在上图中，我们还看到了旧语法中使用了(+)，这是Oracle用于左外联接的专用的旧语法，需要注意的是符号(+)在条件右边的时候是左外联接，在左边的时候反而是右外联接，这点不要搞混)

(PS2:SQL Server2000采用*来支持旧语法,并且与Oracle不同的是，*在等号左边即为左外联接,但是SQL Server2008并不支持旧语法实现外联接,如果确实需要(如重新编写旧代码时),则需要将兼容级别设为80(相当于SQL Server2000),执行代码后再改回100(相当于2008))

(PS3:DB2从一开始就不支持旧语法,实际上,标准SQL的多表联接的新语法就是起源于DB2标准.DB2从9.5版本之后开始支持Oracle的"旧"语法,但需要将某个注册表变量改为特定的数值)

2.右外联接
      
       没什么可说的，和左外联接相反，在JOIN右边的是主表，效果直接看下图吧：

     
      
3.全外联接

       概念和左外联接，右外联接差不多，全外联接只是把相匹配的数据显示成一行，不相匹配的数据各自显示成一行，不匹配的部分用null值表示，效果见下图：
     

       值得注意的是，全外联接并没有旧语法的写法，比如Oracle并不是说在等号两边都放上(+)就是全外，这样做只会报出语法错误，如图：
     

     
       还有一点是，并非所有的数据库都支持全外联接，比如INFORMIX,如果你遇到这种情况又想要达到全外联接的效果，就用左外联接UNION右外联接，如下面这个写法：
      
       SELECT *
       FROM un_temp_3,un_temp_4
       WHERE un_id_1 = un_id_2(+)
       UNION
       SELECT *
       FROM un_temp_3,un_temp_4
       WHERE un_id_1(+) = un_id_2
      
      
      
四.合并联接(*)
      
       用UNION表示,但不要与UNION运算符混淆,它基于与内联接相反的思想,其结果集只包括那些两个联接表中没有匹配的行,没匹配的列用NULL填充.
在现实世界中,唯一支持合并联接的数据库是PostgreSQL

      
       写法如下：
       SELECT *
       FROM a UNION JOIN b ON a.id = b.id