full join 和full outer join_跟飞哥学编程：SQL入门-：JOIN和UNION

因为要遵守三大范式的要求，实际的开发中，数据库中会存在大量的零碎的“小”表。对于这些小表，我们常常使用

JOIN

将多个表 水平 联接起来，以获取单表无法提供的信息。比如我们现在有两张表Student和City：

为了简化，我们只用了很少的列

执行如下SQL语句：

SELECT 

建议：总是使用表别名。如果不使用别名，相同的列名就需要使用表的全名前缀加以区分

得到的结果就是：

从FromCity和Id列，可以清楚的看到两表连接的方式或规则。同一行内，FromCityId的值总是等于（右边City的）Id的值。这是由SQL中：ON s.FromCityId = c.Id 决定的。意思就是：把Student的FromCityId和City的Id相等的行连接起来。

查看执行计划，可以看到SQL Server先进行了两张表的扫描，然后再进行了JOIN操作：

我们可以在SELECT子句中指定列，进一步整理显示结果，比如：

SELECT 
    

结果就会变成：

这就是SQL中默认的连接方式：内连接，关键字INNRE JOIN，其中INNER可以省略。除此以外，还有：

外连接，关键字：OUTER，也可以省略。因为它又还细分为：

• 左外连接（可简称为左连接），关键字：LEFT：左边所有行被保留
• 右外连接（可简称为右连接），关键字：RIGHT：右边所有行被保留
• 全外连接，关键字：FULL：左右两边都被保留

所谓“左”和“右”，是指在书写SQL语句时，在JOIN左边或右边。

为了展示左连接，我们修改一下上表数据，把“陈晓斌”的FromCityId改成NULL值。使用左连接：

SELECT 

因为要保证左表全部显示，“陈晓斌”就一定会被保留。但保留之后，City里没值怎么办呢？用NULL填充：

如果是内连接的话，因为NULL值不会和City.Id里的1,2,3,4相等，所以结果就会没有“陈晓斌”这一行：

注意：没有“陈晓斌”这一行了

同理，为了展示右连接，我们可以在City表中添加一行：5/四川/成都，这个城市在Student表中没有任何对应。使用右连接：

SELECT 

因为要保证由表全部显示，“5/四川/成都”就一定会被保留（但FromCityId=NULL的“陈晓斌”也不会保留）。保留之后，Student里没值还是用NULL填充：

如果是内连接的话，Student表中没有任何一行FromCityId=5，所以结果就不会有“5/四川/成都”这一行：

注意：没有“5/四川/成都”这一行了

想一想：Student LEFT JOIN City是不是等于 City LEFT JOIN Student？

那么最后，全连接就非常好理解了：

SELECT 

结果为：

最后，我们来了解一下交叉连接：

SELECT 

这中连接会不进行任何过滤，依次：

1. 把左边的表的第一行和右边全表连接
2. 把左边的表的第二行和右边全表连接
3. 把左边的表的第三行和右边全表连接
4. ……
5. 把左边的表的最后一行和右边全表连接

这种运算方式又被称之为笛卡尔乘积。其计算结果的行数非常大（mxn），同学们了解即可，一般不需要使用。

实际上，在SELECT查询语句中，使用JOIN连接的表，可以像单表一样使用WHERE/ORDER/GROUP等操作。

但是，注意不要混用WHERE和ON。在INNER JOIN的时候，可能没有什么问题，比如这两句SQL是等效的：

SELECT 

但是，在外连接的时候，使用WHERE子句结果仍然正常：

SELECT 

但使用AND的结果就变得“诡异”起来：

SELECT 

整张表，包括姓名不是“幸龙泰”的同学都被查询出来了，而且我们还注意到除了“幸龙泰”，关联的City表中的值全部为NULL！这是怎么回事？

从逻辑上来讲，在ON中定义的条件是用于“连接”的，不是用于对查询结果进行过滤的。所以，根据左外连接的规则：

1. 左边的所有数据都必须保留，无论连接条件是否满足；
2. 只是当连接条件不能满足的时候，右表字段值为NULL.

从而形成了上述查询结果。

另外，对比两条语句的执行计划：

看起来都差不多。但是，把鼠标停在Student的Clustered Index Scan上，你就会发现区别：

使用的是AND

使用的WHERE子句

可以看出，在Student上设置的WHERE子句其实在JOIN之前都执行了！(^o^)/

为了代码的可读性，我们推荐“两个总是”：

1. 总是使用ON设立联接条件，
2. 总是使用WHERE对联接之后的结果进行筛选。

联表删/改

直接上语法，背下来就行。记忆规律就是在单表的删改语句中加了一行FROM...JOIN...ON。

比如，删除来自重庆的同学：

DELETE 

将来自重庆的同学的成绩加5分：

UPDATE 

就像外键不仅仅用于多张表的连接一样，JOIN也不仅仅用于连接其他表，我们还可以使用JOIN的：

自联接

让表自己和自己联接。

和多表连接唯一的不同：连接时必须使用表别名。

比如有这么一个需求：

找到姓名重复的学生。

除了之前的子查询，我们还可以使用自连接：

SELECT 

UNION

可以将多个查询结构纵向联接，只要这些查询结果列的数量、顺序相同，且每一列的数据类型能够兼容（可隐式转换）。

比如我们可以把学生的综合成绩和各科成绩用一个查询结果显示出来，并按学生姓名有序显示：

SELECT 

注意以下几个语法点：

1. UNION后可以接ALL，也可以不接。不接ALL（默认），会清除重复行（所有列/字段都重复才算重复）；接ALL，不会清除重复行
2. 列名以第一个查询子句为准，所以也只能使用第一个SELECT语句的列名

每日单词：

作业：

1. 联表查出求助的标题和作者用户名
2. 查找并删除从未发布过求助的用户
3. 用一句SELECT显示出用户和他的邀请人用户名
4. 17bang的关键字有“一级”“二级”和其他“普通（三）级”的区别：
   1. 请在表Keyword中添加一个字段，记录这种关系
   2. 然后用一个SELECT语句查出所有普通关键字的上一级、以及上上一级的关键字名称，比如：

1. 17bang中除了求助（Problem），还有意见建议（Suggest）和文章（Article），他们都包含Title、Content、PublishTime和Auhthor四个字段，但是：
   1. 建议和文章没有悬赏（Reward）
   2. 建议多一个类型：Kind NVARCHAR(20)）
   3. 文章多一个分类：Category INT）
2. 请按上述描述建表。然后，用一个SQL语句显示某用户发表的求助、建议和文章的Title、Content，并按PublishTime降序排列

---

感谢童鞋们的阅读！^_^

我就是：黑律师/包工头/创业狗/老码农……现在还是教书匠的大飞哥。

再次重申这个系列的目标是：

1）通俗易懂。2）实战为主。3）面向就业。

系列内容的完善需要你的反馈！

欢迎点赞和评论，以及加入我们的QQ交流群：326801052。