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
- Dozens more answers to tough SQL questions from Rudy Limeback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, SQL Server, DB2, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
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.