Re: left join is strange

> Andrei Ivanov wrote:
>
> On Mon, 8 Dec 2003, Arjen van der Meijden wrote:
>
> > > 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.
> >
>
> I kinda figured that out, but still, being a left join, it
> should return
> all the rows in the table products, which I then filter with
> v.date = current_date OR v.date IS NULL.
>
> v.date has 3 possible values: current_date, some other date
> or NULL, if
> there is no corresponding row in
> products_daily_compacted_views for that
> product.
>
> I filter out only 1 value, and I still should get 1785 rows...

No, you combine two table using a left join (and yes, you get 1785 rows
from that left join), which then (after the joining) get filtered using
your where.
The values that have the current_date (which are probably none, since
that is taken at the moment of the selection, not at the moment of the
insert) or the NULL will get through, resulting in less than your 1785
rows.

Regards,

Arjen