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 по дате отправления:

Предыдущее
От: "Keith C. Perry"
Дата:
Сообщение: Re: pgsql 7.4 on minimal environment
Следующее
От: "Arjen van der Meijden"
Дата:
Сообщение: Re: left join is strange