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.