Does Oracle have a database table subtract?

wasser
wasser used Ask the Experts™
on
Hi,
I used to use Paradox database, and it had a procedure that would subtract all the data records in one table from the records in another table.  Both tables had to have the same structure, so all fields in the same order with the same datatypes and lengths, but the column names could be different.  Using the database table subtract, you could easily and quickly determine if any records were different between the two tables.

Does Oracle have a procedure or function that would do this same thing, or how would one set up a query to accomplish this if no procedure exists?

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2009
Commented:
Yes, you can use MINUS

select * from tab1
MINUS
select * from tab2
Commented:
MINUS ?

eg
select col1, col2, col3 from tableA
minus
select col1, col2, col3 from tableB

The restrictions appear to be similar to the Paradox subtract, in that there needs to be the same number of columns, and they need to be of the same type.

Author

Commented:
Thanks mrjoltcola and ishando,
I'm off to give it a try to see if I come up with any other specific questions.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Devinder Singh VirdiLead Oracle DBA Team
Commented:
To substract records from one table to another use MINUS as
select * from Table_A
MINUS
select * from table_B;

OR

---- To find all records from Table_A have more records than Table_B
select * from table_A, Table_B
where Table_A.col1 = Table_B.col2(+)
and Table_B.col2 is null;
Top Expert 2009

Commented:
Minus compares whole result sets. If you want key by key matching, ignoring other attributes, you can instead use:

-- Exists in both
select * from tab1 where tab1.id in (select id from tab2);

-- Exists in tab1 and not tab2
select * from tab1 where tab1.id not in (select id from tab2);


You can also use "where not exists" for similar functionality.
Devinder Singh VirdiLead Oracle DBA Team

Commented:
Yes minus will chek every column of one table with another, but if you just want to compair primary/uniqye key then you can use
1. Outer join to find missing  records
2. in operator (if one table is very big as compair to 2nd, then oracle may choose Hash join and will consume lots of temp space)
3. Exists and Not exists.
Devinder Singh VirdiLead Oracle DBA Team

Commented:
Not just primary/unique, but other also it was typing mistake.

Author

Commented:
Thanks for your answers, you all added something slightly different.  The first two responses answered my immediate question so I awarded them with somewhat higher points.  The remaining responses all embellished on some of the other uses for a MINUS query which I appreciate and will most likely refer back to.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial