在左联接中未从表A中获取所有记录 [英] Not getting all records from Table A in left join

问题描述

我有一张食谱的质量保证状态表，并且想要选择所有丢弃"记录，包括它们重叠的完美"状态的信息.但是，我只得到交叉点，我想要所有丢弃"记录.

I have a table of Quality Assurance statuses for recipes and want to select all 'discard' records, including info from 'perfect' statuses where they overlap. However, I'm only getting the intersection and I want all 'discard' records.

我想执行左连接，这将使我来自recipe_qa表中的所有丢弃"行以及所有完美"行.

I want to perform a left join that will give me all 'discard' rows from the recipe_qa table, joined with any 'perfect' rows.

select *
from recipe_qa as bad
left join recipe_qa as good on good.id = bad.id
where bad.type = 'discard'
and good.type = 'perfect'

上面的查询返回的行中有id的"perfect"和"discard"记录(24行).我想要的是一个查询，该查询将为我提供所有丢弃"行(76行)，具有完美" ID或没有相应行的null.

The query above is returning rows where there is a 'perfect' and a 'discard' record (24 rows) for the id. What I want is a query that will give me all the 'discard' rows (76 rows), with either the 'perfect' id or a null where there is no corresponding row.

这是一个小提琴: http://sqlfiddle.com/#!2/faa49 /4

Here's a fiddle: http://sqlfiddle.com/#!2/faa49/4

我在做什么错了?

推荐答案

在处理左(或外)联接时，您必须考虑何时施加数据限制与何时创建笛卡尔.

When working with left (or outer) joins you have to consider when the data limit is imposed vs when the Cartesian is created.

假设您要来自一个表(A)的所有记录，而只希望来自另一个表(B)的那些记录.执行联接时，NULL值将出现在B中(A的记录不匹配).针对B字段添加限制条件(where子句)，实际上会从A中消除您想要的记录；因为where子句在联接之后执行.效果与您从INNER JOIN开始一样！

Say you want all records from one table (A) and only those that match in the other (B). When the join is executed, NULL values will be present in B (unmatched records to A). Adding a limiting criteria (where clause) against B fields, will in-fact eliminate records you want from A; as the where clause executes AFTER the join. This has the same effect as if you started with an INNER JOIN!

使用外部联接时，只需将B表上的限制条件移动到联接即可避免这种情况.这样，在施加限制后，将生成笛卡尔坐标，以确保表A中的所有记录"保留所有记录.如您所见，如果不在联接上，则限制是在笛卡尔坐标之后施加的，因此将删除空值，并且您不再获得所有记录"，因此将取消LEFT联接.就像您首先要进行INNER联接一样.这就是为什么要执行OR语句以返回null且该值也有效的原因，但前提是它必须是NOT NULL列(或null类型没有语义)，正如LC在下面的注释中指出的那样.

This situation can be avoided simply by moving the limiting criteria on the B table to the join when using outer joins. This way the Cartesian is generated AFTER the limit has been imposed ensuring the "All Records" from table A remains all records. If not on the join, as you've seen, the limit is imposed after the Cartesian and thus null values are removed, and you no longer get "All Records" thus the LEFT join is negated. It's as if you were doing an INNER join in the first place. This is why doing an OR statement to return nulls and the value also works but only if it is a NOT NULL column (or type of null has no semantic meaning) as LC points out in comments below.

在这种情况下，您的good.type的where子句消除了左联接的结果，因此可以在联接中添加条件，以在生成笛卡尔坐标之前强制限制(允许存在空值)

In this case your where clause of good.type is eliminating the results of the left join so either add the criteria to the join which forces the limits before the Cartesian is generated (allowing the nulls to exist)

select *
from recipe_qa as bad
left join recipe_qa as good 
  on good.id = bad.id
 and good.type = 'perfect'
where bad.type = 'discard'

http://sqlfiddle.com/#!2/faa49/8/0

或使用为空的条件不从左联接中排除记录.在以下评论中指出了这种风险.

OR use an is null condition to not exclude records from left join. This has some risks indicted in the below comments.

select *
from recipe_qa as bad
left join recipe_qa as good 
  on good.id = bad.id
where bad.type = 'discard'
 and (good.type = 'perfect' or good.type is null)

这篇关于在左联接中未从表A中获取所有记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！