Re: left join is strange
От | Arjen van der Meijden |
---|---|
Тема | Re: left join is strange |
Дата | |
Msg-id | 002701c3bd7d$b73e2210$3ac15e91@acm обсуждение исходный текст |
Ответ на | left join is strange (Andrei Ivanov <andrei.ivanov@ines.ro>) |
Ответы |
Re: left join is strange
|
Список | 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 по дате отправления: