如何查找包含另一表多个指定值的行可通过JOIN或无子查询方式实现吗

AI 小北

2026-10-20

你的需求是查询同时存在accepted和closed两种状态的产品，除了你现在使用的子查询写法，还有以下几种更优的实现方案：

方案1：JOIN + 分组聚合（无嵌套子查询，性能最优）

仅需扫描1次PRICES表，是数据量较大场景下的首选方案：

SELECT p.*
FROM PRODUCTS p
INNER JOIN PRICES pr ON p.id = pr.product_id
WHERE pr.status IN ('accepted', 'closed')
GROUP BY p.id, p.name
HAVING COUNT(DISTINCT pr.status) = 2;

说明：如果你的业务中同一个产品的同一状态不会重复出现在PRICES表中，可以去掉DISTINCT关键字，进一步提升执行效率。

方案2：EXISTS 子查询（比IN子查询性能更稳定）

如果倾向用子查询实现，EXISTS的执行效率通常远高于IN，因为它匹配到符合条件的记录就会终止扫描，不需要拉取整个子查询的结果集做匹配：

SELECT * FROM PRODUCTS p
WHERE EXISTS (SELECT 1 FROM PRICES pr1 WHERE pr1.product_id = p.id AND pr1.status = 'accepted')
AND EXISTS (SELECT 1 FROM PRICES pr2 WHERE pr2.product_id = p.id AND pr2.status = 'closed');

方案3：INTERSECT 集合运算（可读性最高）

如果你的数据库支持INTERSECT集合运算（MySQL 8.0+、PostgreSQL、Oracle均支持），可以直接取两个状态的product_id交集，逻辑一目了然：

SELECT p.* FROM PRODUCTS p
WHERE p.id IN (
    SELECT product_id FROM PRICES WHERE status = 'accepted'
    INTERSECT
    SELECT product_id FROM PRICES WHERE status = 'closed'
);