Re: Simple OUTER JOIN doubt

On Fri, Oct 27, 2006 at 04:22:36PM -0500, Russ Brown wrote:
> Sandro Dentella wrote:
> > On Fri, Oct 27, 2006 at 02:42:06PM -0500, Russ Brown wrote:
> >> Looks to me like it is because you're referencing vota_punteggio in the
> >> WHERE clause.
> >>
> >> Try something like this:
> >>
> >> SELECT f.id, f.titolo, p.voto
> >> FROM film_film f
> >> LEFT OUTER JOIN vota_punteggio p
> >> ON f.id = p.film_id
> >> AND p.user_id = 2
> >>
>
> My thinking was that by putting the conditions in the WHERE clause you
> were restricting the rows returned by the entire query, while putting
> them in the ON clause only restricts the matching in the JOIN itself.

It took me a while to understand this sentence (here is 1 past midnight...if
that's an excuse... ;-). I went back to wikipedia

http://en.wikipedia.org/wiki/Outer_join#Left_outer_join

Left outer join

A left outer join is very different from an inner join. Instead of limiting
results to those in both tables, it limits results to those in the "left"
table (A). This means that if the ON clause matches 0 records in B, a row in
the result will still be returned but with NULL values for each column
from B.

in my case 'film' is my left side, 'score' the right. When ON clause match 0
records on B the row is returned but in can be filtered by following WHERE
conditions. Putting it inside the ON clause, garantees that the row is added
and not filtered (and doesn't require the condition p_user_id IS NULL).

If this is correct it would be nice to add this case to the wikipedia
description about outer joins. I think it is not at all self evident. I
don't think I'm skilled enought to do that thought...

Thanks a lot to you and Tom for you help
sandro
*:-)

--
Sandro Dentella *:-)
http://www.tksql.org TkSQL Home page - My GPL work