Advanced Search

Return to Snippet

Revision: 8380
at September 17, 2008 17:26 by stews
Initial Code 
SELECT COUNT(src1) AS in_old_addresses,
       COUNT(src2) AS in_new_addresses,
       first_name,
       last_name,
       city
  FROM (SELECT first_name, last_name, city, 1 AS src1, TO_NUMBER(NULL) AS src2
          FROM old_addresses
        UNION ALL
        SELECT first_name, last_name, city, TO_NUMBER(NULL) AS src1, 2 AS src2 FROM new_addresses)
 GROUP BY first_name, last_name, city
HAVING COUNT(src1) <> COUNT(src2);
Initial URL 

                                

                                                            

                                    

                                        

                                        Initial Description
                                        

                                    

                                    
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.

                                

                                                            

                                    

                                        

                                        Initial Title
                                        

                                    

                                    
Efficiently compare columns from two tables

                                

                                                            

                                    

                                        

                                        Initial Tags
                                        

                                    

                                    
Oracle

                                

                                                            

                                    

                                        

                                        Initial Language
                                        

                                    

                                    
PL/SQL