Re: left join is strange - Mailing list pgsql-general

From Arjen van der Meijden
Subject Re: left join is strange
Date
Msg-id 002701c3bd7d$b73e2210$3ac15e91@acm
Whole thread Raw
In response to left join is strange  (Andrei Ivanov <andrei.ivanov@ines.ro>)
Responses Re: left join is strange
List pgsql-general
> 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




pgsql-general by date:

Previous
From: Andrei Ivanov
Date:
Subject: left join is strange
Next
From: "Victor B. Berdin"
Date:
Subject: Re: pgsql 7.4 on minimal environment