sql - 从两个不同的表中获取数据到一个表中？

Question

我有以下两个表:

uid   | ABC   | ... (bunch of other stuff) ...
1     | val 1 | ... (bunch of other stuff) ...
2     | val 2 | ... (bunch of other stuff) ...
3     | val 3 | ... (bunch of other stuff) ...

和...

uid   | DEF   | ... (bunch of other stuff) ...
4     | val 4 | ... (bunch of other stuff) ...
5     | val 5 | ... (bunch of other stuff) ...
6     | val 6 | ... (bunch of other stuff) ...

我想结束......

uid   | text  | ... (bunch of other stuff) ...
1     | val 1 | ... (bunch of other stuff) ...
2     | val 2 | ... (bunch of other stuff) ...
3     | val 3 | ... (bunch of other stuff) ...
4     | val 4 | ... (bunch of other stuff) ...
5     | val 5 | ... (bunch of other stuff) ...
6     | val 6 | ... (bunch of other stuff) ...

这看起来很简单，但我似乎无法弄清楚该怎么做。这不是加入吗？是吗？

Answer 1

UNION 子句将在此处为您提供帮助。它将两个或多个结果集合并为一个。

尝试:

SELECT uid, ABC, OtherStuff
FROM Table1

UNION

SELECT uid, DEF, OtherStuff
FROM Table2

UNION

SELECT uid, GHI, OtherStuff
FROM Table3

ORDER BY uid

UNION ALL 运算符在这个主题上有一个变体。 UNION 将显式删除重复行，而 UNION ALL 会保留它们。

这超出了简单的行差异:要删除重复项，UNION 运算符需要对最终结果集进行排序。这是 UNION ALL 运算符不会遇到的开销。此外，与 UNION ALL 相比，显式排序操作可能会导致 UNION 收集的结果集在排序顺序上有所不同。我建议您在收集结果集后使用明确的 ORDER BY 语句，以确保您的排序顺序符合您的预期。

另请注意，UNIONed 的结果集中的列数必须匹配。从 OP 中不清楚这两个表在它们存储的列数方面有何不同，因此我对在两个 SELECT * 语句之间执行 UNION 犹豫不决。