CODEWITHSUNDEEP
sqloracle-database
Madhu Velayudhan
Madhu Velayudhan

Reputation: 59

How to get all values from a table which are not present in other table

I have two table say a and b

SQL> desc a;

 Name                                      Type
 ----------------------------------------- ----------------------------
 ID                                        NUMBER(38)    
 NAME                                      VARCHAR2(10)


SQL> desc b;

 Name                                      Type
 ----------------------------------------- ----------------------------
 ID                                        NUMBER(38)    
 NAME                                      VARCHAR2(10)

SQL> select * from a;

    ID NAME
     1 a
     1 a
     1 a
     2 b
     2 b
     3 c
     3 c
     4 d

SQL> select * from b;

    ID NAME
     1 a
     2 b

my output should look like

    ID NAME
     1 a
     1 a
     2 b
     3 c
     3 c
     4 d

When I do

SQL> select * from a minus select * from b;

    ID NAME
     3 c
     4 d

but this is not what I want.. Please help me

Upvotes: 0

Views: 77

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269583

Why have a column called id that is not a unique id on the table? Some naming conventions don't make sense.

In any case, you have a problem because you have exactly duplicate rows, with no way to distinguish them. Fortunately, Oracle has a solution. You can use row_number() or rownum to create a unique key, and then use this for removing the duplicates.

select a.*
from (select a.*, row_number() over (partition by id, name order by id) as seqnum
      from a
     ) a left outer join
     (select b.*, row_number() over (partition by id, name order by id) as seqnum
      from b
     ) b
     on a.id = b.id and a.seqnum = b.seqnum
where b.id is NULL;

Upvotes: 1

Madhivanan
Madhivanan

Reputation: 13700

One approach is

select id,type from
(
   select rownum,id,type from table1 minus select rownum,id,type from table2 
) temp

Upvotes: 0

Related Questions