I have two tables called S and R with the same structure, i.e.
By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
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.