sql-in-vs-exists

SQL - IN 与 EXISTS

在 SQL 中，IN 和 EXISTS 运算符都用于子查询以过滤数据。尽管它们通常可以实现相似的结果，但它们的工作方式根本不同，这可能会影响查询的可读性和性能。

IN 运算符检查外部查询中的值是否与子查询返回的结果集中的任何值匹配。数据库引擎首先完整执行子查询，将所有结果收集到一个临时集合中，然后对照此集合检查外部查询的每一行。

SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT column_name FROM another_table);

查找所有已下订单的客户：

-- 查找所有 ID 在 Orders 表中客户 ID 列表内的客户。
SELECT Name, City
FROM Customers
WHERE ID IN (SELECT CustomerID FROM Orders);

这通常非常直观且易于阅读。它在概念上类似于提供一个静态列表：WHERE ID IN (1, 2, 3)。

EXISTS 是一个布尔运算符，用于检查子查询中是否存在行。如果子查询返回一行或多行，则返回 TRUE；否则返回 FALSE。EXISTS 运算符使用关联子查询，这意味着内部查询会针对外部查询的每一行进行评估。

SELECT column_name(s)
FROM table_name t1
WHERE EXISTS (SELECT 1 FROM another_table t2 WHERE t2.some_col = t1.some_col);

注意：在 EXISTS 子查询中使用 SELECT 1 或 SELECT * 是一种惯例。实际选择的列并不重要，因为 EXISTS 只关心是否返回了任何行。

查找所有已下订单的客户：

-- 对于每个客户，检查是否存在至少一个与其 ID 相关的订单。
SELECT c.Name, c.City
FROM Customers c
WHERE EXISTS (SELECT 1 FROM Orders o WHERE o.CustomerID = c.ID);

EXISTS 可能更高效，因为它在找到第一个匹配行后即可停止处理子查询，而 IN 必须首先处理整个子查询。

过去关于“EXISTS 对于大型子查询更快”的性能建议在今天已不再那么重要。现代查询优化器非常智能，通常可以在幕后将 IN 查询重写为更高效的 JOIN 或 EXISTS 查询，反之亦然。

因此，选择通常应基于清晰性和正确性，尤其是在处理 NULL 值时。

特征	IN	EXISTS
主要用例	将列与一组值（静态列表或子查询结果）进行比较。	检查另一个表中是否存在相关数据。
可读性	通常更直观，尤其适用于简单比较。	阅读可能更复杂，但对于检查关系非常清晰。
性能	适用于小型静态列表。对于子查询，在现代数据库中性能通常与 `EXISTS` 相似。	传统上更适合大型子查询，因为它能“短路”。通常会转换为高效的“半连接（semi-join）”。
NULL 值处理	可能很棘手。`col IN (1, 2, NULL)` 不会匹配 `col` 为 `NULL` 的行。如果子查询返回任何 `NULL` 值，`col NOT IN (SELECT ...)` 可能会产生意想不到的结果。	可预测地处理 `NULL` 值。`EXISTS` 子查询只返回 true 或 false，不受其结果集中 `NULL` 值的影响。

选择能更清晰表达你意图的运算符。对于检查值列表，使用 IN。对于检查相关数据是否存在，使用 EXISTS。对于性能关键的查询，务必同时测试两者，并使用 EXPLAIN 来分析你的特定数据库版本生成的查询计划。