How can I retrieve similar rows from different tables in the same query?

Question

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.

Answer 1

You can use 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.

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.

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)

Answer 2

Try

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

Answer 3

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

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.

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.

Answer 4

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