Re: left join is strange

> 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.

>
> 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