Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table A ( columnA )
as
select 'a' from dual union all
select 'a' from dual union all
select 'a' from dual union all
select 'a' from dual union all
select 'a' from dual union all
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
;
12 rows affected

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
;
8 rows affected

SELECT UNIQUE columnA AS col_a, COUNT(columnA) as count_a, 'A' AS tab
FROM A
GROUP BY columnA
ORDER BY columnA;


COL_A
COUNT_A
TAB


a
6
A

b
2
A

c
3
A

e
1
A


SELECT UNIQUE columnA AS col_a, COUNT(columnA) as count_a, 'B' AS tab
FROM B
GROUP BY columnA
ORDER BY columnA;


COL_A
COUNT_A
TAB


b
1
B

c
2
B

d
4
B

e
1
B


SELECT UNIQUE columnA AS col_a, COUNT(columnA) as count_a, 'A' AS tab
FROM A
GROUP BY columnA
UNION ALL
SELECT UNIQUE columnA AS col_a, COUNT(columnA) as count_a, 'B' AS tab
FROM B
GROUP BY columnA
ORDER BY col_a;


COL_A
COUNT_A
TAB


a
6
A

b
1
B

b
2
A

c
2
B

c
3
A

d
4
B

e
1
A

e
1
B


WITH cte1 AS (
SELECT UNIQUE columnA AS col_a, COUNT(columnA) as count_a, 'A' AS tab
FROM A
GROUP BY columnA
-- ORDER BY columnA
UNION ALL
SELECT UNIQUE columnA AS col_a, COUNT(columnA) as count_a, 'B' AS tab
FROM B
GROUP BY columnA
ORDER BY col_a
),
cte2 AS
(
  SELECT col_a, COUNT(col_a) AS cnt -- this second field is not required - see below
                                    -- it was included just for explanatory purposes
  FROM cte1
  GROUP BY col_a
  HAVING COUNT(col_a) = 1   --<<< only records with columnA unique to either table 
  ORDER BY col_a
)
SELECT t1.tab, t1.col_a, t1.count_a, t2.cnt -- cnt not required - equal to 1 anyway.
FROM cte1 t1
JOIN cte2 t2 ON t1.col_a = t2.col_a
ORDER BY tab, col_a;


TAB
COL_A
COUNT_A
CNT


A
a
6
1

B
d
4
1


WITH cte1 AS (
SELECT UNIQUE columnA AS col_a, COUNT(columnA) as count_a, 'A' AS tab
FROM A
GROUP BY columnA
-- ORDER BY columnA
UNION ALL
SELECT UNIQUE columnA AS col_a, COUNT(columnA) as count_a, 'B' AS tab
FROM B
GROUP BY columnA
ORDER BY col_a
),
cte2 AS
(
  SELECT col_a -- COUNT(col_a) AS cnt
  FROM cte1
  GROUP BY col_a
  HAVING COUNT(col_a) = 2 -- only records with columnA common to both tables 
  ORDER BY col_a
)
SELECT t1.tab, t1.col_a, t1.count_a -- t2.cnt
FROM cte1 t1
JOIN cte2 t2 ON t1.col_a = t2.col_a
ORDER BY col_a, tab;


TAB
COL_A
COUNT_A


A
b
2

B
b
1

A
c
3

B
c
2

A
e
1

B
e
1


SELECT UNIQUE columnA AS col_a, COUNT(columnA) as count_a, 'A' AS tab
FROM A
GROUP BY columnA
-- ORDER BY columnA
UNION ALL
SELECT UNIQUE columnA AS col_a, COUNT(columnA) as count_a, 'B' AS tab
FROM B
GROUP BY columnA
ORDER BY col_a


COL_A
COUNT_A
TAB


a
6
A

b
1
B

b
2
A

c
2
B

c
3
A

d
4
B

e
1
A

e
1
B