How to use the UNION statement-OpenSearch(Open Search)-阿里云帮助中心

UNION and UNION ALL are set operators that combine the result sets of two or more SELECT statements into a single result set.

Schema requirements

All SELECT statements in a UNION query must use the same schema. All of the following conditions must be satisfied:

The same fields appear in each SELECT statement.
Fields in the same position share the same data type.
Fields appear in the same order across all statements.

UNION vs UNION ALL

Operator	Duplicate values	Performance
`UNION ALL`	Included	Faster
`UNION`	Removed (equivalent to `UNION ALL` + `DISTINCT`)	Slower

When deduplication is not required, use UNION ALL.

Constraints

A single query cannot mix UNION and UNION ALL.

Syntax

query:
   select_statement
   UNION [ALL]
   select_statement
   [UNION [ALL]
   select_statement];

Examples

Merge two result sets

SELECT nid, brand, price, size FROM phone WHERE nid < 5
UNION ALL
SELECT nid, brand, price, size FROM phone WHERE nid > 5

Merge more than two result sets

SELECT nid, brand, price, size FROM phone WHERE nid >= 3 AND nid <= 5
UNION ALL
SELECT nid, brand, price, size FROM phone WHERE nid >= 6 AND nid <= 10
UNION ALL
SELECT nid, brand, price, size FROM phone WHERE nid >= 30 AND nid <= 40