I have 1 main table with my entire population of ids and 15 different tables that are subsets of this main table (but not collectively exhaustive, i.e. there are ids in the main table that are not in any of the 15 different tables). I am looking for a smart way to see if there are any of the 15 tables that have the same ids, and if so the names and number of ids that intersects for each pair of tables. I think this could be done by using 14 inner joins for each table. However, there might be a smarter way that I cannot think of. Any ideas for what I can do?
Maybe it could be a matrix that shows the intersection for each pair of tables. Maybe something like this:
+----------+--------+--------+--------+----------+---------+ | | Table1 | Table2 | Table3 | Table... | Table15 | +----------+--------+--------+--------+----------+---------+ | Table1 | 0 | 5 | 2 | ... | 0 | | Table2 | 5 | 0 | 12 | ... | 9 | | Table3 | 2 | 12 | 0 | ... | 6 | | Table... | ... | ... | ... | ... | ...| | Table15 | 0 | 9 | 6 | ... | 0 | +----------+--------+--------+--------+----------+---------+ 2 Answers
I would do something along the lines of (I'm not sure about MSSQL syntax):
with grand_table as ( select 't1' as "table_id", id from table1 UNION ALL select 't2', id from table2 UNION ALL ... select 't15', id from table15 ) SELECT t1.table_id, t2.table_id, count(*) from grand_table t1 full outer join grand_table t2 on t1.id=t2.id group by t1.table_id, t2.table_id I think this is basically the same as joining all the combinations of tables, but it looks neater. It will probably be super inefficient, but then again, you do need all the combinations to get your result.
Yossi is on the right track, but the query isn't quite going to work to construct the 0s. Switching to an inner join gets all the non-zero values. You can get the zero values with a slight variation:
with grand_table as ( select distinct 't1' as table_name, id from table1 union all select distinct 't2', id from table2 union all ... select 't15', id from table15 ), tables as ( select v.* from (values ('t1'), ('t2'), . . . ('t15')) v(table_name) ) select t1.table_name, t2.table_name, count(gt2.id) as num_in_common from tables t1 cross join tables t2 left join grand_table gt1 on gt1.table_name = t1.table_name left join grand_table gt2 on gt2.table_name = t2.table_name and gt2.id = t2.id group by t1.table_name, t2.table_name; Note the use of select distinct to reduce the data before the union all. That is important for the counts and performance.
However, a more efficient method might be to show the overlap among all the tables at once:
with grand_table as ( select distinct id, 1 as t1, 0 as t2, . . . 0 as t15 union all select distinct id, 0, 1, . . . 0 union all ... select id, 0, 0, . . . 1 ) select t1, t2, . . . t15, count(*), min(id), max(id) from (select id, sum(t1) as t1, sum(t2) as t2, . . . , sum(t15) as t15 from grand_table group by id ) x group by t1, t2, . . . t15; This is the approach that I typically take when looking for overlaps among tables.
