Ask the Expert

Selecting and comparing data from two different databases

How do I select data from two tables (scott.emp) from two different databases and compare them?

    Requires Free Membership to View

For the answer to the first part of your question, see my response to a previous question regarding selecting from tables residing in remote databases.

I see you are using the sample Oracle table scott.emp; so I will respond to the second part of your question with that in mind. As far as comparing two tables, this SQL gives you a list of employees found in the local database but not in the remote database:

select * 
  from scott.emp t1
  where not exists ( select *
                       from scott.emp@OtherDatabase t2
                       where t2.EmpNo = t1.EmpNo )
This SQL reverses the tables and gives you a list of employees found in the remote database but not locally:
select * 
  from scott.emp t1
  where not exists ( select *
                       from scott.emp@OtherDatabase t2
                       where t2.EmpNo = t1.EmpNo )
And this SQL gives you a list of employee numbers for employees found in both the remote and local databases, but are different in some way:
select EmpNo
  from ( 
         ( 
           select * from scott.emp 
           minus
           select * from scott.emp@OtherDatabase                
         )
         union all 
         (
           select * from scott.emp@OtherDatabase
           minus
           select * from scott.emp 
         )
       )  
  group by EmpNo
  having count(*) = 2  
Don't be thrown by the use of set operators, MINUS and UNION ALL. Use of these eliminates the need to accommodate for nulls. This stems from the property of nulls that state that a comparison involving a NULL will not evaluate to TRUE, even when comparing two NULL values. For example, (NULL <> 'Joe'), (NULL < 5), and (NULL = NULL) do not evaluate to TRUE. Interestingly enough, they do not evaluate to FALSE either. They evaluate to UNKNOWN, or said another way, they evaluate to NULL. This is an example of the three-value Boolean logic SQL employs.

I think the previous SQL is a bit more elegant than the alternatives. One somewhat bulky alternative looks something like this:

select * 
  from scott.emp t1, scott.emp@OtherDatabase t2
  where t1.EmpNo = t2.EmpNo
    and ( ( t1.EName is null and t2.EName is not null ) or 
          ( t1.EName is not null and t2.EName is null ) or 
          t1.EName <> t2.EName or 
          ( t1.Job is null and t2.Job is not null ) or 
          ( t1.Job is not null and t2.Job is null ) or 
          t1.Job <> t2.Job or 
          ( t1.Mgr is null and t2.Mgr is not null ) or 
          ( t1.Mgr is not null and t2.Mgr is null ) or 
          t1.Mgr <> t2.Mgr or 
          ( t1.HireDate is null 
              and t2.HireDate is not null ) or 
          ( t1.HireDate is not null 
              and t2.HireDate is null ) or 
          t1.HireDate <> t2.HireDate or 
          ( t1.Sal is null and t2.Sal is not null ) or 
          ( t1.Sal is not null and t2.Sal is null ) or 
          t1.Sal <> t2.Sal or 
          ( t1.Comm is null and t2.Comm is not null ) or 
          ( t1.Comm is not null and t2.Comm is null ) or 
          t1.Comm <> t2.Comm or 
          ( t1.DeptNo is null 
              and t2.DeptNo is not null ) or 
          ( t1.DeptNo is not null 
              and t2.DeptNo is null ) or 
          t1.DeptNo <> t2.DeptNo
        )


This was first published in February 2002

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: