Re: left join is strange

On Mon, 8 Dec 2003, Arjen van der Meijden wrote:

> > Andrei Ivanov wrote:
> >
> > I want to list all the products and the number of times each
> > product has
> > been viewed:
> >
> > SELECT p.id, p.name, COALESCE(v.count, 0) AS views
> > FROM products p LEFT JOIN products_daily_compacted_views v ON
> > p.id = v.product
> > WHERE v.date = current_date OR v.date IS NULL ORDER BY views DESC
> >
> > The problem with this query is that it doesn't return all the
> > products,
> > instead of 1785 rows, it returns 1077 rows
> And that is exactly as it should be.
> You will get the left joined combination of p and v, but the filter in
> the where is applied afterwards on all those combinations.
>

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.

I filter out only 1 value, and I still should get 1785 rows...

> >
> > This modified query seems to be correct, it returns all the
> > products...
> >
> > SELECT p.id, p.name, COALESCE(v.count, 0) AS views
> > FROM products p LEFT JOIN products_daily_compacted_views v
> > ON p.id = v.product AND v.date = current_date
> > ORDER BY views DESC
> >
> > Could anybody explain to me why does this happen ?
> Here you apply your filter to the elements of v, before joining them to
> the elements of p.
>
> Best regards,
>
> Arjen
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>