sql - 如何在同一个查询中从不同的表中检索相似的行？

Question

假设我有两个或多个包含用户的表，我想在同一个查询中从这些表中检索所有用户。

表共享一些列，那些具有相同名称的列是我要检索的列。

类似于:

SELECT name, age FROM users1;
SELECT name, age FROM users2;
etc.

注意:这只是一个例子，并不是真正的问题。我无法将这些表合并为一个。

Answer 1

您可以使用 UNION :

UNION is used to combine the result from multiple SELECT statements into a single result set.

The column names from the first SELECT statement are used as the column names for the results returned. Selected columns listed in corresponding positions of each SELECT statement should have the same data type.

一个例子:

mysql> SELECT 1 as ColumnA,'a' as ColumnB
    -> UNION
    -> SELECT 2, 'b'
    -> UNION
    -> SELECT 3, 'c';
+---------+---------+
| ColumnA | ColumnB |
+---------+---------+
|       1 | a       |
|       2 | b       |
|       3 | c       |
+---------+---------+
3 rows in set (0.05 sec)

另请注意:

The default behavior for UNION is that duplicate rows are removed from the result. The optional DISTINCT keyword has no effect other than the default because it also specifies duplicate-row removal. With the optional ALL keyword, duplicate-row removal does not occur and the result includes all matching rows from all the SELECT statements.

一个例子:

mysql> SELECT 1 as x
    -> UNION
    -> SELECT 1;
+---+
| x |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql> SELECT 1 as x
    -> UNION ALL
    -> SELECT 1;
+---+
| x |
+---+
| 1 |
| 1 |
+---+
2 rows in set (0.00 sec)