查询关联Stock表库存数量>0的Products记录的最优SQL语句

嘿，这个需求我太熟悉了！要找出所有关联库存（Stock）中至少有一条记录数量大于0的产品（Products），最高效的SQL写法绝对是用EXISTS半连接查询——这是处理这类“存在性检查”场景的最优解，尤其当你的Stock表数据量较大时，优势会特别明显。

最优SQL语句 #

SELECT p.*
FROM Products p
WHERE EXISTS (
    SELECT 1
    FROM Stock s
    WHERE s.product_id = p.id
      AND s.quantity > 0
);

为什么这个写法最高效？ #

• 半连接特性：EXISTS是数据库的半连接逻辑，当引擎找到第一条满足s.product_id = p.id且s.quantity > 0的Stock记录后，就会立刻停止对当前Product的匹配扫描，不用遍历所有关联的Stock行，能大幅减少不必要的计算。
• 轻量查询：子查询里只需要SELECT 1，不需要返回实际的字段数据，数据库可以跳过字段读取的步骤，做更多底层优化。
• 索引友好：如果给Stock表建立联合索引(product_id, quantity)，数据库直接通过索引就能完成存在性判断，完全不需要回表读取Stock的实际数据，性能会再上一个台阶。

其他常见写法的不足 #

IN子查询写法 #

SELECT *
FROM Products
WHERE id IN (
    SELECT product_id
    FROM Stock
    WHERE quantity > 0
);

这个写法能得到正确结果，但当Stock中存在大量重复的product_id时，数据库需要先对子查询结果去重，额外增加了计算开销，数据量越大，性能差距越明显。

JOIN+DISTINCT写法 #

SELECT DISTINCT p.*
FROM Products p
JOIN Stock s ON p.id = s.product_id
WHERE s.quantity > 0;

这种写法会先把所有匹配的Product和Stock行做连接，生成一个大的中间结果集，再通过DISTINCT去重。当符合条件的Stock记录很多时，中间结果集会占用大量内存和IO资源，效率远不如EXISTS。

关键优化建议 #

一定要给Stock表创建联合索引：

CREATE INDEX idx_stock_product_quantity ON Stock(product_id, quantity);

这个索引完美覆盖了子查询的过滤条件，数据库可以直接通过索引快速定位到符合条件的记录，彻底避免全表扫描。

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