Re: Difference between ON and WHERE in JOINs

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 !