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
anouter 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()
returnsmore than zero). I know this is specific to my data but I really don't get the behaviour.
Thanks for your help,
JC