Re: BUG #2553: Outer join bug

On Thu, Jul 27, 2006 at 07:30:01PM +0000, Steven Adams wrote:
> select ia.name, iac.internal
> from information_assets as ia
> left outer join information_asset_categories as iac on(ia.category_id =
> iac.id)
> and ia.id = 21
>
> This causes all rows in information_assets to be returned despite the "and"
> clause. Adding "join information_assets as ia2 on(ia.id = ia2.id)" after
> the outer join corrects this.

http://www.postgresql.org/docs/8.1/interactive/queries-table-expressions.html#QUERIES-JOIN

LEFT OUTER JOIN

First, an inner join is performed. Then, for each row in T1
that does not satisfy the join condition with any row in T2, a
joined row is added with null values in columns of T2. Thus,
the joined table unconditionally has at least one row for each
row in T1.

The "and ia.id = 21" expression is part of the outer join condition
that restricts rows from information_asset_categories (T2); it
doesn't restrict rows from information_assets (T1). If you don't
want all rows from information_assets then why are you using an
outer join?

--
Michael Fuhr