如何从同一查询中的不同表中检索类似的行？

Let's say I have two or more tables filled with users and I want to retrieve all users from those tables in the same query.

假设我有两个或更多表填充了用户,我想从同一查询中的那些表中检索所有用户。

Tables share some columns and those columns that have the same name are the ones I'm trying to retrieve.

表共享一些列,那些具有相同名称的列是我正在尝试检索的列。

Something like:

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

Note: this is just an example and not the real problem. I cannot combine those tables into one.

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

4 个解决方案

#1

You can use UNION:

你可以使用UNION:

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

UNION用于将多个SELECT语句的结果组合到单个结果集中。

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.

第一个SELECT语句中的列名称用作返回结果的列名称。在每个SELECT语句的相应位置中列出的选定列应具有相同的数据类型。

An example:

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)

Also note:

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.

UNION的默认行为是从结果中删除重复的行。可选的DISTINCT关键字除了默认值之外没有任何影响,因为它还指定了重复行删除。使用可选的ALL关键字,不会发生重复行删除,结果包括所有SELECT语句中的所有匹配行。

An example:

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)

#2

You can use union or union all, depending on what behaviour you want:

您可以使用union或union all,具体取决于您想要的行为:

select name, age from users1
union
select name, age from users2
union
select name, age from users3

or:

select name, age from users1
union all
select name, age from users2
union all
select name, age from users3

The difference is that union removes duplicates, while union all doesn't.

不同之处在于union会删除重复项,而union all则不会。

If you know that there are no duplicates, you should use union all so that the database doesn't have to make the extra work of trying to find duplicates.

如果您知道没有重复项,则应使用union all,以便数据库不必进行额外的尝试查找重复项的工作。

#3

Try

SELECT name, age FROM users1
UNION
SELECT name, age FROM users2

#4

note also that 'union all' will be much faster than 'union.

还要注意'联合所有'将比'联盟'快得多。