连接

本章将讨论 PostgreSQL 的各种连接：内连接、左外连接、右外连接、交叉连接、自然连接、自连接和完全外连接。值得一提的是，哪怕到了 MySQL 8，MySQL 依然不支持完全外连接。

开始之前，先创建两张表并插入一些数据：

CREATE TABLE basket_a (
    a INT PRIMARY KEY,
    fruit_a VARCHAR (100) NOT NULL
);

CREATE TABLE basket_b (
    b INT PRIMARY KEY,
    fruit_b VARCHAR (100) NOT NULL
);

INSERT INTO basket_a (a, fruit_a)
VALUES
    (1, 'Apple'),
    (2, 'Orange'),
    (3, 'Banana'),
    (4, 'Cucumber');

INSERT INTO basket_b (b, fruit_b)
VALUES
    (1, 'Orange'),
    (2, 'Apple'),
    (3, 'Watermelon'),
    (4, 'Pear');

内连接

SELECT
    a,
    fruit_a,
    b,
    fruit_b
FROM
    basket_a
INNER JOIN basket_b
    ON fruit_a = fruit_b;

• 使用 INNER JOIN 进行连接，使用 ON 指定连接条件
• 内连接检查第一个表(basket_a)中每一行，并与第二个表(basket_b)的每一行进行对比：如果指定字段的值相等（由 ON 指定的 fruit_a 和 fruit_b），则创建新行，并将该行添加到结果集中。

左外连接

SELECT
    a,
    fruit_a,
    b,
    fruit_b
FROM
    basket_a
LEFT JOIN basket_b 
   ON fruit_a = fruit_b;

• 使用 LEFT JOIN 进行连接，使用 ON 指定条件
  • LEFT JOIN 左侧的表为“左表”，此例中的 basket_a
  • LEFT JOIN 右侧的表为“右表”，此例中的 basket_b
• 从左表（basket_a）中选择数据，并将它的 fruit_a字段的值和右表（basket_b）的 fruit_b 字段值进行对比（由 ON 指定）：
  • 如果相等，将创建一个包含两张表记录的新行，并将新行添加到结果集中
  • 如果不相等，只创建只包含左表记录，右边记录全部为 NULL 的新行，并将新行添加到结果集中

LEFT JOIN 是 LEFT OUTER JOIN 的简写

右外连接

SELECT
    a,
    fruit_a,
    b,
    fruit_b
FROM
    basket_a
RIGHT JOIN basket_b ON fruit_a = fruit_b;

• 和左外连接原理相同
• 只是以右表的记录为准，对右表选择数据，如果左表有相等的记录则包含左表数据；反则则只包含右表数据，左表为 NULL

全外连接

SELECT
    a,
    fruit_a,
    b,
    fruit_b
FROM
    basket_a
FULL OUTER JOIN basket_b 
    ON fruit_a = fruit_b;

• 返回两张表中所有记录
• 如果两张表有符合条件的记录，则返回两张表的记录
• 如果有一张表没有符合条件的记录，则以 NULL 填充

交叉连接

• 没有 ON 指定条件
• 这是一个非常耗费资源的操作

SELECT 字段列表 FROM t1 CROSS JOIN t1;

-- 等价于
SELECT 字段列表 FROM t1,t2;

-- 使用INNER JOIN模拟：ON的条件始终为真
SELECT 字段列表 FROM t1 INNER JOIN t2 ON 1=1;

自然连接

基于连接表中，相同字段名创建的隐式连接。

SELECT 字段列表 FROM t1 NATURAL [INNER, LEFT, RIGHT] JOIN t2;

SELECT * FROM city NATURAL INNER JOIN country;

• 预期将隐式使用两个表中的同名字段 country_id 进行连接
• 实际结果确是空结果，原因是两个表中还有一个同名字段 last_update

自连接

所谓自连接，是指连接两方都是同一张表，常用应用场景是父子分类。

SELECT FROM 字段列表 FROM 表 AS t1 INNER JOIN 表 AS t2 ON t1.字段名1 = t2.字段名2; 

示例：