How to differentiate two tables

Question

I have two tables and I want to get all records from one table that are different from the records in second table.

Eg.: if we have four records in the first table like A,B,C,D and three records in the second table thats A,B,C then the answer of query should be D.

I have tried "EXCEPT" operator but it doesn't work fine. Kindly help me in writing correct query for the given problem.

Answer 1

How about:

select * from TABLE_A where (COL,COL2,..) not in (select COL1,COL2,.. from TABLE_B) 
union all 
select * from TABLE_B where (COL1,COL2,..) not in (select COL1,COL2,.. from TABLE_A); 

Answer 2

If the first table is called TABLE_A, and the second table is called TABLE_B, in order to enhance the differences you can use the MINUS operand, both sides. This means:

SELECT * FROM TABLE_B
MINUS
SELECT * FROM TABLE_A;

The previous statement shows the records present in TABLE_B, but not present in TABLE_A.

Of course, you have to use the MINUS in the other side:

SELECT * FROM TABLE_A
MINUS
SELECT * FROM TABLE_B;

The previous statement shows the records present in TABLE_A, but not present in TABLE_B.

If you don't want to use MINUS, you can create two Oracle VIEWS with the previous statements.

Answer 3

You can use the EXCEPT, it works as follows :

Table Test1 : A, B, C Table Test2 : A, B, C, D

select * from test1 except select * from test2 : Will return you the records which are there in test1 but not in test2. Result will be null.

select * from test2 except select * from test1 : Will return you the records which are present in test2 but not in test1. Result for this query will give you "D".