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相同。

注意：LATERAL JOIN 受 Postgres 支持，但在 MySQL 8.0.14 及更高版本才受支持。

一对一 (1-1)

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

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)。这是可能的，因为每个电子邮件都唯一标识一个客户。这表示一对一关系，因为每个客户只能有一个配置文件，并且一个配置文件只能属于一个客户。

在一对一关系中，JOIN 很简单。您可以使用传统的INNER JOIN来组合客户和他们的个人资料。这是一个查询示例：

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

当对这两个表执行INNER JOIN时，我们将得到一个结果集，其中每一行都包含来自两个表的列，并根据关系标准进行匹配。这是一个结果集示例：

当您使用INNER JOIN和指定的连接条件连接customers和profiles表时，结果集中的每一行都代表一个客户及其相应的个人资料。换句话说，我们包含了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;

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

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

为了解决这种重复，我们可以将LATERAL JOIN与 JSON 聚合结合使用。JSON 聚合通过将多个相关记录合并到一个 JSON 数组字段中来帮助避免重复，而LATERAL JOIN则有助于提高可读性并优化性能。

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

如您所见，LATERAL JOIN 与 JSON 聚合结合使用可产生更清晰的结果集，没有不必要的重复。Jenny 的所有帖子都被合并到一个 JSON 数组中，将之前结果集中的三行转换为一行。

注意：在此上下文中不讨论多对多（m-n）关系，因为它描述了两个一对多关系，因此将支持相同类型的 JOIN