PolarDB:UNION clause

Syntax

select_statement UNION [ ALL ] select_statement

Where select_statement is a SELECT statement that does not contain an ORDER BY or FOR UPDATE clause.

How it works

UNION and UNION ALL differ in how they handle duplicate rows:

Both variants require the two SELECT statements to return the same number of columns, and corresponding columns must have compatible data types.

Usage notes

Column requirements

The direct operands of UNION must produce the same number of columns. Corresponding columns must be of compatible data types.

ORDER BY scope

ORDER BY applies to the entire UNION result, not to an individual SELECT statement. To attach ORDER BY to a sub-expression, enclose that sub-expression in parentheses.

For example, the following two statements are equivalent:

SELECT a FROM t1 UNION SELECT b FROM t2 ORDER BY 1

(SELECT a FROM t1 UNION SELECT b FROM t2) ORDER BY 1

Neither is equivalent to:

SELECT a FROM t1 UNION (SELECT b FROM t2 ORDER BY 1)

Evaluation order

Multiple UNION operators in the same SELECT statement are evaluated left to right unless parentheses specify a different order.

FOR UPDATE restriction

The FOR UPDATE clause cannot be specified for a UNION result or for any SELECT statement that is an input to a UNION.

Example

The following example shows the difference between UNION and UNION ALL on the same data.

-- Table: t1        -- Table: t2
-- val              -- val
-- 1                -- 3
-- 2                -- 4
-- 3

-- UNION: removes duplicate rows
SELECT val FROM t1
UNION
SELECT val FROM t2
ORDER BY val;
-- Result: 1, 2, 3, 4

-- UNION ALL: keeps duplicate rows
SELECT val FROM t1
UNION ALL
SELECT val FROM t2
ORDER BY val;
-- Result: 1, 2, 3, 3, 4

The value 3 appears in both tables. UNION returns it once; UNION ALL returns it twice.