Re: Simple OUTER JOIN doubt

Sandro Dentella <sandro@e-den.it> writes:
> this works in fact, and it's simpler. But I don't really understard why I
> should put it in this way.

The other way seems to work for me:

regression=# create table film_film (id int, titolo text);
CREATE TABLE
regression=# create table vota_punteggio(film_id int, user_id int, voto int);
CREATE TABLE
regression=# insert into film_film values(1, 'one');
INSERT 0 1
regression=# insert into film_film values(2, 'two');
INSERT 0 1
regression=# insert into vota_punteggio values(1, 2, 10);
INSERT 0 1
regression=# SELECT f.id, f.titolo, p.voto
regression-# FROM film_film f LEFT OUTER JOIN vota_punteggio  p
regression-#  ON (f.id = p.film_id)
regression-# WHERE (p.user_id = 2 OR p.user_id IS NULL);
 id | titolo | voto
----+--------+------
  1 | one    |   10
  2 | two    |
(2 rows)

Can you put together a self-contained test case showing the problem?
What PG version are you running, anyway?  (There's a known bug in merge
right join in 8.1.0-8.1.3, but AFAIR the symptom is too many output rows
not too few.)

            regards, tom lane