Re: left join is strange

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Andrei Ivanov <andrei(dot)ivanov(at)ines(dot)ro>
Cc: Arjen van der Meijden <acmmailing(at)vulcanus(dot)its(dot)tudelft(dot)nl>, pgsql-general(at)postgresql(dot)org
Subject: Re: left join is strange
Date: 2003-12-08 16:03:54
Message-ID: 16528.1070899434@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Andrei Ivanov <andrei(dot)ivanov(at)ines(dot)ro> writes:
> I kinda figured that out, but still, being a left join, it should return
> all the rows in the table products, which I then filter with
> v.date = current_date OR v.date IS NULL.
> v.date has 3 possible values: current_date, some other date or NULL, if
> there is no corresponding row in products_daily_compacted_views for that
> product.

Right. Your first query will show products for which (1) there is a v
row with date = current_date, or (2) there is *no* v row at all. If
there is a v row with the wrong date, it will get through the left join
and then be eliminated at WHERE. Because it gets through the left join,
no null-extended row is generated for that product, and so your OR
v.date IS NULL doesn't help.

In your second query, the date condition is considered part of the LEFT
JOIN condition, meaning that if no v rows pass the date condition, a
null-extended row will be emitted.

regards, tom lane

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Doug McNaught 2003-12-08 16:13:21 Re: What is WAL used for?
Previous Message Tom Lane 2003-12-08 15:55:32 Re: pgsql 7.4 on minimal environment