Advanced Search

Efficiently compare results from two queries

/ Published in: PL/SQL
Save to your folder(s)
This compares selected columns between 2 queries, using the WITH clause. It lists the rows and values that are different between the two sources.

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, because it only looks through each query once.

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. WITH old_set AS (SELECT first_name, last_name
  2.                    FROM addresses 
  3.                   WHERE status = 1),
  4.  
  5.      new_set AS (SELECT first_name, last_name
  6.                    FROM temp_load_table)
  7.  
  8. SELECT COUNT(src1) AS in_old_set,
  9.        COUNT(src2) AS in_new_set,
  10.        first_name,
  11.        last_name
  12.   FROM (SELECT first_name, last_name, 1 AS src1, TO_NUMBER(NULL) AS src2
  13.           FROM old_set
  14.         UNION ALL
  15.         SELECT first_name, last_name, TO_NUMBER(NULL) AS src1, 2 AS src2 FROM new_set)
  16.  GROUP BY first_name, last_name
  17. HAVING COUNT(src1) <> COUNT(

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.