Don't understand why these two queries give different results
I have three tables (for sake of argument) individual, email and attribute. individual_Ref the foreign key that links individual to email and attribute.
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开发者_JAVA百科 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...
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
I'm not suggesting SQL is broken more that my understanding is... so I guess "what's my confusion?" is the question.
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.)
You would need to allow a.attr_code_ref = 4199 or a.attr_code_ref is null
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:
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
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
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.
精彩评论