how minus works

Question

I have 2 tables

create table table1 (tb1 number);
insert into table1 values (13);
insert into table1 values (14);
insert into table1 values (15);
insert into table1 values (16);
create table table2(tb2 number);
insert into table2 values (13);
insert into table2 values (14);
insert into table2 values (25);
insert into table2 values (26);

I want to output inner join values in left column and distinct right values from table 2 in right column. So it should be

13 25
14 26

I try to use minus

select  table1.tb1, table2.tb2  from table1 right  join table2 on table1.tb1= table2.tb2 
minus 
select  null, table2.tb2 from table1 inner join table2 on table1.tb1=table2.tb2
;

But instead I got

13 13
14 14
   25
   26

What can I do? Individually two selects works fine and I just wanna to do first select result minus second select result.

Answer 1

Why

13 25
14 26

and not

13 26
14 25

?

If you are just asking for column one to be a list of rows, in numeric order, of IDs that occur in table1 and table2, and column 2 to be rows that exist in table2 but not table1 then you are looking at bringing back two unrelated lists that may have different lengths. In that case, the correlation between the values in any given row is their ranking within their individual data sets

So - it can be done like this:

with t1 as ( 
          select 13 a from dual
union all select 14 a from dual
union all select 15 a from dual
union all select 16 a from dual)
, t2 as (
          select 13 a from dual
union all select 14 a from dual
union all select 25 a from dual
union all select 26 a from dual)
select sub1.a  t1_a
      ,sub2.a  t2_a
from      
(-- subquery to get common values and their rankings
 select t1.a
      ,rank() over (order by t1.a) rnk
from t1 join t2 on t1.a = t2.a) sub1
full outer join 
(--subquery to get the values only in t2 and their rankings
 select t2.a
      ,rank() over (order by t2.a) rnk
from t1 right outer join t2 on t2.a = t1.a
where t1.a is null) sub2
-- join on the ranking. This is needed to avoid a cartesien product.
using (rnk)

t1_A                                   t2_a                                    
13                                     25                                     
14                                     26