Posted By

stews on 09/17/08


Tagged

compare Subquery


Versions (?)

Efficiently compare results from two queries


 / Published in: PL/SQL
 

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.

  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  

You need to login to post a comment.