JOIN（连接）众生相

JOIN语句的其实是用来合并数据，或着说是进行集合运算的语句，而合并数据的理论是以关系代数(Relation Algebra) 为基础的。关系代数是一种抽象的查询语言，用对关系的运算来表达查询，用作研究关系数据语言的数学工具。

在关系代数中，连接运算是由一个笛卡尔积运算和一个选取运算构成的。首先用笛卡尔积完成对两个数据集合的乘运算，然后对生成的结果集合进行选取运算，确保只把分别来自两个数据集合并且具有重叠部分的行合并在一起。连接的全部意义在于在水平方向上合并两个数据集合（通常是表），并产生一个新的结果集合，其方法是将一个数据源中的行于另一个数据源中和它匹配的行组合成一个新元组。
SQL提供了多种类型的连接方式，它们之间的区别在于：从相互交叠的不同数据集合中选择用于连接的行时所采用的方法不同。

SQL中有五种连接（JOIN)，分别是：左连接(Left Join)、右连接(Right Join)、等值连接(Inner Join)、全外连接(Full Outer Join)、交叉连接(Cross Join)。

左连接(LEFT JOIN)

左连接(Left Join)，其全名应该是左外连接(Left Outer Join)，包含左边表的全部行（不管右边的表中是否存在与它们匹配的行），以及右边表中全部匹配的行。

左连接的语法：

select column_name(s)
  from table_name1
  left join table_name2
    on table_name1.column_name = table_name2.column_name

Examples over LEFT JOIN

Persons:

id	lastname	firstname	address	city
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

Orders:

id	order_no	person_id
1	77895	3
2	44678	3
3	22456	1
4	33489	1
5	78945	65

现在，我们希望列出所有的人，以及他们的定购 - 如果有的话，可以使用下面的 SELECT 语句：

select persons.lastname,
       persons.firstname,
       orders.order_no
  from persons
  left join orders
    on persons.id=orders.person_id
 order by persons.lastname

执行上面的SQL语句，我们会得到如下结果集：

lastname	firstname	order_no
Adams	John	22456
Adams	John	33489
Carter	Thomas	44678
Carter	Thomas	77895
Bush	George

LEFT JOIN 关键字会从左表 (Persons) 那里返回所有的行，即使在右表 (Orders) 中没有匹配的行。

右连接(RIGHT JOIN)

右连接(Right Join)，其全名应该是左外连接(Right Outer Join)，包含右边表的全部行（不管左边的表中是否存在与它们匹配的行），以及左边表中全部匹配的行。

右连接的语法：

select column_name(s)
  from table_name1
 right join table_name2
    on table_name1.column_name = table_name2.column_name

Examples over RIGHT JOIN

还是使用上面的Persons和Orders表，如果我们希望列出所有的订单，以及下单的人（如果有的话），可以使用下面的 SELECT 语句：

select persons.lastname,
       persons.firstname,
       orders.order_no
  from persons
 right join orders
    on persons.id=orders.person_id
 order by orders.order_no

这们会得到如下的结果集：

order_no	lastname	firstname
22456	Adams	John
33489	Adams	John
44678	Carter	Thomas
77895	Carter	Thomas
78945

LEFT JOIN 关键字会从右表 (Orders) 那里返回所有的行，即使在左表 (Persons) 中没有匹配的行。

内连接(INNER JOIN)

内连接(Inner Join)，是用比较运算符比较要连接列的值的连接，只连接匹配的行。

内连接的语法：

select column_name(s)
  from table_name1
 inner join table_name2
    on table_name1.column_name = table_name2.column_name

Examples Over INNER JOIN

我们继续使用上面的两个表，现在，我们希望列出所有的人，以及他们的定购，可以使用下面的 SELECT 语句：

select persons.lastname,
       persons.firstname,
       orders.order_no
  from persons
 inner join orders
    on persons.id=orders.person_id
 order by persons.lastname

我们会得到下面的结果集：

lastname	firstname	order_no
Adams	John	22456
Adams	John	33489
Carter	Thomas	44678
Carter	Thomas	77895

INNER JOIN关键字只会返回左右表都匹配的行。

全外连接(FULL OUTER JOIN)

全外连接(Full Outer Join)返回参与连接的两个数据集合中的全部数据，无论它们是否具有与之相匹配的行。在功能上，它等价于对这两个数据集合分别进行左外连接和右外连接，然后再使用消去重复行的并操作将上述两个结果集合并为一个结果集。
在现实生活中，参照完整性约束可以减少对于全外连接的使用，一般情况下左外连接就足够了。在数据库中没有利用清晰、规范的约束来防范错误数据情况下，全外连接就变得非常有用了，你可以使用它来清理数据库中的数据。

全外连接的语法：

select column_name(s)
  from table_name1
  full outer join table_name2
    on table_name1.column_name = table_name2.column_name

Examples Over FULL OUTER JOIN

如果我们要对上面的Persons表和Orders表进行全外连接合并，可以使用下面的语句：

select persons.lastname,
       persons.firstname,
       orders.order_no
  from persons
  full outer join orders
    on persons.id=orders.person_id

我们会得到如下的结果集：

lastname	firstname	order_no
Adams	John	22456
Adams	John	33489
Carter	Thomas	44678
Carter	Thomas	77895
Bush	George
		78945

你会发现，全外连接首先列出了匹配的行，然后对于不匹配的行也列了出来，虽然这些行可以没有任何意义。

交叉连接(CROSS JOIN)

交叉连接用于对两个源表进行纯关系代数的乘运算。它不使用连接条件来限制结果集合，而是将分别来自两个数据源中的行以所有可能的方式进行组合。数据集合中一的每个行都要与数据集合二中的每一个行分别组成一个新的行。例如，如果第一个数据源中有5 个行，而第二个数据源中有4个行，那么在它们之间进行交叉连接就会产生20个行。人们将这种类型的结果集称为笛卡尔乘积。
大多数交叉连接都是由于错误操作而造成的，但是它们却非常适合向数据库中填充例子数据，或者预先创建一些空行以便为程序执行期间所要填充的数据保留空间。

交叉连接的语法：

select column_name(s)
  from table_name1
 cross join table_name2

在交叉连接中没有on条件子句和Where语句的。

如果对前面的Persons表和Orders表进行交叉连接运算：

select persons.lastname,
       persons.firstname,
       orders.order_no
  from persons
 cross outer join orders

我们会得到3*5=15条记录：

lastname	firstname	order_no
Adams	John	22456
Adams	John	33489
Adams	John	44678
Adams	John	77895
Adams	John	78945
Carter	Thomas	22456
Carter	Thomas	33489
Carter	Thomas	44678
Carter	Thomas	77895
Carter	Thomas	78945
Bush	George	22456
Bush	George	33489
Bush	George	44678
Bush	George	77895
Bush	George	78945