David,
Thanks for the verbose explanations, really useful.
However, in my case :
- type is never NULL
- there are no lines from uidinoldgroups that match the "FROM ldap" join.
But I think I got it :
drop table if exists tmpA;
drop table if exists tmpB;
create temp table tmpA (name varchar);
insert into tmpA values ('jack');
insert into tmpA values ('joe');
create temp table tmpB (name varchar, value int);
insert into tmpB values('jack',10);
-- case (1)
select a.name,COUNT(b.*)
from tmpA a
LEFT JOIN tmpB b ON a.name=b.name AND b.value>0
group by a.name
This gives :
Jack 1
Joe 0
But :
-- case (2)
select a.name,COUNT(b.*)
from tmpA a
LEFT JOIN tmpB b ON a.name=b.name
WHERE b.value>0
group by a.name
gives :
Jack 1
No mention of Joe.
Though :
-- case (3)
select a.name,COUNT(b.*)
from tmpA a
LEFT JOIN tmpB b ON a.name=b.name
WHERE (b.value>0 or b.value is null)
group by a.name
Brings back Joe. The WHERE clause is evaluated AFTER the JOIN.
A subtle concept difference that makes big differences in the results.
Many thanks for the enlightenment.
And also for making me look at CTE constructs which I did not know of.
They make things much clearer :
with b2 as (
select name,value
from tmpB
where value>0
)
SELECT a.name, count(b.*)
FROM tmpA a
LEFT JOIN b2 b ON a.name=b.name
GROUP BY a.name
Have a nice day, you made mine rich !
