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(*) = 2Don'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