SQL Left Join过滤后丢失行 [英] SQL Left Join losing rows after filtering

问题描述

我有一个多表联接(示例中仅显示了两个)，在这里我需要保留基表中的所有行.显然，我使用了LEFT JOIN来包含基表中的所有行.如果没有WHERE子句，它的效果会很好–当右表中不存在任何行时，仍然会显示左表中的行，而右表中的列仅显示0.数据集中的前两行是左"表中的标签"和右"表中的行数"，按标签"分组.当标签没有分配来自Table2的值时，我只需要计数0.

I have a multi-table join (only two shown in example) where I need to retain all rows from the base table. Obviously I use a LEFT JOIN to include all rows on the base table. Without the WHERE clause it works great – When a row doesn’t exist in the Right table the row from the Left table still shows, just with a 0 from the column in the Right table. The first two rows in the dataset are Labels from the Left table and Count of rows from the Right table, grouped by Label. All I want is a count of 0 when a label does not have a value from Table2 assigned.

表1

Label | FK
----------
Blue  | 1
Red   | 2
Green | 3

表2

Values | Color | Date
---------------------------
Dog    | 1     | 02/02/2010
Cat    | 2     | 02/02/2010
Dog    | 1     | 02/02/2010
Cat    | 2     | 02/02/2010

查询:

SELECT 1.Label, COUNT(2.values)
FROM Table1 1
    LEFT JOIN Table2 2 ON 1.fk = 1.pk
GROUP BY 1.Label

好的结果集-没有过滤器

Good Result Set - No filters

Blue  | 2
Red   | 2
Green | 0

太好了！

我的问题是，当我添加过滤条件以从Right表中删除行时，我的Left联接行中的行被删除(将它们清零)，Left行被删除了.我需要保留左行，即使它们的计数被过滤为零也是如此.

My issue is that when I add filtering criteria to remove rows from the Right table the row is removed for my Left join rows (zeroing them out), the Left rows are dropped. I need the Left rows to remain even if their count is filtered down to zero.

SELECT 1.Label, COUNT(2.values)
FROM Table1 1
    LEFT JOIN Table2 2 ON 1.fk = 1.pk
WHERE 2.Date BETWEEN '1/1/2010' AND '12/31/2010'    
GROUP BY 1.Label

Bummer结果集-过滤后

Bummer Result Set - After Filters

Blue | 2
Red  | 2

公爵！

那么，到底是什么?我是否需要获取带有过滤后的数据集的临时表，然后将其连接到Left表?我想念什么? 谢谢！

So, what the hell? Do I need to get a temp table with the filtered dataset THEN join it to the Left table? What am I missing? Thanks!

进行第二次联接或递归联接.获得我的好"联接表，获得第二个已过滤"表，然后左联接它们

Do a second join or recursive join. Get my "good" join table, get a second "filtered" table, then LEFT JOIN them

推荐答案

您正在筛选where中的第二个表.值可能为NULL，而NULL不能通过比较.

You are filtering on the second table in the where. The values could be NULL and NULL fails the comparisons.

将where条件移至on子句:

SELECT 1.Label, COUNT(2.values)
FROM Table1 1
    LEFT JOIN Table2 2 ON 1.fk = 1.pk and
              2.Date BETWEEN 1/1/2010 AND 12/31/2010    
GROUP BY 1.Label

这篇关于SQL Left Join过滤后丢失行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！