Thread: "left join" not working?
Hi, This query: select c.id_currency,max(p.modified_on) from currency c left join price_line p using (id_currency) where p.id_line=1 group by c.id_currency; doesn't list all c.id_currency's, only those with a price_line. However this one does: select c.id_currency,max(p.modified_on) from currency c left join price_line p on (p.id_currency = c.id_currency and p.id_line=1) group by c.id_currency; How come? Thanks,
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... Didn't do any tests, it's just a guess... Best, Oliveiros ----- Original Message ----- From: "Louis-David Mitterrand" <vindex+lists-pgsql-sql@apartia.org> To: <pgsql-sql@postgresql.org> Sent: Friday, February 12, 2010 11:14 AM Subject: [SQL] "left join" not working? > Hi, > > This query: > > select c.id_currency,max(p.modified_on) from currency c left join > price_line p using (id_currency) where p.id_line=1 group by > c.id_currency; > > doesn't list all c.id_currency's, only those with a price_line. However > this one does: > > select c.id_currency,max(p.modified_on) from currency c left join > price_line p on (p.id_currency = c.id_currency and p.id_line=1) group by > c.id_currency; > > How come? > > Thanks, > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
> where p.id_line=1 this filters rows after join was applied. Try this select c.id_currency,max(p.modified_on) from currency c left join price_line p using (id_currency) where (p.id_line=1 or p.id_line is null) group by c.id_currency; > Hi, > This query: > select c.id_currency,max(p.modified_on) from currency c left join > price_line p using (id_currency) where p.id_line=1 group by > c.id_currency; > doesn't list all c.id_currency's, only those with a price_line. However > this one does: > select c.id_currency,max(p.modified_on) from currency c left join > price_line p on (p.id_currency = c.id_currency and p.id_line=1) group by > c.id_currency; > How come? > Thanks, > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > Яндекс.Почта. Письма есть. Спама - нет. http://mail.yandex.ru/nospam/sign
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!
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@apartia.org> To: <pgsql-sql@postgresql.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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >