SQL中的JOIN

发布时间: 2017-04-07

Ref: (stackoverflow) Difference between Inner Join & Full join Ask

在SQL中一种有三种用于连接外部表的JOIN方式:

LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN

关键字OUTER在不同的SQL方言中是不同的实现，可带可不带。所以FULL JOIN其实跟FULL OUTER JOIN是一个意思。OUTER字段可以忽略。

接下来看看这堆JOIN到底做了些什么。

假如我们有如下两张表信息:

 Set "A"    Set "B"

 AA         BB
--------   --------
 Item 1     Item 3
 Item 2     Item 4
 Item 3     Item 5
 Item 4     Item 6

表A / B中各自存有不同的Item，A中有些Item在B中没有，B也如此。

LEFT JOIN

首先看一下LEFT JOIN，将A作为左表，B作为右表：

SELECT * FROM A LEFT JOIN B ON AA = BB

会得到如下结果(空的部分其实是NULL，示例中没有打出来):

 AA         BB
--------   --------
 Item 1
 Item 2
 Item 3     Item 3
 Item 4     Item 4

A表返回了所有Item,B表返回了对应A表相同Item的部分。

RIGHT JOIN

承接上面所述，换成RIGHT JOIN:

SELECT * FROM A RIGHT JOIN B ON AA = BB

返回结果:

 AA         BB
--------   --------
 Item 3     Item 3
 Item 4     Item 4
            Item 5
            Item 6

B表返回全部结果，A表返回对应着B表Item的结果。

FULL JOIN

然后，如果你想返回AB表所有记录，就用到了FULL JOIN：

SELECT * FROM A FULL JOIN B ON AA = BB

得出结果:

 AA         BB
--------   --------
 Item 1            <-----+
 Item 2                  |
 Item 3     Item 3       |
 Item 4     Item 4       |
            Item 5       +--- empty holes are NULL's
            Item 6       |
   ^                     |
   |                     |
   +---------------------+

A表和B表内容会全部返回，AB中相对应的记录会一一对应，没有对应到的记录会用NULL填充。

INNER JOIN

相反如果希望取出的是AB中的交集的部分，就用到了INNER JOIN:

SELECT * FROM A INNER JOIN B ON AA = BB

 AA         BB
--------   --------
 Item 3     Item 3
 Item 4     Item 4

这样取到的只有AB中对应的Item，相当于取出交集运算，一一对应，不会存在NULL填充的情况。

CROSS JOIN

CROSS JOIN会产生AB表的笛卡尔积，将A中的Item跟B中的行一一对应。

SELECT * FROM A CROSS JOIN B

 AA         BB
--------   --------
 Item 1     Item 3      ^
 Item 1     Item 4      +--- first item from A, repeated for all items of B
 Item 1     Item 5      |
 Item 1     Item 6      v
 Item 2     Item 3      ^
 Item 2     Item 4      +--- second item from A, repeated for all items of B
 Item 2     Item 5      |
 Item 2     Item 6      v
 Item 3     Item 3      ... and so on
 Item 3     Item 4
 Item 3     Item 5
 Item 3     Item 6
 Item 4     Item 3
 Item 4     Item 4
 Item 4     Item 5
 Item 4     Item 6

NATURAL JOIN

最后讨论一下NATURAL JOIN，并没有指定需要on什么字段。他会自动将名称相同的列记录按行内容是否相同匹配。

SELECT * FROM A NATURAL JOIN B

 +----------+------- matches on the names, and then the data
 |          |
 v          v
 XX         XX
--------   --------
 Item 3     Item 3
 Item 4     Item 4

最终得出结果跟INNER JOIN一样。