Ask The Oracle Expert: Questions & Answers

Return non-matching rows from both tables

Return non-matching rows from both tables

By  Rudy Limeback, SQL Consultant, r937.com

SearchOracle.com

Is there a way of joining two tables to return only non-matching rows? For example, Table1 and Table2 have an identical structure and contain only two columns:

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:

To continue reading for free, register below or login

Requires Membership to View

To gain access to this and all member only content, please provide the following information:

By joining SearchOracle.com you agree to receive email updates from the TechTarget network of sites, including updates on new content, magazine or event notifications, new site launches and market research surveys. Please verify all information and selections above. You may unsubscribe at any time from one or more of the services you have selected by editing your profile or unsubscribing via email.

TechTarget cares about your privacy. Read our Privacy Policy

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.