Re: How to diff two tables?

Hi Feng,
There should be a far more simple method. It is called MINUS (and/or INTERSECT)

here are a few examples
select col1
from tbl1
minus
select col1
from tbl2
will give all rows that occur in tbl1 but not in tbl2 Reverse tbl1 and tbl2 and it will work the other way around. Rows common to both tables
select col1
from tbl1
intersect
select col1
from tbl2

This should be more efficient as this is 2 full table scans and a sort, it doesn't query any indexes. NB: whatever select list you use, the datatype of each individual expression has to be identical to the expression at the same ordinal position in the other select list.

Hth,

Sybrand Bakker, Oracle DBA

Feng Huang wrote:

> Hi, there,
>
> This question may have been asked before, if so please bear with me.
>
> I have two tables with identical structures. Let's call them tbl1 and tbl2.
> I'd like to find out what rows are common to both tables, what rows have
> the same key values but different for other columns, what rows only exist
> in tbl1, and only in tbl2?
>
> My objective is to do this efficiently. The tables contains about 10,000
> records each. One simple way is: for each record in tbl1, find a match in
> tbl2. And for each record in tbl2, find a match in tbl1. This is simple
> but dumb, any better ways?
>
> Your help would be appreciated.
>
> Thanks very much.
>
> -Feng
> fhuang_at_cs.umd.edu
Received on Wed Sep 23 1998 - 00:00:00 CEST