CODEWITHSUNDEEP
mysqlcountdistinctmultiple-tables
Tucker
Tucker

Reputation: 7362

Selecting distinct values from two tables

I have two rather large databases (+1 million rows each). Both tables have the same structure.

How can I check if each value in a column is unique across both tables?

Is there a
SELECT COUNT(DISTINCTcol) FROM tbl
type of query that will consider BOTH tables?

Thanks!

Upvotes: 3

Views: 13665

Answers (3)

Ryan Miller
Ryan Miller

Reputation: 391

Here is my initial thought in pseudocode.

select tableOne.distinctcol
from
(select distinct col as distinctcol from tb1) as tableOne
(select distinct col as distinctcol from tb2) as tableTwo
where tableOne.distinctcol = tableTwo.distinctcol

Basic get a distinct list of values from each table, join them on that column.

Upvotes: 0

Adrian Serafin
Adrian Serafin

Reputation: 7715

You can use

UNION ALL

statement. It doesn't remove duplicate rows so you can see if there are any duplicates.

Upvotes: 1

Richard Pianka
Richard Pianka

Reputation: 3356

You can UNION two full sets in a subquery and then select DISTINCT col from that.

Something like:

SELECT DISTINCT col FROM (SELECT * FROM tbl1 UNION ALL SELECT * FROM tbl2)

Upvotes: 5

Related Questions