Re: Simple OUTER JOIN doubt

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
>

this works in fact, and it's simpler. But I don't really understard why I
should put it in this way.

On Fri, Oct 27, 2006 at 03:55:35PM -0400, Tom Lane wrote:
> [ scratches head... ] Looks all right to me. Are you sure you copied
> what you typed accurately? Does EXPLAIN show that a left join is being
> used?

here is the explain for both queries:

cinemino=# explain SELECT f.id, f.titolo, p.voto
FROM film_film f
LEFT OUTER JOIN vota_punteggio p
ON (f.id = p.film_id)
WHERE
(p.user_id = 2 OR p.user_id IS NULL)
;
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Merge Right Join (cost=18.00..106.26 rows=170 width=76)
Merge Cond: ("outer".film_id = "inner".id)
Filter: (("outer".user_id = 2) OR ("outer".user_id IS NULL))
-> Index Scan using vota_punteggio_film_id on vota_punteggio p (cost=0.00..59.93 rows=1630 width=12)
-> Sort (cost=18.00..18.42 rows=170 width=72)
Sort Key: f.id
-> Seq Scan on film_film f (cost=0.00..11.70 rows=170 width=72)
(7 righe)

cinemino=# explain 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)
;
QUERY PLAN
-------------------------------------------------------------------------------------------------
Hash Left Join (cost=12.05..24.67 rows=170 width=76)
Hash Cond: ("outer".id = "inner".film_id)
-> Seq Scan on film_film f (cost=0.00..11.70 rows=170 width=72)
-> Hash (cost=12.03..12.03 rows=8 width=8)
-> Bitmap Heap Scan on vota_punteggio p (cost=2.03..12.03 rows=8 width=8)
Recheck Cond: (user_id = 2)
-> Bitmap Index Scan on vota_punteggio_user_id (cost=0.00..2.03 rows=8 width=0)
Index Cond: (user_id = 2)
(8 righe)

BTW: I'm no able to read explain output, but it's a long time I want to
start studying them. I think I should start studying chapter 13, other
hints on this subject?

--
Sandro Dentella *:-)
e-mail: sandro(at)e-den(dot)it
http://www.tksql.org TkSQL Home page - My GPL work