Andrei Ivanov <andrei.ivanov@ines.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