Postgresql - JOIN

Postgresql join 用于连接两个或多个表的数据。下图是所有的 join 类型级语法

使用方式包括 inner join、left join、right join、full outer join。

首先创建两个表：

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');

新建的表中有一些相同的数据，比如apple和orange

basket_a

basket_b

内连接

内连接检查第一个表 ( basket_a) 中的每一行。它将fruit_a列中的值 fruit_b 与第二个表 ( basket_b) 中每一行的列中的值进行比较。如果这些值相等，则内部连接会创建一个包含两个表中的列的新行，并将这个新行添加到结果集中。

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

左连接

使用左连接子句将basket_a表与basket_b表连接起来。在左连接上下文中，第一个表称为左表，第二个表称为右表。

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

左连接开始从左表中选择数据。它将 fruit_a 列中的值与 basket_b 表中 fruit_b 列中的值进行比较。

如果这些值相等，则左连接会创建一个包含两个表的列的新行，并将此新行添加到结果集中。（参见结果集中的第 1 行和第 2 行）。

如果值不相等，左连接还会创建一个包含两个表中的列的新行，并将其添加到结果集中。但是，它 basket_b 用空值填充右表 ( ) 的列。（参见结果集中的第 3 行和第 4 行）。

要从左表中选择右表中没有匹配行的行，请使用带 WHERE 子句的左连接。例如：

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

右连接

右连接是左连接的反转版本。右连接开始从右表中选择数据。它将右表中每一行的 fruit_b 列中的每个值与 fruit_a 表中每一行的 fruit_a 列中的每个值进行比较。

如果这些值相等，则右连接会创建一个包含两个表中的列的新行。

如果这些值不相等，右连接还会创建一个包含两个表中的列的新行。但是，它用 NULL 填充左表中的列。

以下语句使用右连接将basket_a表与basket_b表连接起来：

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;

要返回一个表中没有匹配行的行，请使用带有WHERE如下子句的完全连接：

SELECT
    a,
    fruit_a,
    b,
    fruit_b
FROM
    basket_a
FULL JOIN basket_b
   ON fruit_a = fruit_b
WHERE a IS NULL OR b IS NULL;