Thread: HELP WITH A QUERY

On Wed, 24 Nov 2010 17:16:47 -0600, JORGE MALDONADO
<jorgemal1960@gmail.com> wrote:



> 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
>
> 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?

Could you be more precise:
* what are common point(s) between tables?
* what do you really want?
* what column(s) is involved in the SELECT criterium?

--
But sex and drugs and rock & roll, why, they'd bring our blackest day.

On 2010-11-24, JORGE MALDONADO <jorgemal1960@gmail.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

On 11/24/2010 06:16 PM, JORGE MALDONADO wrote:
> 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?

How about "NOT EXISTS"?

--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
www.vmsinfo.com

On 11/24/2010 08:35 PM, Jasen Betts wrote:
> all examples untested
>
> eg "NOT IN":
A word of caution: according to my experience, "NOT IN" exhibits much
stronger inclination to produce sequential scan plans than "NOT EXISTS".

--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
www.vmsinfo.com