Don't understand why these two queries give different results

Question

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 = '[email protected]'
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...

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 = '[email protected]'
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.

Answer 1

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 = '[email protected]' 
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

Answer 2

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 = '[email protected]'
and (a.attr_code_Ref = 4119 or a.individual_ref is null)
group by e.individual_ref

and you get the same results

Answer 3

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.

Answer 4

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 = '[email protected]'
GROUP BY
        e.individual_ref