left join is strange

From: Andrei Ivanov <andrei(dot)ivanov(at)ines(dot)ro>
To: pgsql-general(at)postgresql(dot)org
Subject: left join is strange
Date: 2003-12-08 10:58:57
Message-ID: Pine.LNX.4.58L0.0312081252130.3852@webdev.ines.ro
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Arjen van der Meijden 2003-12-08 11:23:28 Re: left join is strange
Previous Message Keith C. Perry 2003-12-08 10:20:47 Re: pgsql 7.4 on minimal environment