Re: "left join" not working?

Yes, because your WHERE is something that comes after the operation
of the LEFT JOIN, in practice, defeating the purpose you intented.

On your second query the p.id_line = 1 doesn't do that because it is part of
the LEFT JOIN itself..

HTH

Best,
Oliveiros d'Azevedo Cristina

----- Original Message -----
From: "Louis-David Mitterrand" <vindex+lists-pgsql-sql(at)apartia(dot)org>
To: <pgsql-sql(at)postgresql(dot)org>
Sent: Friday, February 12, 2010 11:57 AM
Subject: Re: [SQL] "left join" not working?

> On Fri, Feb 12, 2010 at 11:35:02AM -0000, Oliveiros C, wrote:
>> My first guess is that
>> NULL fails the condition on your WHERE clause,
>> p.id_line = 1
>>
>> So your WHERE clause introduces an additional level of filtering
>> that filters out the NULLs coming from the LEFT JOIN...
>
> So, if I understand correctly, a WHERE filters all results regardless of
> join conditions and can turn an OUTER JOIN into an INNER JOIN.
>
> Thanks for pointing that out!
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>