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

Differences between two tables

I have two tables called S and R with the same structure, i.e.

 id    not null number(3)
 name  not null varchar2(33)

I would like to compare any differences between the two tables.


A classic problem. You'll need three queries: one to find rows in S that aren't in R, one to find rows in R that aren't in S, and one to compare the values of rows that match. For convenience, run them all at the same time in a UNION.

select id    as Sid
     , name  as Sname
     , cast(null as number(3))    as Rid
     , cast(null as varchar2(33)) as Rname
     , 'S row without matching R' as errormsg
  from S
 where not exists
       ( select 1 from R
          where id = S.id )
union all
select cast(null as number(3))    as Sid
     , cast(null as varchar2(33)) as Sname
     , id    as Rid
     , name  as Rname
     , 'R row without matching S' as errormsg
  from R
 where not exists
       ( select 1 from S
          where id = R.id )
union all
select id    as Sid
     , name  as Sname
     , id    as Rid
     , name  as Rname
     , 'S name not equal R name' as errormsg
  from S                
inner
  join R
    on S.id = R.id
 where S.name <> R.name

For More Information


This was last published in May 2004

Dig Deeper on Oracle and SQL

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.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close