Advanced Search

Return to Snippet

Revision: 8381
at September 17, 2008 17:33 by stews
Initial Code 
WITH old_set AS (SELECT first_name, last_name
                   FROM addresses 
                  WHERE status = 1),

     new_set AS (SELECT first_name, last_name
                   FROM temp_load_table)

SELECT COUNT(src1) AS in_old_set,
       COUNT(src2) AS in_new_set,
       first_name,
       last_name
  FROM (SELECT first_name, last_name, 1 AS src1, TO_NUMBER(NULL) AS src2
          FROM old_set
        UNION ALL
        SELECT first_name, last_name, TO_NUMBER(NULL) AS src1, 2 AS src2 FROM new_set)
 GROUP BY first_name, last_name
HAVING COUNT(src1) <> COUNT(
Initial URL 

                                

                                                            

                                    

                                        

                                        Initial Description
                                        

                                    

                                    
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.

                                

                                                            

                                    

                                        

                                        Initial Title
                                        

                                    

                                    
Efficiently compare results from two queries

                                

                                                            

                                    

                                        

                                        Initial Tags
                                        

                                    

                                    

                                

                                                            

                                    

                                        

                                        Initial Language
                                        

                                    

                                    
PL/SQL