Return non-matching rows from both tables
An Oracle user asks how to return only non-matching rows when joining two tables.
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
Table1 ID Name 1 John 2 Philip Table2 ID Name 1 John 3 Andy
The query should return only the following rows:
ID Name 2 Philip 3 Andy
Currently I am using Unions and NOT IN clause. Is there another way?
Try a FULL OUTER JOIN with IS NULL tests:
select coalesce(Table1.ID,Table2.ID) as ID
, coalesce(Table1.Name,Table2.Name) as Name
from Table1
full outer
join Table2
on Table2.ID = Table1.ID
where Table1.ID is null
or Table2.ID is null
Neat, eh?
The only difficulty you might have is that not every database management system supports FULL OUTER JOIN. Many do, because it is part of the SQL standard. If yours doesn't, the UNION is your fallback strategy. My preference is LEFT OUTER JOIN with an IS NULL test as opposed to a NOT EXISTS subquery.
Dig Deeper on Oracle development languages
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.
Meet all of our Oracle Database / Applications experts
View all Oracle Database / Applications questions and answers
Start the conversation
0 comments