Problem solve Get help with specific problems with your technologies, process and projects.

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?

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 
           select * from scott.emp@OtherDatabase                
         union all 
           select * from scott.emp@OtherDatabase
           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 last published in February 2002

Dig Deeper on Oracle and SQL



Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.