LEFT JOIN不保留SELECT查询中出现的记录

Question

我使用以下SQL选择语句来获取产品或葡萄酒的子集:

SELECT pv.SkProdVariantId AS id,
pa.Colour AS colour,
FROM Dim.ProductVariant AS pv
JOIN ProductAttributes_new AS pa
ON pv.SkProdVariantId = pa.SkProdVariantId
WHERE pv.ProdTypeName = 'Wines'

生成的该表的长度为3905。我想要这些产品的所有交易数据。现在我正在使用这个select语句

SELECT c.CalDate AS timestamp,
f.SkProductVariantId AS sku_id,
f.Quantity AS quantity
FROM fact.FTransactions AS f
LEFT JOIN Dim.Calendar AS c
ON f.SkDateId = c.SkDateId
LEFT JOIN (    
SELECT pv.SkProdVariantId AS id,
pa.Colour AS colour,
FROM Dim.ProductVariant AS pv
JOIN ProductAttributes_new AS pa
ON pv.SkProdVariantId = pa.SkProdVariantId
WHERE pv.ProdTypeName = 'Wines'  
) AS s
ON s.id = f.SkProductVariantId
WHERE c.CalDate LIKE '%2019%'

日历日期是正确的，但是的数字是唯一的返回的产品是5,648，而不是从选择查询中预期的3,905。

为什么我的LEFT JOIN在第一个选择查询不工作，因为我期望它，请?

谢谢你的帮助!

Answer 1

如果您想要查询的所有行，它需要是LEFT JOIN中的第一个引用。那么，我猜你想在2019年交易:

select . . .
from (SELECT pv.SkProdVariantId AS id, pa.Colour AS colour,
FROM Dim.ProductVariant pv JOIN
ProductAttributes_new pa
ON pv.SkProdVariantId = pa.SkProdVariantId
WHERE pv.ProdTypeName = 'Wines'
) s LEFT JOIN
(fact.FTransactions f JOIN
Dim.Calendar c
ON f.SkDateId = c.SkDateId AND
c.CalDate >= '2019-01-01' AND
c.CalDate < '2020-01-01'
)
ON s.id = f.SkProductVariantId;

注意，这里假设CalDate是一个日期而不是字符串。LIKE只能用于字符串。

Answer 2

你误解了外部连接的工作原理。请看Gordon的回答和我的请求评论。

关于任务:看起来你想选择2019年的交易，但你想把结果限制在葡萄酒产品上。我们通常在WHERE子句中限制查询结果。您可以使用IN或EXISTS。

SELECT
c.CalDate AS timestamp,
f.SkProductVariantId AS sku_id,
f.Quantity AS quantity
FROM fact.FTransactions AS f
INNER JOIN Dim.Calendar AS c ON f.SkDateId = c.SkDateId
WHERE DATEPART(YEAR, c.CalDate) = 2019
AND f.SkProductVariantId IN
(
SELECT pv.SkProdVariantId
FROM Dim.ProductVariant AS pv
WHERE pv.ProdTypeName = 'Wines'
);

(我已经删除了到ProductAttributes_new的连接，因为它似乎在这个查询中不起任何作用。)