Re: BUG #2553: Outer join bug

[Michael Fuhr <mike(at)fuhr(dot)org>]

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