九月 1, 2023
摘要:在本教程中,您将了解各种 PostgreSQL 连接,包括内连接、左连接、右连接和完全外连接。
目录
PostgreSQL 连接用于根据相关表之间公共列的值来组合一个(自连接)或多个表中的列。公共列通常是第一个表的主键列和第二个表的外键列。
PostgreSQL 支持内连接、左连接、右连接、完全外连接、交叉连接、自然连接以及一种称为自连接的特殊连接。
设置样例表
假设您有两个名为basket_a
和basket_b
的表,用于存储水果信息:
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
表中返回数据:
PostgreSQL 内连接
以下语句通过匹配fruit_a
和fruit_b
列中的值来连接第一个表 (basket_a
) 和第二个表 (basket_b
) :
SELECT
a,
fruit_a,
b,
fruit_b
FROM
basket_a
INNER JOIN basket_b
ON fruit_a = fruit_b;
内连接检查第一个表 (basket_a
) 中的每一行。它将fruit_a
列中的值与第二个表 (basket_b
) 中每行的fruit_b
列中的值进行比较。如果这些值相等,则内部联接将创建一个包含两个表中的列的新行,并将新行添加到结果集中。
下面的维恩图说明了内连接:
PostgreSQL 左连接
以下语句使用左连接子句将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 行)。
如果值不相等,左连接还会创建一个新行,其中包含两个表中的列,并将其添加到结果集中。但是,它用NULL
填充右表 (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;
输出是:
请注意,
LEFT JOIN
与LEFT OUTER JOIN
相同,因此您可以互换使用它们。
下面的维恩图说明了左连接,它返回左表中与右表中没有匹配行的行:
PostgreSQL 右连接
右连接是左连接的反转版本。右连接开始从右表中选择数据。它将右表中每行的fruit_b
列中的每个值与basket_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;
这是输出:
下面的维恩图说明了右连接:
同样,您可以通过添加WHERE
子句从右表中获取与左表中没有匹配行的行,如下所示:
SELECT
a,
fruit_a,
b,
fruit_b
FROM
basket_a
RIGHT JOIN basket_b
ON fruit_a = fruit_b
WHERE a IS NULL;
RIGHT JOIN
和RIGHT OUTER JOIN
是相同的,因此您可以互换使用它们。
下面的维恩图说明了右连接,它返回右表中的行,这些行在左表中没有匹配的行:
PostgreSQL 全外连接
完全外连接或完全连接返回一个结果集,其中包含左表和右表中的所有行,以及两侧的匹配行(如果有)。如果没有匹配,表的列将被填充为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;
结果如下:
下面的维恩图说明了完全外连接,该连接返回一个表中的行,而另一个表中没有对应的行:
下图显示了我们到目前为止讨论的所有 PostgreSQL 连接的详细语法:
在本教程中,您学习了如何使用各种 PostgreSQL 连接来组合多个相关表中的数据。