Combine multiple SELECT statements

You can combine multiple SELECT statements into one result set using the UNION operator. The syntax is:

SELECT column1, column2 UNION SELECT column1, column2

Here is an example. Let’s say that we have the following two tables:

mysql> SELECT * FROM buyers;
 +----+-----------+--------+---------+
 | id | username | city | country |
 +----+-----------+--------+---------+
 | 1 | john | London | UK |
 | 2 | mark | Berlin | Germany |
 | 3 | alejandra | Madrid | Spain |
 +----+-----------+--------+---------+
 3 rows in set (0.00 sec)
mysql> SELECT * FROM testtb;
 +-------+-------------+------+
 | name | surname | year |
 +-------+-------------+------+
 | Amy | Bryant | 1991 |
 | Mark | Smith | 1955 |
 | John | von Neumann | 1921 |
 | Aaron | Rogers | 1995 |
 | Brian | Cormier | 1988 |
 +-------+-------------+------+
 5 rows in set (0.00 sec)

We want to combine the username column from the buyers table with the name column from the testtb table. Here is how we would do that:

mysql> SELECT username FROM buyers UNION SELECT name FROM testtb;
 +-----------+
 | username |
 +-----------+
 | john |
 | mark |
 | alejandra |
 | Amy |
 | Aaron |
 | Brian |
 +-----------+
 6 rows in set (0.00 sec)
The number of columns in the SELECT statements must be equal.
Geek University 2021