Selecting distinct values from two tables

Question

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!

Your question is unclear. Your title asks for one query, your question asks for a different query, and your example is different from both. What specifically are you trying to do - find all distinct values, find all non-distinct values, or find all values together with their counts? If you ask a specific question you will get answers that are more suitable for your needs - in particular the performance of the solution may differ depending on which problem you are trying to solve. — Mark Byers, Mar 01 '11 at 18:24
possible duplicate of [MySQL: Select Distinct from 2 different tables?](http://stackoverflow.com/questions/1002452/mysql-select-distinct-from-2-different-tables) — nawfal, Jun 18 '13 at 09:19

score 5 · Accepted Answer · answered Mar 01 '11 at 18:19

5

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)

answered Mar 01 '11 at 18:19

Richard Pianka

3,317
2
28
36

5

The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL. – Adrian Serafin Mar 01 '11 at 18:21

score 1 · Answer 2 · answered Mar 01 '11 at 18:20

1

You can use

UNION ALL

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

answered Mar 01 '11 at 18:20

Adrian Serafin

7,665
5
46
67

score 0 · Answer 3 · answered Mar 01 '11 at 18:23

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.

Selecting distinct values from two tables

3 Answers3

Linked