Selecting distinct values from two tables

mysql count distinct multiple-tables
Tucker picture Tucker · Mar 1, 2011 · Viewed 13.4k times · Source

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!

Answer

Richard Pianka picture Richard Pianka · Mar 1, 2011

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)

Related questions

MySQL distinct count if conditions unique

I am trying to build a query that tells me how many distinct women and men there are in a given dataset. The person is identified by a number 'tel'. It is possible for the same 'tel' to appear multiple …

Read More
Select top distinct results ordered by frequency

My table has two columns: id and name. The data looks like this: id | name ---------- 1 Jeff 2 Sam 3 Carl 4 Sam 5 Carl 6 Jeff 7 Dave 8 Jeff What I want to obtain is this: name | frequency ---------------- Jeff 3 Carl 2 Sam 2 Dave 1 Essentially, I …

Read More
select count(*) from table of mysql in php

I am able to get both the value and row of the mysql query result. But I am struggling to get the single output of a query. e.g.: $result = mysql_query("SELECT COUNT(*) FROM Students;"); I need the result …

Read More