`UNION` clause

The UNION clause appends the results of one query to the results of another. This removes duplicate rows from the result unless the ALL operator is added.

To be able to compute the union of the results of two queries, the queries must be compatible. This means that they must have the same number of columns and the corresponding columns must be of compatible types.

Syntax

SELECT ...
UNION [ALL]
SELECT ...
[UNION [ALL]
SELECT ...
...
];

Examples

Let’s output a list of all countries that appear in the country columns in two tables — capitals and population:

CREATE TABLE capitals (country VARCHAR, capital VARCHAR);
CREATE TABLE population (country VARCHAR, population_mil BIGINT);

INSERT INTO capitals VALUES
    ('Russia', 'Moscow'),
    ('Italy', 'Rome'),
    ('Spain', 'Madrid'),
    ('France', 'Paris');
INSERT INTO population VALUES
    ('Russia', 143),
    ('Spain', 48),
    ('Brazil', 211);

SELECT country FROM capitals
UNION
SELECT country FROM population;

+---------+
| country |
+---------+
| Russia  |
+---------+
| Italy   |
+---------+
| France  |
+---------+
| Spain   |
+---------+
| Brazil  |
+---------+

Now, for the same two tables, let’s output the list of countries that occur in the country columns, but without abbreviating the repeated occurrences. To do this, we use the ALL operator:

SELECT country FROM capitals
UNION ALL
SELECT country FROM population;

+---------+
| country |
+---------+
| Russia |
+---------+
| Italy |
+---------+
| Spain |
+---------+
| France |
+---------+
| Russia |
+---------+
| Spain |
+---------+
| Brazil |
+---------+