# 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.

You can simply get the desired result using LEFT OUTER JOIN as following:

SELECT DISTINCT
*
FROM
(
SELECT
A.COLUMN_A,
CASE
WHEN B.COLUMN_A IS NOT NULL THEN 'YES'
ELSE 'NO'
END AS "EXISTS IN TABLE_B"
FROM
TABLE_A A
LEFT JOIN TABLE_B B ON ( A.COLUMN_A = B.COLUMN_A )
);


Cheers!!

Answered by ORA_Dentist on August 18, 2020

From this answer which I reworked a little and came up with (depending on the OP's exact requirements which aren't entirely clear) the following answer (fiddle available here).

The data is the same (with the addition of a few records). First query run was this:

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


Result:

COL_A   COUNT_A TAB
a         6 A   -- extra records (5) in my fiddle, + 1 = 6
b         2 A
c         3 A
e         1 A


Do the same for table B.

Result:

COL_A   COUNT_A TAB
b         1   B
c         2   B
d         4   B
e         1   B


Perform a UNION ALL on these two queries. It's more efficient than UNION, i.e. no duplicate elimination required. We know that the records are all different anyway because of the tab "artificial" field but AFAIK, UNION has to perform a check anyway.

Result of UNION ALL - see fiddle for full query:

COL_A   COUNT_A TAB
a   6   A
b   1   B
b   2   A
... results snipped for brevity


Then, using COMMON TABLE EXPRESSIONs (aka CTEs - or the WITH clause), we combine the above result (cte1) with another query cte2 derived from cte1 as follows:

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) = 1   -- only records with columnA unique to either table
ORDER BY col_a
)
SELECT * FROM cte2;


Result:

COL_A   CNT
a     1  <<-- The CNT of 1 does not refer to the number of records with that
d     1  <<-- value in the table - it's merely a reflection of the
<<-- HAVING COUNT(col_a) = 1: only records with columnA unique to
<<-- either table and is shown for clarity


So, this gives us a and d which are indeed the values not shared by tableA and tableB but NOT which table they are in.

We then join this back to cte1 as follows:

... Rest of query snipped for brevity - see above (or fiddle)

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;


Which gives the final result:

TAB COL_A   COUNT_A CNT
A     a         6   1
B     d         4   1


which tells us that columnA contains the value a 6 times and none in tableB and that value d is in tableB 4 times and not in tableA. Again the CNT field is just to show how the result was derived. If you want all the records and their counts which are shared by both tables, put HAVING... = 2 - see end of fiddle.

The result for this would be:

TAB COL_A   COUNT_A
A   b   2
B   b   1
A   c   3
B   c   2
A   e   1
B   e   1


In future, I would strongly recommend that you use a fiddle to give sample data and then indicate the result that you with to obtain and the logic you used to obtain that result. Since your question isn't very clear, my answer may or may not be "overkill" - if this doesn't respond to your requirements, please let me know. My profile contains some articles on how to ask questions on this forum, you might want to take a look? p.s. welcome to the forum! :-)

Answered by Vérace on August 18, 2020

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 ->' as tbl, columnA
, count(*) over ( partition by columnA ) as instances_a
from A

-- result
TBL         COLUMNA  INSTANCES_A
table A ->  a        1
table A ->  c        3
table A ->  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 ->' as tbl, columnA
, count(*) over ( partition by columnA ) as instances_a
from A
) A_
full join
(
select unique
'table B ->' 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 ->  a        1            NULL        NULL     NULL
table A ->  b        2            table B ->  b        1
table A ->  c        3            table B ->  c        2
NULL        NULL     NULL         table B ->  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 ->' as tbl, columnA
, count(*) over ( partition by columnA ) as count_a
from A
) A_
full join
(
select unique
'table B ->' 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 ->  a        1        NULL        NULL     NULL     no
table A ->  b        2        table B ->  b        1        no
table A ->  c        3        table B ->  c        2        no
table A ->  e        1        table B ->  e        1        yes
NULL        NULL     NULL     table B ->  d        4        no


(Tested w/ Oracle 12c and 18c)

Answered by stefan on August 18, 2020

## Related Questions

### Can I just restore the .diff file over full backup to speed up restore?

1  Asked on February 4, 2021

### Can connect using SQL Server Management Studio but cannot ping or via program

2  Asked on February 3, 2021 by leona

### ERROR: no schema has been selected to create in

3  Asked on February 2, 2021 by emanuele-paolini

### ORACLE ASM present one or more LUNs?

1  Asked on January 31, 2021 by miguel-ramires

### I have 600% high CPU usage mysqld

1  Asked on January 30, 2021 by alfredo

### How to import multiple CSV files to a postgres table using pgadmin or other method?

2  Asked on January 29, 2021 by chris-jenner

### Counting Grouped records, using start date and end date with NULLs

1  Asked on January 28, 2021 by thedemonlord

### How to create a SELECT statement involving a subtype

1  Asked on January 26, 2021 by elephantcoder

### MySQL Installation: Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’

1  Asked on January 25, 2021 by rdrgtec

### Limiting join to top 1 row for each row

2  Asked on January 25, 2021 by dstr

### How to move SSISDB database

2  Asked on January 24, 2021 by jac

### Ident authentication failed for user “postgres”

1  Asked on January 23, 2021 by lloyd-thomas

### Creacte Mysql Database copy

1  Asked on January 20, 2021 by viktor-mandrika

### What are minimum configurations for mongodb replica set though a java program

1  Asked on January 18, 2021 by narendra

### Solving intra parallel query deadlocks

1  Asked on January 14, 2021 by din

### Fetching Remote Encryption Key for MariaDB

1  Asked on January 14, 2021 by vince-kronlein

### A database differential backup without a full backup

2  Asked on January 14, 2021 by nico-m

### Table mysql/innodb_index_stats has length mismatch in the column name table_name. Please run mysql_upgrade

3  Asked on January 13, 2021 by roy-hinkley