Re: Table differences

From: Somaskandan <somas2007_at_gmail.com>
Date: Thu, 11 Jun 2009 18:32:07 -0700 (PDT)
Message-ID: <50d34932-2907-491a-9645-0edcf207d5ae_at_r37g2000yqd.googlegroups.com>

On Jun 10, 4:38 pm, a..._at_unsu.com wrote:
> I'm sure there is a way to do this.  Have not found it yet.
>
> But, I have 2 tables, different structures.  Both have a date column.
> I want to get the rows from table A which do not exist in table B,
> based on the date:
>
> TABLE A:   COL1, DATE_COL
> TABLE B:   COL1, COL2, COL3, DATE_COL
>
> I want to get the rows in TABLE A which are not in TABLE B, based
> solely on the DATE_COL.  I do not think I can use MINUS due to the
> column difference.  And, this is going to a user, so cannot use PL/SQL
> either.....

Hi,

You can use any one of the following methods.... Based on the data volume choose the appropriate method...

1. Using outer-join

SELECT a.col1,a.date_col
FROM
     table_a a, table_b b
WHERE
     a.date_col=b.date_col(+)
 AND b.date_col IS NULL

b) Using Minus

SELECT a.*

    FROM table_a a,
(SELECT a.date_col FROM table_a a
 MINUS
SELECT b.date_col FROM table_b b
) x
WHERE a.date_col=x.date_col

c) Using NOT EXISTS

SELECT a.* FROM table_a a WHERE NOT EXISTS (SELECT 1 FROM table_b b WHERE a.date_col=b.date_col)

d) Using NOT IN

SELECT a.* FROM table_a a WHERE a.date_col NOT IN (SELECT b.date_col FROM table_b b)

---Soma Received on Thu Jun 11 2009 - 20:32:07 CDT