Does Oracle have a database table subtract?
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
-- 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.
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.
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.
Not just primary/unique, but other also it was typing mistake.
ASKER
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.
ASKER
I'm off to give it a try to see if I come up with any other specific questions.