Compare 2 columns in 2 different tables with non uniques values I want to compare and check whether the values in columnA of table A exist in column A of Table B. But the columnA consist of duplicate values in both the table.
Maybe this will help:  use analytics to count (the duplicate) values in each table, and use the results of these operations in a full outer join.  Example (see dbfiddle):

Tables

create table A ( columnA )
as
select 'a' from dual union all
select 'b' from dual union all
select 'b' from dual union all
select 'c' from dual union all
select 'c' from dual union all
select 'c' from dual union all 
select 'e' from dual -- one 'e' in each table
;

-- one 'a', 'b', 'c' (respectively) missing here
-- 4 x 'd' (does not exist in the other table)
create table B ( columnA)
as
select 'b' from dual union all
select 'c' from dual union all
select 'c' from dual union all
select 'd' from dual union all
select 'd' from dual union all
select 'd' from dual union all
select 'd' from dual union all
select 'e' from dual -- one 'e' in each table
;


Write to 2 queries, one for each table, that look something like ...

-- count the instances of each value
select unique 
  'table A -&gt;' as tbl, columnA
, count(*) over ( partition by columnA ) as instances_a
from A

-- result
TBL         COLUMNA  INSTANCES_A  
table A -&gt;  a        1            
table A -&gt;  c        3            
table A -&gt;  b        2 


If this works, then use the initial queries as "inline views", and write a full outer join, which will allow you to compare the value counts, eg

select 
  *
from (
  select unique 
    'table A -&gt;' as tbl, columnA
  , count(*) over ( partition by columnA ) as instances_a
  from A
) A_
full join
(
  select unique 
    'table B -&gt;' as tbl, columnA
  , count(*) over ( partition by columnA ) as instances_b
  from B
) B_
on A_.columnA = B_.columnA 
order by A_.columnA, B_.columnA
;

-- result
TBL         COLUMNA  INSTANCES_A  TBL         COLUMNA  INSTANCES_B  
table A -&gt;  a        1            NULL        NULL     NULL         
table A -&gt;  b        2            table B -&gt;  b        1            
table A -&gt;  c        3            table B -&gt;  c        2            
NULL        NULL     NULL         table B -&gt;  d        4 


You can then modify this query, in order to find out whether values are missing from one of the tables etc.  Eg

select 
  A_.tbl, A_.columnA, count_a
, B_.tbl, B_.columnA, count_b
, case
    when count_a = count_b then 'yes'
    else 'no'
  end as "column contents equal?"
from (
  select unique 
    'table A -&gt;' as tbl, columnA
  , count(*) over ( partition by columnA ) as count_a
  from A
) A_
full join
(
  select unique 
    'table B -&gt;' as tbl, columnA
  , count(*) over ( partition by columnA ) as count_b
  from B
) B_
on A_.columnA = B_.columnA 
order by A_.columnA, B_.columnA
;

TBL         COLUMNA  COUNT_A  TBL         COLUMNA  COUNT_B  column contents equal?  
table A -&gt;  a        1        NULL        NULL     NULL     no                       
table A -&gt;  b        2        table B -&gt;  b        1        no                       
table A -&gt;  c        3        table B -&gt;  c        2        no                       
table A -&gt;  e        1        table B -&gt;  e        1        yes                      
NULL        NULL     NULL     table B -&gt;  d        4        no 


(Tested w/ Oracle 12c and 18c)