How to compare two records of 2 separate tables in the same schema and show only the difference in Oracle Database?

user_11481688 0
Viewed 86

I have a source table and a target table in the same schema. Based on the primary key value , I would want to compare the records of the source and destination tables and show only the columns which are having different values.

Could you please help me out on how to get a solution for the same ?

Note : DB version I am having : Oracle Database 19c Enterprise Edition

oracle
2 Answers

In Oracle, you can use CONCAT to concatenate multiple texts into one. Since you have multiple columns, you can view this problem-space as the concatenation of many expressions, one for each column. Your expression for a column could look like:

CASE WHEN ((r1.c1 IS NULL) AND (r2.c1 IS NULL)) OR (r1.c1 = r2.c1)
THEN ''
ELSE CONCAT('Table1: ', r1.c1, ', Table2: ', r2.c1, ';')
END

Now that we know how the expression looks like for a field, let's assume there are three fields:

SELECT CONCAT(
CASE WHEN ((r1.c1 IS NULL) AND (r2.c1 IS NULL)) OR (r1.c1 = r2.c1)
THEN ''
ELSE CONCAT('Table1: ', r1.c1, ', Table2: ', r2.c1, ';')
END,
CASE WHEN ((r1.c2 IS NULL) AND (r2.c2 IS NULL)) OR (r1.c2 = r2.c2)
THEN ''
ELSE CONCAT('Table1: ', r1.c2, ', Table2: ', r2.c2, ';')
END,
CASE WHEN ((r1.c3 IS NULL) AND (r2.c3 IS NULL)) OR (r1.c3 = r2.c3)
THEN ''
ELSE CONCAT('Table1: ', r1.c3, ', Table2: ', r2.c3, ';')
END) AS diff
FROM table1 r1
JOIN table2 r2
ON r1.id = r2.id;

This is the basic idea, but you will encounter some problems, like the types of the fields. If they are not textual, then you will need to convert them into some text. Also, if you need to reuse this diff tool, then you cannot assume that you know the number of fields to compare or even their name, so you will need to load the fields and generate the query based on their names and types.

user_4365600

I have a similar job which compares two large tables (26 mio rows each). I don't need to know which column is different, but if I rephrase the query to do that, it would be something along the lines of

CREATE TABLE t1 (
  id  NUMBER PRIMARY KEY,
  a   NUMBER NOT NULL,
  b   NUMBER NOT NULL,
  c   NUMBER     NULL
);

CREATE TABLE t2 (
  id  NUMBER PRIMARY KEY,
  x   NUMBER NOT NULL,
  y   NUMBER NOT NULL,
  z   NUMBER     NULL
);

INSERT INTO t1 VALUES (1, 10, 20, 30);
INSERT INTO t2 VALUES (1, 10, 20, 30);

INSERT INTO t1 VALUES (2, 10, 20, 30);
INSERT INTO t2 VALUES (2, 11, 20, 30);

INSERT INTO t1 VALUES (3, 10, 21, 30);
INSERT INTO t2 VALUES (3, 10, 20, 30);

INSERT INTO t1 VALUES (4, 10, 20, 31);
INSERT INTO t2 VALUES (4, 10, 20, 30);

INSERT INTO t1 VALUES (5, 10, 20, null);
INSERT INTO t2 VALUES (5, 10, 20, 30);


SELECT id,
       CASE WHEN a <> x THEN 1 ELSE 0 END a_x,
       CASE WHEN b <> y THEN 1 ELSE 0 END b_y,
       CASE WHEN c <> z 
              OR (c IS     NULL AND z IS NOT NULL) 
              OR (c IS NOT NULL AND z IS     NULL) THEN 1 ELSE 0 END c_z
  FROM t1 JOIN t2 USING (id)
 WHERE a <> x
    OR b <> y
    OR c <> z OR (c IS     NULL AND z IS NOT NULL) 
              OR (c IS NOT NULL AND z IS     NULL);

ID  A_X  B_Y  C_Z
 2    1    0    0
 3    0    1    0
 4    0    0    1
 5    0    0    1

The code is lengthy, but I got a script that looks at the data dictionary and writes the query.

However, I'm not totally happy with the comparison of the nullable columns. There is a undokumented system function that makes that easier, but I never got it properly working...

user_18640290
Related