Re: Difference between ON and WHERE in JOINs

Le 19/09/2012 02:47, David Johnston a écrit :
> There is no difference in your example. Conceptually though I
> suggest using only table-table conditions in an ON clause and placing
> any table-value conditions into the where.

This is how I use it usually.

> The main time you get differences is when you use OUTER JOIN
> constructions since the order of filtering can affect the final
> result. With an inner join the order of evaluation doesn't matter
> since all valid results will have a record from both sides of the
> join.

Ok, I didn't know what the "trigger" was : outer joins, ok.

I have this query working :
select profil,count(og.name)
from ldap l
left join uidinoldgroups ug on l.uid=ug.uid
left join oldgroups og on og.id=ug.idgroupe and og.rne='0410030k' and og.type='g'
where l.profilgeneral='P'
and l.rne='0410030k'
group by l.profil

But if I put :
and og.rne='0410030k' and og.type='g'
in the where part (what you suggested and what I did naturally), I get 0 results which is quite confusing (because it's an outer join).

Also, if I replace these with full outer joins, I still get 0 results.

With my data, if I replace og.type='g' with og.type='m' I get MOST OF my resultats back (those where the count() returns more than zero). I know this is specific to my data but I really don't get the behaviour.

Thanks for your help,

JC