如何确保带有过滤器的外连接仍然返回所有所需的行？

Question

想象一下，我在数据库中有两个表，如下所示：

products:    
product_id  name
----------------
1           Hat
2           Gloves
3           Shoes

sales:
product_id  store_id  sales
----------------------------
1           1         20
2           2         10

现在我想做一个查询来列出 store_id = 1 的所有产品及其销售额。我的第一个破解方法是使用左连接，并过滤到 store_id我想要一个 null store_id，以防产品在 store_id = 1 时没有获得任何销售，因为我想要列出所有产品：

SELECT name, coalesce(sales, 0)
FROM products p
LEFT JOIN sales s ON p.product_id = s.product_id
WHERE store_id = 1 or store_id is null;

当然，这不会按预期工作，而是我得到：

name   sales
---------------
Hat    20
Shoes  0

没有手套！这是因为 Gloves 确实获得了销售，只是不是在 store_id = 1 时获得了销售，因此 WHERE 子句已将其过滤掉。

那么我如何才能获取特定商店的所有产品及其销售情况的列表？

以下是创建测试表的一些查询：

create temp table test_products as 
select 1 as product_id, 'Hat' as name;
insert into test_products values (2, 'Gloves');
insert into test_products values (3, 'Shoes');
create temp table test_sales as
select 1 as product_id, 1 as store_id, 20 as sales;
insert into test_sales values (2, 2, 10);

更新：我应该注意，我知道这个解决方案：

SELECT name, case when store_id = 1 then sales else 0 end as sales
FROM test_products p
LEFT JOIN test_sales s ON p.product_id = s.product_id;

但是，它并不理想......实际上，我需要为 BI 工具创建此查询，以便工具可以简单地向查询添加一个where子句并获得所需的结果。此工具不支持将所需的 store_id 插入此查询中的正确位置。所以我正在寻找其他选择（如果有的话）。

Answer 1

将 WHERE 条件添加到 LEFT JOIN 子句以防止行丢失。

SELECT p.name, coalesce(s.sales, 0)
FROM   products p
LEFT   JOIN sales s ON p.product_id = s.product_id
                   AND s.store_id = 1;

编辑附加请求：

我假设您可以操作 SELECT 项目？那么这应该可以完成工作：

SELECT p.name
      ,CASE WHEN s.store_id = 1 THEN coalesce(s.sales, 0) ELSE NULL END AS sales
FROM   products p
LEFT   JOIN sales s USING (product_id)

_{还简化了本例中的连接语法。}

Answer 2

我不熟悉 SQL，但请尝试一下：

SELECT name, coalesce(sales, 0)
FROM products p
LEFT JOIN sales s ON p.product_id = s.product_id AND store_id = 1

您不需要整个查询上的 where，只需在您的连接上