如何无需子查询，通过聚合列过滤比价服务的非聚合查询结果？

Great question! You absolutely can rewrite this query to avoid nested subqueries, while fixing the repetition, performance concerns, and ORM compatibility issues you mentioned. Here's a cleaner, more efficient approach using a pre-aggregated join:

SELECT 
    p1.id AS p1_id, 
    p1.name AS p1_name, 
    p1.price AS p1_price, 
    p2.id AS p2_id, 
    p2.name AS p2_name, 
    p2.price AS p2_price, 
    m.time
FROM Product p1
-- Precompute the lowest competitor price for each product from site 1
JOIN (
    SELECT 
        _m.fromProduct_id,
        MIN(_p2.price) AS min_competitor_price
    FROM ProductMatch _m
    JOIN Product _p2 ON _m.toProduct_id = _p2.id
    GROUP BY _m.fromProduct_id
) AS competitor_min ON p1.id = competitor_min.fromProduct_id
-- Join back to get all matching competitor details
LEFT JOIN ProductMatch m ON m.fromProduct_id = p1.id
LEFT JOIN Product p2 ON m.toProduct_id = p2.id
WHERE p1.site_id = 1
  AND p1.price < competitor_min.min_competitor_price;

Key Improvements Over Your Original Query: #

• No repeated joins: The original subquery duplicated the full Product → ProductMatch → Product join chain. Here, we only compute the minimum competitor price once in the small aggregated subquery, then join it back to the main Product table.
• Better performance: Aggregating first reduces the data set size before joining back to the main tables. Database optimizers typically handle join-based filtering more efficiently than IN subqueries, especially on large datasets.
• ORM-friendly: Most ORMs (like Hibernate, Entity Framework, or Django ORM) handle joins to aggregated subqueries far better than nested IN clauses. You can often map this structure directly using ORM query builders without falling back to raw SQL.

Note on Edge Cases: #

• Products with no matching competitors are automatically excluded (since MIN(_p2.price) would be NULL, and p1.price < NULL evaluates to FALSE), which matches the behavior of your original subquery (which used JOIN instead of LEFT JOIN in the nested logic).
• If you wanted to include products with no competitors (though your original logic doesn't), you could switch the JOIN to LEFT JOIN and adjust the WHERE clause to handle NULL values.

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