Advanced Join Techniques: LATERAL Joins, Semi Joins, Anti Joins

KDnuggets

KDnuggets2026年6月18日

Advanced Join Techniques: LATERAL Joins, Semi Joins, Anti Joins

8.5内容质量

TL;DR · AI 摘要

LATERAL、Semi 和 Anti Joins 是 SQL 中处理复杂查询的高级技巧，适用于特定场景，如逐行处理函数结果、过滤存在或不存在匹配的行。

核心要点

LATERAL Joins 允许子查询引用 FROM 子句中前面的列，适用于逐行处理函数结果。
Semi Joins 返回与另一张表匹配的行，但不重复这些行，适用于存在性过滤。
Anti Joins 返回与另一张表无匹配的行，适用于排除性过滤。

结构提纲

按章节快速跳转。

§引言
INNER JOIN 和 LEFT JOIN 无法处理所有 SQL 查询，某些场景需要 LATERAL、Semi 和 Anti Joins。
§LATERAL Joins
LATERAL Joins 允许子查询引用 FROM 子句中前面的列，适用于逐行处理函数结果。
§Semi Joins
Semi Joins 返回与另一张表匹配的行，但不重复这些行，适用于存在性过滤。
§Anti Joins
Anti Joins 返回与另一张表无匹配的行，适用于排除性过滤。
§示例：统计单词出现次数
使用 LATERAL 和 regexp_matches() 函数统计 'bull' 和 'bear' 在 contents 列中的出现次数。

思维导图

用一张图看清主题之间的关系。

查看大纲文本（无障碍 / 无 JS 友好）

高级 Join 技术
- LATERAL Joins
  - 允许子查询引用前面的列
  - 适用于逐行处理函数结果
- Semi Joins
  - 返回匹配的行
  - 不重复这些行
- Anti Joins
  - 返回无匹配的行
  - 适用于排除性过滤

金句 / Highlights

值得收藏与分享的关键句。

LATERAL joins let a subquery in the FROM clause reference columns from earlier in the same FROM clause.
— 第 2 段
⬇︎ 下载 PNG 𝕏 分享到 X
Semi joins return rows where a match exists in another table, without duplicating those rows.
— 第 2 段
⬇︎ 下载 PNG 𝕏 分享到 X
Anti joins return rows where no match exists.
— 第 2 段
⬇︎ 下载 PNG 𝕏 分享到 X
regexp_matches() returns one row per match. To run it once per row of google_file_store and count all matches across the table, we put it in the FROM clause with LATERAL.
— 第 5 段
⬇︎ 下载 PNG 𝕏 分享到 X

#SQL#数据库#数据工程

打开原文

高级连接技术：LATERAL 连接、半连接、反连接 - KDnuggets

publ: 2026年6月18日

博客热门文章

主题人工智能职业建议计算机视觉数据工程数据科学语言模型机器学习 MLOps 自然语言处理编程 Python SQL

数据集

活动

资源快速参考指南推荐技术简报

广告

加入新闻通讯

#header end

/ad_wrapper

高级连接技术：LATERAL 连接、半连接、反连接

LATERAL 连接允许 FROM 子句中的子查询引用同一 FROM 子句中前面的列。半连接返回在另一张表中存在匹配的行，但不会重复这些行。反连接返回在另一张表中不存在匹配的行。

作者：

Nate Rosidi

，KDnuggets 市场趋势与 SQL 内容专家，2026年6月18日，在

SQL

# 引言

INNER JOIN 和 LEFT JOIN 处理大多数 SQL 查询。但有一小部分问题需要其他类型的连接：逐行计算集返回函数的结果，根据另一张表中是否存在行来过滤行，以及返回在另一张表中没有匹配的行。

三种不太常见的连接可以很好地处理这些问题。LATERAL 连接允许 FROM 子句中的子查询引用同一 FROM 子句中前面的列。半连接返回在另一张表中存在匹配的行，但不会重复这些行。反连接返回在另一张表中不存在匹配的行。

让我们探讨如何在实践中应用这些模式。

# LATERAL 连接

FROM 子句中的 LATERAL 子查询可以引用同一 FROM 子句中前面表的列。没有 LATERAL 的情况下，FROM 子句中的子查询是独立评估的，无法看到这些列。

这在调用集返回函数（每个输入返回多行的函数）时最为重要。集返回函数可以在 SELECT 列表中调用，但要在 FROM 子句中逐行应用它们到外部表的列，需要 LATERAL。

常见情况：

对数组列调用 unnest()，以每个数组元素生成一行
使用 'g' 标志调用 regexp_matches()，以每行提取所有匹配项
使用 FROM 子句中的相关子查询计算每个组的前 N 个结果
按行拆分 JSON 数组

#### // 示例：统计单词出现次数

这个问题要求我们统计 contents 列中单词 "bull" 和 "bear" 出现的次数。匹配必须不区分大小写，并且像 bullish 或 bearing 这样的子字符串应被排除。

数据：google_file_store 表的结构如下：

filename

contents

draft1.txt

The stock exchange predicts a bull market which would make many investors happy.

draft2.txt

The stock exchange predicts a bull market... but analysts warn... we are awaiting a bear market.

final.txt

The stock exchange predicts a bull market... a bear market. As always predicting the future market is uncertain...

代码：regexp_matches() 返回每个匹配一行。为了对 google_file_store 表的每一行运行一次，并统计表中所有匹配项，我们使用 LATERAL 将其放在 FROM 子句中。\m 和 \M 是 PostgreSQL 的单词边界锚点，这正是排除 "bullish" 和 "bearing" 的原因。

code

SELECT 'bull' AS word,
       COUNT(*) AS nentry
FROM google_file_store,
     LATERAL regexp_matches(LOWER(contents), '\m(bull)\M', 'g')
UNION ALL
SELECT 'bear' AS word,
       COUNT(*) AS nentry
FROM google_file_store,
     LATERAL regexp_matches(LOWER(contents), '\m(bear)\M', 'g');

#### // 输出

word

nentry

bull

3

bear

2

# 半连接

半连接（semi join）会从左表中返回那些在右表中至少存在一个匹配的行，且左表的每一行最多只出现一次。而 INNER JOIN 会在右表有多个匹配时重复左表的行。半连接则不会。

以下是两种 SQL 实现方式：

WHERE EXISTS (SELECT 1 FROM ...) WHERE col IN (SELECT col FROM ...)

EXISTS 是更通用的形式，因为它可以处理多列连接条件和相关子查询，而无需重写查询。

#### // 示例：查找高价值客户

这个问题要求我们找出至少下过一笔金额超过 100 美元订单的客户，并返回他们的客户 ID 和姓名。

数据：在线商店客户表（online_store_customers）和订单表（online_store_orders）的预览：

customer_id | customer_name --- | --- 1 | Alice Johnson 2 | Bob Smith 3 | Carol Williams ... | ... 10 | Jack Anderson

order_id | amount | status --- | --- | --- 101 | 150 | paid 102 | 200 | 103 | 75 | ... | ... | ... 115 | 9 | 450

代码：EXISTS 子查询会检查每个客户是否至少有一笔金额超过 100 美元的订单。SELECT 1 是一种惯例，因为 EXISTS 只关心是否有行返回，而不关心行中的具体内容。

code

SELECT
    c.customer_id,
    c.customer_name
FROM online_store_customers c
WHERE EXISTS (
    SELECT 1
    FROM online_store_orders o
    WHERE o.customer_id = c.customer_id
      AND o.amount > 100
);

如果我们使用 INNER JOIN，客户 1 会在结果中出现两次，因为有两个订单匹配。而 EXISTS 会只返回客户 1 一次。

Ivy Taylor

# 反连接（Anti Joins）

反连接（anti join）会从左表中返回那些在右表中没有匹配的行。它是半连接的反面。

LEFT JOIN ... WHERE right_table.col IS NULL WHERE NOT EXISTS (SELECT 1 FROM ...)

两者会产生相同的结果。在现代 PostgreSQL 版本中，NOT EXISTS 通常会产生更优的查询计划，并且读起来更直接。LEFT JOIN + IS NULL 模式较旧，当你还需要从右表中获取非匹配行的列时，这种模式会很有用。

#### // 示例：没有在 2020 年 4 月拨打电话的免费用户

这个问题要求我们返回那些在 2020 年 4 月没有拨过任何电话的免费用户。

数据：rc_calls 和 rc_users 表的预览：

user_id | call_id | call_date --- | --- | --- 1218 | 0 | 2020-04-19 01:06:00 1554 | | 2020-03-01 16:51:00 1857 | | 2020-03-29 07:06:00 1525 | | 2020-03-07 02:01:00 1910 | 39 | 2020-03-11 08:33:00

company_id | free | inactive --- | --- | --- 1884 | |

代码：日期过滤条件位于 ON 子句中，而不是 WHERE 子句中。这一区别正是使这个查询成为反连接的原因。如果将日期过滤条件放在 WHERE 子句中，会丢弃 LEFT JOIN 生成的 NULL 行，从而将其退化为 INNER JOIN。而将过滤条件放在 ON 子句中，即使免费用户在 2020 年 4 月没有符合条件的电话，也会生成一行，右表中的列会为 NULL，而 IS NULL 检查会保留这些行。

code

SELECT DISTINCT u.user_id
FROM rc_users u
LEFT JOIN rc_calls c
       ON u.user_id = c.user_id
      AND c.call_date BETWEEN '2020-04-01' AND '2020-04-30'
WHERE u.status = 'free'
  AND c.user_id IS NULL;

1575

# 结论

这三种连接方式解决了在使用 INNER JOIN 和 LEFT JOIN 时可能显得笨拙或错误的情况：

LATERAL 是在 FROM 子句中逐行调用返回集合的函数的方式。EXISTS 可以在不导致 INNER JOIN 重复的情况下返回“有匹配的行”。NOT EXISTS 或 LEFT JOIN + IS NULL 可以干净地返回“没有匹配的行”。

需要记住的模式很简单。当 INNER JOIN 产生你不想要的重复行时，使用 EXISTS。当你需要没有匹配项的行时，使用 NOT EXISTS 或 LEFT JOIN + IS NULL。当 FROM 子句中的子查询需要引用外部表的列时，添加 LATERAL。

在真实的 SQL 面试问题中练习这些技巧，语法就会变得自然。

Nate Rosidi 是一名数据科学家，目前从事产品战略工作。他还是兼职教授，教授分析课程，并且是 StrataScratch 的创始人，这是一个帮助数据科学家通过使用顶尖公司的真实面试问题来准备面试的平台。Nate 会撰写有关职业市场最新趋势的文章，提供面试建议，分享数据科学项目，并涵盖所有与 SQL 相关的内容。

Advanced Join Techniques: LATERAL Joins, Semi Joins, Anti Joins

TL;DR · AI 摘要

核心要点

结构提纲

思维导图

金句 / Highlights

高级连接技术：LATERAL 连接、半连接、反连接 - KDnuggets

高级连接技术：LATERAL 连接、半连接、反连接

# 引言

# LATERAL 连接

# 半连接

# 反连接（Anti Joins）

# 结论

更多相关内容

最新文章

热门文章