SQL Join 连接

连接 (Join)

我们知道，连接可以理解为筛选+笛卡尔乘积，即便真实的算法可以做很多优化，基本思想不会变。笛卡尔乘积是固定的，让连接操作变得复杂的，只有筛选操作，也就是筛选条件。根据不同的筛选条件，SQL提供了不同的书写方式，帮助程序员明确目的、减少意料之外的事情，也增加了学习的难度。只要牢记，各种连接就是筛选条件不同，也就可以很容易地区分各种连接了。

以下挨个介绍常用的连接方式，同时呈现对应SQL的写法。

自然连接 (Natural Join)

两个表中含有相同的「属性」，或称「列」，在笛卡尔乘积的结果集中，筛选这些相同属性相等的情况。

前文讲解笛卡尔集合时的例子就是一种自然连接，相同的列是表达教师姓名的两个属性。但是， 从SQL的角度说，这个例子并不自动构成「自然连接」，因为两个列的「名称」不同。把instructor表中的列名改为teacher，或把course表中的列名改为name，才可以自动构成SQL意义下的「自连接」。

虽然SQL有所规定，但是我们知道，自然连接实际划出了一类连接，代表「使用相等条件筛选」。只要我们明确，「使用相等条件筛选」是有直接SQL支持的，就可以了。

说了这么多，还没说SQL怎么写。

最简单的是直接使用SQL提供的关键字natural join。假设student表和takes表有相同的列ID，则以下两行SQL等价：

select name, title from student natural join takes;
select name, title from student, takes where student.ID = takes.ID;

关键字natural join相当于自动添加了where语句，自动检查了两个表的哪几个属性相同。

也可以同时操作多个表，多写几个natural join就可以了。当然，前提是多个表都存在同样的列。

select name, title 
from student natural join takes natural join course;

无论有多少个表被同时操作，SQL解释器都会找到这些表共有的相同列，以这些列的相等为条件，筛选产生结果集。

指定连接条件

这样自动找相同列的操作十分方便，同时也十分危险。要是程序员粗心地看漏了一些列名，或是对表结构有不正确的预期，可能导致数据库采用的相等条件比程序员设想的要多。SQL也提供了手动指定连接属性的方法。

如要连接表示选课情况的表takes和课程列表course，指定以属性course_id相等为筛选条件。以下SQL语句等价：

select name, title from takes join course using(course_id);
select name, title from takes, course where takes.course_id = course.course_id;

也可以指定多个属性，相当于多个筛选条件：

select name, title from takes join course using(course_id, course_name);

join...using...形式的语句可以很方便地指定「相等」条件，SQL同样提供了指定其他条件的方式，也就是join...on...。on语句同样可以指定相等条件，如上面的SQL等价于以下SQL：

select name, title from takes join course on takes.course_id = course.course_id;

on关键字可以指定任意筛选条件，这就可以实现任意形式的连接，也增加了我们学习的难度。

你可能认为，where关键字可以直接筛选笛卡尔乘积的结果，没有必要使用on或using。这是个人习惯问题，见仁见智。然而，where通常用作对结果集最后的筛选，而on using专门用作连接时候的筛选，遵守一些规定，不但可以帮助SQL解释器减轻负担，还可以让自己的工作更加清晰明了。

外连接 (Outer Join)

使用「自然连接」时，若有一个取值没有同时出现在两个表中，连接的结果集中也不会出现这个取值。若有个学生没有选任何课程，在学生表student中有他，但是在选课表takes中没有他，那么以「学生姓名相等」作为连接筛选条件的结果集中也不会出现这个学生的名字。

在大多数情况下，我们想要这样的效果。但是，我们有时也想保留其中一个表的信息，不希望连接之后得到的表信息缺失。这就是「外连接」。

当指定连接方式为「外连接」时，即便有的元素没有同时出现在两张表里，也会被保留。如一个没有选任何课的学生，指定外连接后，这个学生在学生表student中的信息会完整出现在结果集中，而相应行中来自选课表takes的信息则不会出现，以「空」显示。可能形式如下：

外连接

SQL语句中对应的关键字是outer join，可以加上前缀left或right或full，表示「左连接」、「右连接」和「全连接」。所谓「左右」的意思是，保留outer join操作符左边或是右边的列的信息。如上图的结果集，就是一个「左连接」的结果集，保留了左边student表的信息，而没有保留右边takes表的信息，出现在takes表中的信息要是没有出现在student表中，也就不会出现在结果集中。

full代表要同时保留两个表的信息，如果一个表中的信息在另一个表中没出现，就把没出现的信息显示为「空」，道理是相同的。

上图对应SQL语句如下，同样使用on来指定连接条件。

select * from student left outer join takes on student.name = takes.name;

相同的结果可以由「顺序相反」的右连接得到：

select * from takes right outer join student on student.name = takes.name;

上面两个SQL得到的结果集相同。这提醒我们，无论是左连接还是右连接，甚至是全连接，重要的是指定好「要保留哪个表的信息」，而不是「连接的名字是什么」。

从内连接到外连接

「内连接」是和「外连接」相对的概念，也就是丢弃没有同时出现在两个表中的数据，也就是我们一开始介绍的连接方式。可以通过内连接理解外连接。

两个表进行内连接后，有的行满足内连接的筛选条件，有的不满足。给不满足筛选条件的，相应填补空值，得到的就是「外连接」的结果集。

由此，我们可以这样理解：外连接=内连接+Null填补。

外连接条件使用where

外连接的筛选条件不能用where写。

考虑这样一个SQL语句：

select * from student left outer join takes on true where student.ID = takes.ID;

这个SQL语句，连接的结果集是完整的、没有筛选过的笛卡尔乘积。把连接的结果集通过where筛选，最终结果集中一定不存在ID不相等的行，这不是我们想要的「外连接」结果。

你可以停下来想一想，有没有可能通过where实现外连接。当然是不可能的！因为「筛选」不是外连接的最终结果，还有填补空值这一步，并且填补空值依赖筛选的结果。

交叉连接

交叉连接就是直接保留笛卡尔乘积的结果集，不做任何处理，没有太多好说的。普通的逗号可以触发交叉连接，如select * from student, takes;。也可以使用cross join关键字，如select * from student cross join takes;。

应该不需要再多解释了。

自连接

同一个表可以当做两个表来使用，出现在join操作符的两边，相同的连接操作仍然适用。如列出所有的工资大小关系，显示所有「同事A比同事B工资高」的情况。

select higher.name as better_employee, lower.name as lower_employee
from salary as higher join salary as lower
on higher.sal > lower.sal;

根据显示出来的「工资比较」，你就可以去打死比你工资高的同事了。