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
Join the conversationComment
Share
Comments
Results
Contribute to the conversation