CODEWITHSUNDEEP
sqloracle-database
Praphul Viswan
Praphul Viswan

Reputation: 11

How to intersect two tables without losing the duplicate values oracle

How to intersect two tables without losing the duplicate values in Oracle?

TAB1:

A
A
B
C

TAB2:

A
A
B
D

Output:

A
A
B

Upvotes: 0

Views: 720

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

If I understand correctly:

select a.*, row_number() over (partition by col1 order by col1)
from a
intersect
select b.*, row_number() over (partition by col1 order by col1)
from b;

This adds a new sequential number to each row. Intersect will go up to the matching number.

This uses partition by col1 -- the col1 is arbitrary. You may need to include all columns in the partition by.

Upvotes: 3

The Impaler
The Impaler

Reputation: 48850

A subquery will filter the rows:

select *
  from tab1
 where col in (select col from tab2)

Upvotes: 2

Related Questions