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!

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)

You can use

UNION ALL

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

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.