Search notes:

Oracle: Compare the data of two tables

With a combination of the set operators union and minus, it is possible to execute an SQL statement that compares the data of two tables and returns their difference.
create table tq84_A (num  number, txt varchar2(10));
create table tq84_B (num  number, txt varchar2(10));
 
insert into tq84_A values(1, 'one'  );
insert into tq84_A values(2, 'two'  );
insert into tq84_A values(3, 'three');
insert into tq84_A values(4, 'four' );
 
insert into tq84_B values(1, 'one'  );
insert into tq84_B values(3, 'THREE');
insert into tq84_B values(4, 'four' );
insert into tq84_B values(5, 'five' );
 
select 'only A' where_, only_a.* from (select num, txt from tq84_A minus select num, txt from tq84_B) only_a union all
select 'only B' where_, only_b.* from (select num, txt from tq84_B minus select num, txt from tq84_A) only_b;
 
drop table tq84_A;
drop table tq84_B;
Of course, writing such an SQL statement is tedious. We should let Oracle create the SQL statement for us. An example of such a statement is here: Create an SQL Statement that compares two tables.

See also

dbms_comparison.
Determine the difference between the structure of two tables

Index

Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 8 attempt to write a readonly database in /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php:78 Stack trace: #0 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(78): PDOStatement->execute(Array) #1 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(30): insert_webrequest_('/notes/developm...', 1758194038, '216.73.216.150', 'Mozilla/5.0 App...', NULL) #2 /home/httpd/vhosts/renenyffenegger.ch/httpsdocs/notes/development/databases/Oracle/objects/tables/compare-data/index(62): insert_webrequest() #3 {main} thrown in /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php on line 78