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.


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. SELECT first_name, last_name, TO_NUMBER(NULL) AS src1, 2 AS src2 FROM new_set)
  15. GROUP BY first_name, last_name
  16. HAVING COUNT(src1) <> COUNT(

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.