CODEWITHSUNDEEP
sqloracle-database
Madhu Velayudhan
Madhu Velayudhan

Reputation: 59

How to get all values when I minus two tables in oracle

I have two tables say table1 and table2

table1 has duplicate rows but table2 may or may not have when I do 

select * from table1 minus select * from table2

I get unique values of table1 but i need duplicates value to come

Upvotes: 0

Views: 89

Answers (1)

Emmanuel
Emmanuel

Reputation: 14209

If you have a key (a field or several fields which allow you to know if one line of table1 matches another line in table2), you could use exist:

select *
from table1 t1
where not exists (select 1 from table2 t2 where t2.pk_field = t1.pk_field)

If you don't have a PK, you will have to specify all fields:

select *
from table1 t1
where not exists (select 1 from table2 t2 where t2.field1 = t1.field1
                                            and t2.field2 = t1.field2 ...)

EDIT: here is a basic example:

with table1 as
(
  select 1 a from dual
  union all
  select 1 a from dual
  union all
  select 2 a from dual
  union all
  select 2 a from dual
  union all
  select 3 a from dual
),
table2 as
(
  select 2 a from dual
)
select *
from table1 t1
where not exists (select 1 from table2 t2 where t2.a = t1.a)

The result is correct, 1 is shown twice:

Row #   A
1   1
2   1
3   3

Upvotes: 3

Related Questions