sql-in

SQL IN 运算符：使用值列表进行筛选

SQL IN 运算符是一种强大且易读的数据筛选方式，通过检查列的值是否与指定列表中的任何值匹配。它替代链式多个 OR 条件的简洁方式，使您的查询更清晰、更易于理解。

静态列表的基本用法

IN 运算符的主要用途是在 WHERE 子句中提供一个文字值列表。

语法

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

示例

考虑一个 products 表：

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    category VARCHAR(50),
    price DECIMAL(10, 2)
);

INSERT INTO products (id, name, category, price) VALUES
(1, 'Laptop', 'Electronics', 1200.00),
(2, 'T-Shirt', 'Apparel', 25.00),
(3, 'Coffee Maker', 'Home Goods', 80.00),
(4, 'Jeans', 'Apparel', 75.00),
(5, 'Smartphone', 'Electronics', 950.00);

要查找属于 ‘Apparel’ 或 ‘Home Goods’ 类别的所有产品，您可以使用 IN：

SELECT id, name, category FROM products
WHERE category IN ('Apparel', 'Home Goods');

输出

id	name	category
2	T-Shirt	Apparel
3	Coffee Maker	Home Goods
4	Jeans	Apparel

作为比较，使用 OR 的相同查询会更冗长：

WHERE category = 'Apparel' OR category = 'Home Goods'

随着列表的增长，IN 的优势将更加明显。

使用 NOT IN 排除值

您可以使用 NOT IN 检索不匹配列表中任何值的所有行。例如，查找所有非 ‘Electronics’ 类别的产品：

SELECT id, name, category FROM products
WHERE category NOT IN ('Electronics');

常见陷阱：NOT IN 与 NULL

开发人员的一个主要陷阱是在包含 NULL 值的列表中使用 NOT IN。像 WHERE column NOT IN (value1, NULL) 这样的条件将永远不会返回任何行（除非列本身也是 NULL 并且您的数据库设置以特定方式处理 NULL 比较）。这是因为 col <> NULL 的评估结果是 UNKNOWN，而不是 TRUE 或 FALSE。该查询实际上变得无法满足。

-- 由于存在 NULL，此查询将返回空结果集
SELECT * FROM products WHERE category NOT IN ('Electronics', NULL);

-- 解决方案：确保您的列表不包含 NULL，或者使用 NOT EXISTS 等替代方法。
SELECT * FROM products p
WHERE NOT EXISTS (
  SELECT 1 FROM (VALUES ('Electronics'), (NULL)) AS excluded(cat)
  WHERE p.category = excluded.cat
);

将 IN 与子查询一起使用

IN 更动态、更强大的用法是从另一个查询（子查询）提供值列表。子查询必须只返回一列。

示例

假设您有一个 special_offers 表，并想要查找所有当前正在优惠的产品。

CREATE TABLE special_offers (product_id INT, campaign VARCHAR(50));
INSERT INTO special_offers VALUES (1, 'Winter Sale'), (4, 'Denim Deals');

-- 查找正在优惠的产品的完整产品详情
SELECT * FROM products
WHERE id IN (SELECT product_id FROM special_offers);

输出

id	name	category	price
1	Laptop	Electronics	1200.00
4	Jeans	Apparel	75.00

性能考量：IN 与 JOIN 与 EXISTS

对于上面的子查询示例，通常可以通过 JOIN 获得相同的结果。现代数据库优化器非常优秀，但从历史上看，在某些复杂场景中，选择不同的方法可能会影响性能。

-- 使用 INNER JOIN，通常性能更优
SELECT p.*
FROM products p
INNER JOIN special_offers so ON p.id = so.product_id;

• IN 非常易读，但对于非常大的子查询列表，性能可能较差，因为数据库可能会首先将整个列表加载到内存中。
• JOIN 通常是此类查询的首选。它允许数据库优化器选择最有效的连接策略（例如，哈希连接、嵌套循环）。如果您需要从连接的表中选择列，它也更灵活。
• EXISTS 是另一种强大的替代方案，当您只需要检查是否存在匹配行而不在乎值本身时，通常表现出色。它可能比 IN 更快，因为它一旦找到单个匹配项就可以停止搜索。

专业提示： 对于简单检查，选择最易读的选项（IN 或 JOIN）。对于性能关键的查询，请使用数据库的 EXPLAIN 或 ANALYZE 工具来比较 IN、JOIN 和 EXISTS 的查询计划，以查看哪种方法对您的特定数据和模式最有效。