多表产品筛选中，SQL多次使用IN替代INTERSECT是否可行？

核心结论 #

在单条SQL查询中使用2个及以上的IN子查询完全合规，这是符合SQL标准的写法，非常适合你这种从不同关联表中筛选同时满足多个条件的场景（比如同时匹配属性、选项、分类的产品）。

你的写法为什么有效 #

你当前的查询逻辑是通过多个AND product_id IN (...)实现“交集筛选”：每个IN子查询从对应的关联表（属性表、选项表）提取符合条件的产品ID集合，多个IN条件叠加相当于取这些集合的交集，最终得到同时满足所有筛选条件的产品。

示例代码（你的原写法）：

SELECT COUNT(*) FROM `test_oc_product` 
WHERE product_id IN ( 
                        SELECT product_id 
                        FROM test_oc_product_option 
                        WHERE option_id = '21' AND value = 'Red'  
                    )
AND product_id IN ( 
                        SELECT product_id 
                        FROM test_oc_product_attribute 
                        WHERE attribute_id = '10' 
                )

这个写法逻辑清晰，能准确筛选出同时具备指定选项（Red，option_id=21）和指定属性（attribute_id=10）的产品，完全匹配你的需求。

关于INTERSECT的限制 #

你遇到的INTERSECT不可行问题，本质是因为INTERSECT要求所有参与运算的子查询返回的列结构完全一致：列数、数据类型、顺序都必须匹配。

• 可行写法：所有子查询都只返回product_id（同类型单列），因此能正常计算交集
• 不可行写法：SELECT *返回多列、SELECT COUNT(*)返回数值类型，和其他子查询的product_id（整数类型单列）结构不匹配，因此报错

相比之下，IN子查询没有这个限制：主查询可以灵活选择返回内容（比如统计数、全表字段），只要筛选条件基于product_id匹配即可，适用性更广。

可选优化写法 #

如果数据量较大，也可以考虑用EXISTS子查询替代IN，部分数据库（如MySQL）对EXISTS的半连接优化更高效：

SELECT COUNT(*) FROM `test_oc_product` p
WHERE EXISTS (
    SELECT 1 FROM test_oc_product_option po
    WHERE po.product_id = p.product_id 
      AND po.option_id = '21' AND po.value = 'Red'
)
AND EXISTS (
    SELECT 1 FROM test_oc_product_attribute pa
    WHERE pa.product_id = p.product_id 
      AND pa.attribute_id = '10'
)

或者用JOIN方式（注意配合DISTINCT避免重复计数）：

SELECT COUNT(DISTINCT p.product_id) 
FROM `test_oc_product` p
JOIN test_oc_product_option po ON p.product_id = po.product_id
JOIN test_oc_product_attribute pa ON p.product_id = pa.product_id
WHERE po.option_id = '21' AND po.value = 'Red'
  AND pa.attribute_id = '10'

附：简化后的表结构与数据 #

CREATE TABLE test_oc_product (
  product_id int(11) NOT NULL AUTO_INCREMENT,
  model varchar(64) NOT NULL,
  sku varchar(64) NOT NULL,
   PRIMARY KEY (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO test_oc_product (product_id, model, sku) VALUES
(1, 'A1', 'A1'),
(2, 'A2', 'A2'),
(3, 'A3', 'A3'),
(4, 'AA', 'AA'),
(5, 'A5', 'A5'),
(6, 'A6', 'A6'),
(7, 'A7', 'A7'),
(8, 'A8', 'A8');

CREATE TABLE test_oc_product_attribute (
  product_id int(11) NOT NULL,
  attribute_id int(11) NOT NULL,
  text text NOT NULL,
  PRIMARY KEY (product_id, attribute_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO test_oc_product_attribute (product_id, attribute_id, text) VALUES 
('1', '10', 'Attribute Value 1'), 
('2', '11', 'Attribute Value 2');

CREATE TABLE test_oc_product_option (
  product_option_id int(11) NOT NULL AUTO_INCREMENT,
  product_id int(11) NOT NULL,
  option_id int(11) NOT NULL,
  value text NOT NULL,
   PRIMARY KEY (product_option_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO test_oc_product_option (product_option_id, product_id, option_id, value) VALUES 
(NULL, '1', '21', 'Red'), 
(NULL, '2', '21', 'Red'),
(NULL, '3', '21', 'Blue'),
(NULL, '4', '10', 'S');

内容的提问来源于stack exchange，提问作者SergeTkach