Re: HELP WITH A QUERY

From: Jasen Betts <jasen(at)xnet(dot)co(dot)nz>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: HELP WITH A QUERY
Date: 2010-11-25 01:35:21
Message-ID: ickegp$lia$1@reversiblemaps.ath.cx
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

On 2010-11-24, JORGE MALDONADO <jorgemal1960(at)gmail(dot)com> wrote:
> --0015174c188eeae5060495d4ae01
> Content-Type: text/plain; charset=ISO-8859-1
>
> I have 2 tables and each one has a date field. Is it possible to write a
> simple query to get the records with the dates in table1 but not in table2?
> For example:
>
> TABLE 1
>
> -----------------------------------------------------------------------------
> Branch_Name Sales Date
> -----------------------------------------------------------------------------
> Los Angeles 1500 May-15-2010
> San Diego 250 May-27-2010
> Los Angeles 300 May-28-2010
> Boston 700 May-28-2010
>
>
> TABLE 2
>
> -----------------------------------------------------------------------------
> Date Sales
> -----------------------------------------------------------------------------
> May-27-2010 250
> May-20-2010 535
> May-21-2010 320
> May-22-2010 750

> The query result should be:
> May-15-2010
> May-28-2010

all examples untested

eg "NOT IN":

select date from table1 where date not in ( select distinct date from table2);

or this "LEFT JOIN WHERE NULL:

select table1.date from table1 left outer join table2 on
table2.date=table1.date where table2.date is null.

or this "EXCEPT":

select date from table1 except select date from table2

If you want a tested example make it easy for us, post the SQL to make those
tables.

> I guess that a query with a subselect and the EXCEPT keyword would work but
> I would like to know if there is a simpler alternative?

EXCEPT is probably the best way, but all three methods will probably
produce similar query plans and have similar performance.

except is not really a subquery as both queries are on the same level.
"not in" uses a subquery.

--
⚂⚃ 100% natural

In response to

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Mladen Gogala 2010-11-25 01:36:20 Re: HELP WITH A QUERY
Previous Message Jean-Yves F. Barbier 2010-11-24 23:35:28 Re: HELP WITH A QUERY