• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 967
  • Last Modified:

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
0
SharonInGeorgia
Asked:
SharonInGeorgia
  • 2
2 Solutions
 
MikeOM_DBACommented:

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

Open in new window

0
 
awking00Commented:
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

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now