how to capture mismatched records

Database
nag
Fan

how to capture mismatched records

hi
i want to capture mismatched records from 2 tables?

for example table_a has 200 records,table_b has 400 records out of which 120 records are same from both the tables, so i want to identify and capture the 360 unmatched records+ 1(the 240 times duplicated record) into table_c

thanks in advance.
Tags (1)
4 REPLIES
Senior Apprentice

Re: how to capture mismatched records

Finding unmatched records:
- using NOT EXISTS on all columns, maybe needs DISTINCT, be aware of NULLs
- using EXCEPT, NULLs are treated equal

select * from tab_a
except
select * from tab_b
)
union
(
select * from tab_b
except
select * from tab_a
)

Finding matched records:
- using EXISTS on all columns, maybe needs DISTINCT, be aware of NULLs
- using INTERSECT, NULLs are treated equal

select * from tab_b
intersect
select * from tab_a

But when you want to insert both matched and unmatched rows into the same target table it's just removing the duplicates:
insert into tab_c
select * from tab_b
intersect
select * from tab_a

Dieter
Enthusiast

Re: how to capture mismatched records

Thanks Dieter

Re: how to capture mismatched records

I have two columns in a table. I want to extract the matched part of strings in these two columns.

Please help.
Enthusiast

Re: how to capture mismatched records

@Dieter

I think it should be  an union

 insert into tab_c

select * from tab_b

UNION

select * from tab_a