I want to see all the columns as a result of the minus operation-CodePudding

table1

Column A	Column B
A	New York
B	Istanbul
B	London

table2

Column A	Column B
A	New York
B	Istanbul
C	London

SELECT Column A From Table1
minus
SELECT Column A From Table2

RESULT -> C

I want to see result row so not only columnA

RESULT -> C LONDON

How can i handle it?

CodePudding user response：

The way you put it:

Sample data:

SQL> with
  2  table1 (cola, colb) as
  3    (select 'A', 'New York' from dual union all
  4     select 'B', 'Istanbul' from dual union all
  5     select 'B', 'London'   from dual
  6    ),
  7  table2 (cola, colb) as
  8    (select 'A', 'New York' from dual union all
  9     select 'B', 'Istanbul' from dual union all
 10     select 'C', 'London'   from dual
 11    )

Query begins here:

 12  select b.*
 13  from table2 b
 14  where not exists (select null
 15                    from table1 a
 16                    where a.cola = b.cola
 17                   );

C COLB
- --------
C London

SQL>

CodePudding user response：

Move that select to the where clause of your query, as follows:

SELECT Column B FROM table 
  WHERE Column A IN (
SELECT Column A From Table
minus
SELECT Column A From Table
)

CodePudding user response：

This is the most optimal query for it : have proper indexes on columnA either btree(high selectivity) or bitmap(low selectivity)

select b.* from 
table2 b left join table1 a 
on b.ColumnA=a.ColumnA
where a.ColumnA is null;

in will be very slow if the tables cardinality is very large so Koen Lostrie answer is not optimal.

Littlefoot's answer is based on subquery which is also slower !!