本页内容受版权保护 · 已添加水印 · 禁止任何形式转载
SQL 实战编程题（GitHub 高频题） · 查找类

找出某月没下过单的所有用户。(LEFT JOIN + IS NULL / NOT EXISTS)

Find all users who did not place any orders in a given month. (using LEFT JOIN

答案语言

考察要点

这道题面试官想看你是否掌握 SQL 中查找不存在关系（Anti-Join）的两种核心实现方式：LEFT JOIN ... IS NULL 和 NOT EXISTS。同时，考察你对 SQL 执行顺序、NULL 值处理、索引优化，以及不同方案在性能上的细微差异是否有深刻理解。

核心概念

本题的核心是实现 "反连接"（Anti-Join），即从一个集合（用户表）中，排除掉在另一个集合（某月的订单表）中存在的元素。

1. LEFT JOIN ... IS NULL

这是一种非常直观的实现方式。它通过左连接将两个表关联，对于左表（users）中存在、但右表（orders）中没有匹配记录的行，右表的所有列都会被填充为 NULL。我们利用这个特性来筛选目标用户。

数据示例: 假设我们有 users 表和 orders 表：

users 表

user_iduser_name
1Alice
2Bob
3Carol

orders 表

order_iduser_idorder_time
10112023-10-05 10:00:00
10232023-10-15 12:30:00
10312023-11-02 09:00:00

目标： 找出在 2023 年 10 月没有下过单的用户。预期结果：Bob (user_id: 2)。

LEFT JOIN 的中间结果（ON 条件中包含日期过滤）： FROM users u LEFT JOIN orders o ON u.user_id = o.user_id AND o.order_time >= '2023-10-01' AND o.order_time < '2023-11-01'

u.user_idu.user_nameo.order_ido.user_ido.order_time
1Alice10112023-10-05 10:00:00
2BobNULLNULLNULL
3Carol10232023-10-15 12:30:00

最后通过 WHERE o.order_id IS NULL 筛选，即可得到 user_id 为 2 的 Bob。

2. NOT EXISTS

NOT EXISTS 是一种基于子查询的实现方式，语义上更贴近“不存在”。它遍历外层查询的每一行，然后检查该行数据是否满足 NOT EXISTS 内部子查询的条件。

工作原理： 对于 users 表中的每个用户，执行 NOT EXISTS 中的子查询。

  • 如果子查询能找到至少一条记录（即该用户在指定月份有订单），EXISTS 返回 true，NOT EXISTS 返回 false，该用户被排除。
  • 如果子查询返回空集（即该用户在指定月份无订单），EXISTS 返回 false，NOT EXISTS 返回 true，该用户被选中。

NOT EXISTS 的一个重要优点是，子查询一旦找到第一条匹配记录就会立即停止并返回，无需扫描所有匹配项，这在某些场景下效率更高。

3. NOT IN

NOT IN 是另一种选择，但存在严重陷阱。col NOT IN (subquery) 在子查询结果集中包含 NULL 值时，整个 NOT IN 条件会返回 UNKNOWN（在 WHERE 子句中等同于 false），导致查询结果为空。除非能确保子查询的 user_id 列永不为 NULL（例如该列是主键或有 NOT NULL 约束），否则不推荐使用。

多种解法

假设表结构如下：

sql
 1CREATE TABLE users (
 2    user_id INT PRIMARY KEY,
 3    user_name VARCHAR(50)
 4);
 5

 6CREATE TABLE orders (
 7    order_id INT PRIMARY KEY,
 8    user_id INT,
 9    order_time DATETIME,
10    -- 在 user_id 和 order_time 上建立复合索引以优化查询
11    INDEX idx_user_time (user_id, order_time)
12);

解法 1：LEFT JOIN ... IS NULL

这是最通用、最直观的解法。

  • 版本兼容性: ✅ 所有 SQL 数据库版本均支持。
  • SQL 实现:
sql
 1-- 查找 2023 年 10 月没有下过单的用户
 2SELECT
 3    u.user_id,
 4    u.user_name
 5FROM
 6    users u
 7LEFT JOIN
 8    orders o ON u.user_id = o.user_id
 9             AND o.order_time >= '2023-10-01'
10             AND o.order_time < '2023-11-01' -- 日期范围条件必须在 ON 子句中
11WHERE
12    o.order_id IS NULL; -- 或者 o.user_id IS NULL
  • 复杂度:
    • 时间复杂度：若有索引，通常为 O(Nuserslog⁡Norders)O(N_{users} \log N_{orders}) 或 O(Nusers+Norders)O(N_{users} + N_{orders})，取决于优化器选择的连接算法（如 Nested Loop Join 或 Hash Join）。
    • 空间复杂度：O(Nusers)O(N_{users})，需要存储左表数据和部分匹配结果。
  • 索引依赖:
    • users(user_id) 主键索引。
    • orders(user_id, order_time) 复合索引至关重要。它能高效地在 ON 子句中完成 user_id 匹配和 order_time 范围过滤。

解法 2：NOT EXISTS

这是在很多数据库（尤其是 MySQL）中性能更优、语义更清晰的解法。

  • 版本兼容性: ✅ 所有 SQL 数据库版本均支持。
  • SQL 实现:
sql
 1-- 查找 2023 年 10 月没有下过单的用户
 2SELECT
 3    u.user_id,
 4    u.user_name
 5FROM
 6    users u
 7WHERE
 8    NOT EXISTS (
 9        SELECT 1
10        FROM orders o
11        WHERE o.user_id = u.user_id
12          AND o.order_time >= '2023-10-01'
13          AND o.order_time < '2023-11-01'
14    );
  • 复杂度:
    • 时间复杂度：与 LEFT JOIN 类似，但由于“找到即停”的特性，在 orders 表中一个用户有多条记录时，通常比 LEFT JOIN 更快。
    • 空间复杂度：O(1)O(1) 或很小，因为它不需要在内存中构建大的中间连接结果集。
  • 索引依赖:
    • orders(user_id, order_time) 复合索引是性能的关键。对于 users 表的每一行，子查询都能通过索引快速判断是否存在匹配的订单。

常见低分回答

  1. LEFT JOIN 中将过滤条件误放在 WHERE 子句：这是最经典的错误。

    sql
     1-- 错误示例
     2SELECT u.user_id FROM users u
     3LEFT JOIN orders o ON u.user_id = o.user_id
     4WHERE o.order_id IS NULL
     5  AND o.order_time >= '2023-10-01'; -- 错误！

    原因：WHERE 子句在 LEFT JOIN 生成中间结果之后执行。o.order_time >= ... 会将所有 o.order_time 为 NULL 的行（即我们想找的目标用户）直接过滤掉，导致结果集永远为空。

  2. 使用 NOT IN 却不处理 NULL 值：如果 orders.user_id 列允许为 NULL，这个查询会出错。

    sql
     1-- 潜在风险示例
     2SELECT user_id FROM users
     3WHERE user_id NOT IN (SELECT user_id FROM orders WHERE ...);

    原因：如果子查询返回的 user_id 列表中包含 NULL，user_id NOT IN (..., NULL) 的结果永远是 UNKNOWN，导致外层查询无任何返回。

  3. 在 WHERE 子句的列上使用函数：这会导致索引失效，引发全表扫描。

    sql
     1-- 性能低下示例
     2SELECT ... FROM orders WHERE DATE_FORMAT(order_time, '%Y-%m') = '2023-10';

    原因：数据库无法使用 order_time 上的索引来定位数据，必须对每一行的 order_time 计算 DATE_FORMAT 函数，然后才进行比较。正确做法是使用范围查询 col >= start AND col < end。

  4. 使用 COUNT 进行判断：虽然逻辑上可行，但通常比 LEFT JOIN 或 NOT EXISTS 效率低。

    sql
     1-- 效率较低示例
     2SELECT u.user_id
     3FROM users u
     4WHERE (
     5    SELECT COUNT(*) FROM orders o
     6    WHERE o.user_id = u.user_id AND o.order_time >= '2023-10-01' AND o.order_time < '2023-11-01'
     7) = 0;

    原因：COUNT(*) 需要统计出所有匹配的行数，而 EXISTS 只需要找到一行即可。对于一个用户有大量订单的场景，COUNT 的开销远大于 EXISTS。

工程实践与优化

  • 索引选择：对于此问题，orders 表的 (user_id, order_time) 复合索引是性能的命脉。user_id 在前，可以快速关联用户；order_time 在后，可以在 user_id 确定的 B+ 树子树上进行高效的范围扫描。
  • 执行计划分析 (EXPLAIN)：
    • LEFT JOIN 解法：理想的 EXPLAIN 结果中，Extra 字段会显示 Using where; Using not exists。这表明 MySQL 优化器已经将 LEFT JOIN ... IS NULL 智能地转换为了一个高效的 Anti-Join 操作。
    • NOT EXISTS 解法：EXPLAIN 会显示这是一个 DEPENDENT SUBQUERY，但如果索引 idx_user_time 被有效利用，子查询的成本会非常低（通常是 index lookup）。
  • 数据倾斜：如果 orders 表极大（百亿级），而 users 表相对较小（百万级），NOT EXISTS 通常表现更稳定。因为它对每个用户独立执行子查询，避免了 LEFT JOIN 可能因少数“超级用户”（订单极多）而产生巨大中间结果集的风险。
  • 日期范围处理：始终使用 column >= start_date AND column < end_date_plus_one_day 的半开半闭区间形式。这对于 DATETIME 和 TIMESTAMP 类型是精确且索引友好的，避免了 BETWEEN 在处理 DATETIME 时可能遗漏当天时间部分的问题。

常见追问与延伸题目

  1. 追问：如果 orders 表的数据量增长 100 倍（达到百亿级别），这条 SQL 性能会怎样？怎么优化？

    • 回答要点 1 (性能瓶颈)：两条 SQL 的性能都会显著下降，主要瓶颈在于对 orders 表的访问。即使有索引，百亿级别的数据也会让索引树变得非常深，I/O 成本急剧增加。LEFT JOIN 可能会因内存压力过大而将中间结果写入磁盘，导致性能雪崩。
    • 回答要点 2 (优化方案)：
      • 分区表 (Partitioning)：这是应对海量数据的首选方案。在 orders 表上按 order_time 进行范围分区（如按月分区）。这样，查询 2023-10 的数据时，数据库只需扫描 p_202310 这一个分区，数据量从百亿骤降到当月数据（可能几亿），查询性能得到数量级的提升。
      • 确保最优索引：再次确认 (user_id, order_time) 复合索引在每个分区上都存在。
      • 优先 NOT EXISTS：在大数据量下，NOT EXISTS 的“找到即停”和低内存占用的优势会更加明显，是更稳健的选择。

  2. 追问：这个问题反过来，如何找出“只在 2023 年 10 月下过单”的用户？

    • 回答要点: 这个问题更复杂，需要满足两个条件：1) 在 10 月有订单；2) 在其他月份没有订单。这通常使用 GROUP BY 和 HAVING 来解决。
    sql
     1SELECT
     2    user_id
     3FROM
     4    orders
     5GROUP BY
     6    user_id
     7HAVING
     8    -- 订单的最小时间大于等于10月1日
     9    MIN(order_time) >= '2023-10-01'
    10    -- 并且订单的最大时间小于11月1日
    11AND MAX(order_time) < '2023-11-01';

    或者使用 CASE 表达式计数：

    sql
     1SELECT
     2    user_id
     3FROM
     4    orders
     5GROUP BY
     6    user_id
     7HAVING
     8    -- 统计10月份的订单数 > 0
     9    COUNT(CASE WHEN order_time >= '2023-10-01' AND order_time < '2023-11-01' THEN order_id END) > 0
    10    -- 并且统计非10月份的订单数 = 0
    11AND COUNT(CASE WHEN order_time < '2023-10-01' OR order_time >= '2023-11-01' THEN order_id END) = 0;

  3. 追问：如果换成 PostgreSQL 或分布式数据库（如 TiDB），答案有什么不同？

    • 回答要点 1 (PostgreSQL)：PostgreSQL 的优化器非常成熟，LEFT JOIN ... IS NULL 和 NOT EXISTS 两种写法通常会被优化成相同的执行计划（如 Hash Anti Join），性能差异不大。开发者可以根据语义清晰度自由选择，NOT EXISTS 的可读性可能稍好。
    • 回答要点 2 (TiDB/分布式数据库)：在分布式环境下，Join 的执行策略是关键。数据库会根据表的大小、数据分布和统计信息选择不同的 Join 算法（如 Hash Join, Index Nested Loop Join）。
      • LEFT JOIN 可能会触发大规模的数据混洗（Shuffle），网络开销大。
      • NOT EXISTS（或 NOT IN）在 TiDB 中可以被优化为 Anti Semi Join，如果 users 表是小表，可以将其广播（Broadcast）到存储 orders 表的各个节点上进行计算，避免 orders 表的大量数据移动，性能可能更优。因此，在分布式数据库中，NOT EXISTS 的优势可能更明显。
← Previous
找出每个部门工资排名前 3 的员工。(分组取 top N)
Next →
找出连续登录 7 天的用户。