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
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
Related Q&A from Rudy Limeback
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading