Ask the Expert

Return non-matching rows from both tables

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?

    Requires Free Membership to View

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.

This was first published in January 2008

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: