Advanced Search

Efficiently compare columns from two tables

/ Published in: PL/SQL
Save to your folder(s)
This compares selected columns between 2 tables that you think might have differences. It lists the rows and values that are different between the two sources

You can add WHERE clauses as appropriate. You could also use this to compare the results of two queries.

This is from Tom Kyte (www.asktom.com). I can't say I understand exactly how it works, but it's much faster than other methods I've seen. It has the advantage of only looking through each table once.

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. SELECT COUNT(src1) AS in_old_addresses,
  2.        COUNT(src2) AS in_new_addresses,
  3.        first_name,
  4.        last_name,
  5.        city
  6.   FROM (SELECT first_name, last_name, city, 1 AS src1, TO_NUMBER(NULL) AS src2
  7.           FROM old_addresses
  8.         UNION ALL
  9.         SELECT first_name, last_name, city, TO_NUMBER(NULL) AS src1, 2 AS src2 FROM new_addresses)
  10.  GROUP BY first_name, last_name, city
  11. HAVING COUNT(src1) <> COUNT(src2);

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.