CODEWITHSUNDEEP
c#sqlasp.netoracle-database
Fahad
Fahad

Reputation: 1444

Table data comparison - Oracle

I have two tables with the same columns with identical data. The data in one of the tables may be updated, and I need to show a data comparison. The resultant dataset should contain only new, deleted, or modified rows. Consider the following data.

Table1 data

EmployeeId  EmployeeName

10001       Ned Stark
10002       Jon Snow
10003       Robb Stark

Table 2 data

EmployeeId  EmployeeName

10001       Eddard Stark
10002       Jon Snow
10004       Arya Stark

Data comparison result (in the following format):

EmployeeId1 EmployeeName1 EmployeeId2 EmployeeName2

10001       Ned Stark     10001       Eddard Stark
10003       Robb Stark    NULL        NULL
NULL        NULL          10004       Arya Stark

How can I achieve this result? I've considered using joins, but I'm worried about the performance. Also, the key may not be a simple numeric key, it can also be a composite key comprising 2 or more columns. What would be the best way to achieve this comparison in the desired format?

Upvotes: 2

Views: 122

Answers (2)

David Faber
David Faber

Reputation: 12485

You'll want to do a FULL OUTER JOIN:

SELECT t1.employeeid AS employeeid1, t1.employeename AS employeename1
     , t2.employeeid AS employeeid2, t2.employeename AS employeename2
  FROM table1 t1 FULL OUTER JOIN table2 t2
    ON t1.employeeid = t2.employeeid
 WHERE t1.employeename IS NULL
    OR t2.employeename IS NULL
    OR t1.employeename != t2.employeename;

Please see SQL Fiddle demo here.

Upvotes: 1

Andomar
Andomar

Reputation: 238126

Een full join returns a row even if the left or right hand side is missing:

select  *
from    Table1 e1
full join
        Table2 e2
on      e1.EmployeeID = e2.EmployeeID
where   nvl(e1.EmployeeName,'-') <> nvl(e2.EmployeeName,'-')

The where clause says that there must have been a change in the EmployeeName column. The nvl call is required because an outer join returns null when a match is not found, and comparing to null never succeeds.

Upvotes: 1

Related Questions