SQL 中的 JOIN 是什么？

传统 JOIN

您将遇到的最常见的 JOIN 操作形式是 INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL JOIN 和 CROSS JOIN。为了演示这些 JOIN 类型，我们将使用以下 customers 和 orders（修改后的）表：

SELECT
    *
FROM
    customers
INNER JOIN orders
    ON customers.customer_id = orders.customer_id;

SELECT
    *
FROM
    customers
LEFT JOIN orders
    ON customers.customer_id = orders.customer_id;

SELECT
    *
FROM
    customers
RIGHT JOIN orders
    ON customers.customer_id = orders.customer_id;

SELECT
    *
FROM
    customers
FULL JOIN orders
    ON customers.customer_id = orders.customer_id;

  (SELECT *
   FROM customers
   LEFT JOIN orders ON customers.customer_id = orders.customer_id)
UNION ALL
  (SELECT *
   FROM customers
   RIGHT JOIN orders ON customers.customer_id = orders.customer_id
   WHERE customers.customer_id IS NULL );

t1.r1 + t2.r1
t1.r1 + t2.r2
t1.r1 + t2.r3
t1.r2 + t2.r1
t1.r2 + t2.r2
t1.r2 + t2.r3
t1.r3 + t2.r1
t1.r3 + t2.r2
t1.r3 + t2.r3

LATERAL JOIN

与传统 JOIN 相比，Lateral JOIN 提供了一种不同的语法来组合表。在 LATERAL JOIN 中，第二个表表示为子查询，并且 JOIN 条件在子查询的 WHERE 子句中定义。

根据 PostgreSQL 文档：

“LATERAL 关键字可以放在子 SELECT FROM 项之前。这允许子 SELECT 引用 FROM 列表中出现在它之前的 FROM 项的列。（如果没有 LATERAL，则每个子 SELECT 都是独立评估的，因此无法交叉引用任何其他 FROM 项。）”

换句话说，LATERAL JOIN 就像一个 foreach 循环，其中 PostgreSQL 迭代结果集中的每一行，并使用每一行来评估子查询。

以下是 customers 表和 orders 表的相关查询：

SELECT *
FROM customers
LEFT JOIN LATERAL
  (SELECT *
   FROM orders
   WHERE orders.customer_id = customers.customer_id ) AS ALIAS ON TRUE;

以下是使用 LATERAL JOIN 后的结果表：

在本例中，结果将与 LEFT JOIN 和 FULL JOIN 相同。

注意： Postgres 支持 LATERAL JOIN，但仅在 MySQL 版本 > 8 中支持

一对一 (1-1)

在一对一关系中，一个表中的每个记录都与另一个表中的一个记录精确关联，反之亦然。以下是一个 SQL 模式示例，演示了 customers 表和 profiles 表之间的一对一关系：

CREATE TABLE customers (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    email TEXT NOT NULL
);

CREATE TABLE profiles (
    name TEXT NOT NULL,
    gender TEXT NOT NULL,
    age TEXT NOT NULL,
    customer_email TEXT NOT NULL,
    CONSTRAINT fk_profiles_customer_email FOREIGN KEY (customer_email) REFERENCES customers (email) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE UNIQUE INDEX customers_email_key ON customers(email);

CREATE UNIQUE INDEX profiles_customer_email_key ON profiles(customer_email);

在本例中，我们使用 customers 表中的唯一字段 (email) 作为 profiles 表中的外键 (customer_email)。这是可能的，因为每个电子邮件都唯一标识一个客户。这表示一对一关系，因为每个客户只能有一个 profile，而一个 profile 只能属于一个客户。

在一对一关系中，JOIN 非常简单。您可以使用传统的 INNER JOIN 将客户与其 profile 组合在一起。这是一个示例查询：

SELECT
    *
FROM
    customers
INNER JOIN profile
    ON customers.email = profiles.customer_email;

当在两个表之间执行 INNER JOIN 时，我们将获得一个结果集，其中每行都包含来自两个表的列，并根据关系条件进行匹配。以下是一个示例结果集：

当您使用 INNER JOIN 和指定的连接条件连接 customers 表和 profiles 表时，结果集中的每一行都代表一个客户及其对应的 profile。换句话说，我们包括来自 customers 表的 id 和 email 列，以及来自 profiles 表的这些客户的 name、gender 和 age。

一对多 (1-n)

在一对多关系中，一个表中的每个记录可以与另一个表中的多个记录关联。以下是一个 SQL 模式示例，演示了 users 表和 posts 表之间的一对多关系：

CREATE TABLE users (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    email TEXT NOT NULL
);

CREATE TABLE posts (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    author_id INTEGER NOT NULL,
    CONSTRAINT fk_posts_author_id FOREIGN KEY (author_id) REFERENCES users (id) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE UNIQUE INDEX users_email_key ON users(email);

这意味着一个用户可以拥有多个帖子，但每个帖子仅链接到一个用户。

在处理一对多关系时，传统的 JOIN 可能会导致数据重复。考虑以下查询：

SELECT
    user.id, user.email, post.id AS post_id, post.title AS post_title
FROM
    user
INNER JOIN post
    ON user.id = post.authorId;

以下是基于上述查询的示例结果集：

正如您在结果集中看到的那样，珍妮的 email 和 id 在多行中为她的每个帖子重复。随着我们加入更多嵌套关系，结果集可能会因冗余数据而变得臃肿。这使得查询效率降低（因为它需要更多带宽来传输结果），并且可能使数据更难以解释。

为了解决这种重复问题，我们可以将 LATERAL JOIN 与 JSON 聚合配对。JSON 聚合通过将多个相关记录整合为单个字段（JSON 数组）来帮助避免重复，而 LATERAL JOIN 有助于提高可读性并可以优化性能。

以下是使用 LATERAL JOIN 与 JSON 聚合的结果集示例：

如您所见，LATERAL JOIN 与 JSON 聚合相结合，可产生更简洁的结果集，且没有不必要的重复。珍妮的每个帖子都被整合到一个 JSON 数组中，将之前结果集中的三行转换为一行。

注意： 多对多 (m-n) 关系在此上下文中未讨论，因为它描述了两个 1-n 关系，因此将通过相同类型的 JOIN 来支持