不明白为什么这两个查询会产生不同的结果

I have three tables (for sake of argument) individual, email and attribute. individual_Ref the foreign key that links individual to email and attribute.

我有三个表(为了便于讨论)个人、电子邮件和属性。personal_ref将个人链接到电子邮件和属性的外键。

It isn't necessary for a given individual to be represented on the attribute table as they may never have had an attribute added and they can appear more than once if they have more than one attributes.

不需要在属性表中表示给定的个体，因为它们可能从来没有添加过属性，如果它们有多个属性，则可以出现多次。

I want get a list of individual references and a count of a particular attribute for them. But need to search by email address as it is allowed for individuals to share email addresses (don't get me started)...

我想要一个单独引用的列表和一个特定属性的计数。但是需要通过电子邮件地址搜索，因为个人可以共享电子邮件地址。

My first stab was

我第一次尝试是

select e.individual_ref, count(a.attr_Code_ref)
from email e left join attribute a on e.individual_Ref = a.individual_ref
where e.email_Address = 'example.email@adomain.net'
and a.attr_code_Ref = 4119 
group by e.individual_ref

using a left join to ensure I get an individual ref from email if one exists and to ensure I get a result if there is an individual ref in email but not in attribute. Or so I thought since this returns no rows but...

使用左连接确保我从电子邮件中获得一个单独的ref(如果存在的话)，并确保如果电子邮件中有一个单独的ref(而不是属性)，我将得到一个结果。我想既然它不返回行，但是…

select e.individual_ref, 
(select count(a.attr_Code_ref) from attribute a where a.attr_code_Ref = 4119 and a.individual_ref = e.individual_ref)
from email e
where e.email_Address = 'example.email@adomain.net'
group by e.individual_REf

returns one row with an individual_Ref and a count of 0

返回一个带有独立编号的行，并且计数为0。

I'm not suggesting SQL is broken more that my understanding is... so I guess "what's my confusion?" is the question.

我并不是说SQL被破坏的更多，我的理解是……所以我猜“我的困惑是什么?”

4 个解决方案

#1

When converting a subquery to a LEFT JOIN, the correlated WHERE conditions in the subquery go to the ON clause of the join, not to the WHERE clause:

当将子查询转换为左连接时，子查询中的相关WHERE条件将转到连接的ON子句，而不是WHERE子句:

SELECT  e.individual_ref, count(a.attr_Code_ref)
FROM    email e
LEFT JOIN
        attribute a
ON      a.individual_ref = e.individual_Ref
        AND a.attr_code_Ref = 4119 
WHERE   e.email_Address = 'example.email@adomain.net'
GROUP BY
        e.individual_ref

#2

the following part causes the join to alter:

以下部分导致连接发生改变:

from email e left join attribute a on e.individual_Ref = a.individual_ref 
where e.email_Address = 'example.email@adomain.net' 
and a.attr_code_Ref = 4119

By placing a where clause on the a.attr_code you have turned the left join into an inner join e.g. where no attribute record exists, it returns null, which fails the where clause. (since a.attr_code_ref can't be 4119, there was no record.)

把where子句放在a上。attr_code您已经将左连接转换为内部连接，例如，如果不存在属性记录，则返回null，这将导致where子句失败。(因为一个。attr_code_ref不能是4119，没有记录)

You would need to allow a.attr_code_ref = 4199 or a.attr_code_ref is null

你需要允许a。attr_code_ref = 4199或a。attr_code_ref为空

#3

Change the first one in:

改变第一个:

select e.individual_ref, count(a.attr_Code_ref)
from email e left join attribute a on e.individual_Ref = a.individual_ref
where e.email_Address = 'example.email@adomain.net'
and (a.attr_code_Ref = 4119 or a.individual_ref is null)
group by e.individual_ref

and you get the same results

结果是一样的

#4

In the first query, you are joining the two tables and looking for any rows that correspond to both your conditions - there are none.

在第一个查询中，您将连接两个表，并查找与您的条件相对应的任何行——没有。

In the second query, you are getting the rows from the first table the correspond to the email condition (there is one), and the number of rows that correspond to your second condition from the other table, there are 0 of those.

在第二个查询中，您将从第一个表中获取与电子邮件条件(有一个)对应的行，以及与另一个表中的第二个条件对应的行数，其中有0个。

#1