left join is strange
От | Andrei Ivanov |
---|---|
Тема | left join is strange |
Дата | |
Msg-id | Pine.LNX.4.58L0.0312081252130.3852@webdev.ines.ro обсуждение исходный текст |
Ответы |
Re: left join is strange
|
Список | pgsql-general |
Hello, I have 2 tables: CREATE TABLE products ( id SERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL ); CREATE TABLE products_daily_compacted_views ( product INTEGER NOT NULL REFERENCES products, date DATE NOT NULL DEFAULT ('NOW'::TEXT)::DATE, count INTEGER NOT NULL ); The table products has 1785 rows, the table products_daily_compacted_views has 768 rows with date = current_date; 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 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 ? Thank you.
В списке pgsql-general по дате отправления: