ORACLE SLQ - Comparing records in 2 tables

Table 2 retrieves data from existing fields in Table 1.  The system also creates additional fields in Table 2.  Not all fields are transfered from Table 1 to Table 2.  These tables may have millions of records so efficentcy is important.

I need to compare that the records are accurately populated in Table2 from Table1.  

              TABLE 1                                      TABLE 2
           Field1                                               Field1
           Field2                                               Field2
           Field3                                               Field3
           Field4                                            
           Field5                    
                                                                  Field6
                                                                  Field7
SharonInGeorgiaAsked:
Who is Participating?
 
MikeOM_DBAConnect With a Mentor Commented:

SELECT * FROM TABLE1 A
 WHERE NOT EXISTS (
    SELECT '?' FROM TABLE2 B
     WHERE A.FIELDX = B.FIELDX );

Open in new window

0
 
awking00Connect With a Mentor Commented:
The attached query compares every record in table1 OR table2 with every record in table1 AND table2. A perfect match will return no rows.

compare.txt
0
 
SharonInGeorgiaAuthor Commented:
I understand that using UNION can be slow when dealing with massive amount of records.  What are your thoughts?
0
 
awking00Commented:
In this case, I would probably use UNION ALL instead of UNION, which should avoid an extra sort, although the results are the same.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.