Syntax

SELECT ...
UNION [ALL | DISTINCT] SELECT ...
[UNION [ALL | DISTINCT] SELECT ...]
[ORDER BY [column [, column ...]]]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]

Description

UNION is used to combine the results 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. (For example, the first column selected by the first statement should have the same type as the first column selected by the other statements.)

Table names can be specified as db_name.tbl_name. This allows to write UNIONs which involve multiple databases. See Identifier Qualifiers for syntax details.

The ALL keyword causes duplicate rows to be preserved. The DISTINCT keyword causes duplicate rows to be removed by the results. This is the default behaviour.

MariaDB starting with 10.1.1

Until MariaDB 10.1.1, all UNION ALL statements required the server to create a temporary table. Since MariaDB 10.1.1, the server can in most cases execute UNION ALL without creating a temporary table, improving performance (see MDEV-334).

The ORDER BY and LIMIT clauses

Individual selects can contain their own ORDER BY and LIMIT clauses. In that case, the individual queries need to be wrapped between parenthesis. However, this does not affect the order of the UNION, so they only are useful to limit the record read by one SELECT.

The UNION can have global ORDER BY and LIMIT clauses, which affect the whole resultset. If the columns retreived by individual SELECT statements have an alias (AS), the ORDER BY must use that alias, not the real column names.

Examples

UNION between tables having different column names:

(SELECT e_name AS name, email FROM employees)
UNION
(SELECT c_name AS name, email FROM customers);

Specifying the UNION's global order and limiting total rows:

(SELECT name, email FROM employees)
UNION
(SELECT name, email FROM customers)
ORDER BY name LIMIT 10;

Adding a constant row:

(SELECT 'John Doe' AS name, 'john.doe@example.net' AS email)
UNION
(SELECT name, email FROM customers);

Comments

Comments loading...
Loading