SQL:如何在单个列上连接大量表

Question

我有一个查询，我需要在单个列上连接大量表，当任何表中的任何记录与该列匹配时，应该连接记录。一个例子:

A
----------
id | a_value
----------
1  | foo
2  | bar

B
----------
id | b_value
----------
2  | cad
3  | qud

C
----------
id | c_value
----------
1  | fiz
4  | buz

D
----------
id | d_value
----------
5  | sas
6  | tos

SELECT id, a_value, b_value, c_value, d_value FROM <join A, B, C, D by id>

应该返回这样的结果集:

results
------------------------------------------
id | a_value | b_value | c_value | d_value
------------------------------------------
1  | foo     | null    | fiz     | null
2  | bar     | cad     | null    | null
3  | null    | qud     | null    | null
4  | null    | null    | buz     | null
5  | null    | null    | null    | sas
6  | null    | null    | null    | tos

你可以这样写连接:

A FULL JOIN B ON A.id = B.id
FULL JOIN C ON A.id = C.id OR B.id = C.id
FULL JOIN D ON A.id = D.id OR B.id = D.id OR C.id = D.id

但这看起来很荒谬，并且会随着列数的增加而迅速失控(以这种方式连接 n 表需要 n*(n-1)/2条件)。有更好的方法。有人有什么想法吗？

Answer 1

可以通过三种方法来完成您想要的操作。您已经研究过 full outer join 选项，并发现它需要。顺便说一下，您可以将其简化为:

A FULL JOIN
B
ON A.id = B.id FULL JOIN
C
ON C.id = coalesce(A.id, B.id) FULL JOIN
D
ON D.id = coalesce(A.id, B.id, C.ID)

第二种方式有两个子部分。如果您有一张包含所有 ID 的表格，那就太好了。只需使用 left join:

AllIds ai left outer join
A
on ai.id = A.id left outer join
B
on ai.id = B.id . . .

如果你没有，你可以做一个:

(select id from a union
 select id from b union
 select id from c union
 select id from d
) AllIds left outer join
. . .

第三种方式是union all方式:

select id, max(a_value) as a_value, max(b_value) as b_value,
       max(c_value) as c_value, max(d_value) as d_value
from (select a.id, a_value, NULL as b_value, NULL as c_value, NULL as d_value
      from a
      union all
      select b.id, NULL, b_value, NULL, NULL
      from b
      union all
      select c.id, NULL, NULL, c_value, NULL
      from c
      union all
      select d.id, NULL, NULL, NULL, d_value
      from d
     ) t
group by id;

根据表、索引和数据库，它们具有不同的性能特征。在实践中，我经常在大表上使用第二个选项。