Unexpected results when joining on date fields

From: Tim Uckun <tim(at)basediary(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Unexpected results when joining on date fields
Date: 2011-07-10 23:58:33
Message-ID: CAGuHJrMYkWt15=Tf7e-dKhqkeEAnzpcPpdTwF9LycF2ycJyJJA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have two tables, traffic and sales. Each one has a date field and
lists the traffic and sales broken down by various parameters
(multiple rows for each date).

If I run select (select count(*) from traffic) as traffic, (select
count(*) from sales) as sales; I get the following 49383;167807

if I run select count(*) from traffic t inner join sales s on t.date
= s.date I get 24836841.

If I change the join to a left join, right join, full join I get the
same number of records.

So I created a data table which just has the dates in it and ran this query.

select count(d.date) from dates d
inner join traffic t on t.date = d.date
inner join sales s on s.date = d.date

And I get the same number 24836841

Same goes for right joins on the above query. Left joins of course
give a different answer as there are more dates in the date table than
there are in the other tables.

I am a bit perplexed by what is happening here.

Cheers

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Michael Nolan 2011-07-11 04:01:13 Re: [HACKERS] Creating temp tables inside read only transactions
Previous Message Darren Duncan 2011-07-09 20:07:25 Re: [HACKERS] Creating temp tables inside read only transactions