Search Postgresql Archives

Re: Difference between ON and WHERE in JOINs

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



> 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
> 

The query you wrote is equivalent to this:

SELECT profil, count(og.name)
FROM ldap l
LEFT JOIN ... ug ON (l.uid = ug.uid)
LEFT JOIN (SELECT * FROM ... WHERE rne = '...' AND type = '...') og ON
(og.id = ug.idgroupe)
WHERE l.rne = '' AND l.type=''

As soon as you start putting the right-hand tables in the outer-most where
clause you have to deal with the fact that the outer join can cause those
columns to be null.  If you compare those columns to a literal value then
you are saying that you don't want that column to be NULL and by extension
you really meant to use an inner join.

If you had put:

WHERE l.rne = '' AND l.type = '' AND (og.type = 'g' OR og.type IS NULL)

That would give you the behavior such that if og.type is a known value AND
it is not 'g' then the item should NOT be counted.  If the value is unknown
or 'g' then count it.

As for a rule-of-thumb I would avoid non-relational conditions in the ON
clause all-together and move them into a sub-query as I showed above.  The
WHERE clause is reserved for the left side of the join hierarchy by default.
The ON clause is reserved for inter-relation comparisons.  When cobbling
together multiple JOINs I would also suggest making use of liberal newlines
as well as parentheses.  Lastly knowing the correct answer is not that
common; just starting building up the query piece-by-piece and keep in mind
that you have to deal with the NULLs introduced by the OUTER JOIN.  How you
deal with them is query specific and cannot be "rule-of-thumbed".

Yes, this is all more verbose but you've now separated the relational filter
and the joining into the designated areas making interpreting the query
easier.  Throw in usage of CTE/WITH and the important portion of the query
can be made succinct by moving the sub-queries and filters to the top and
out of the way.  You can refactor, if necessary, when you are done.

David J.








-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux