union.md

UNION

SELECT ...
UNION [<DEFAULT>DISTINCT | ALL]
SELECT ...

Every SELECT statement must fetch the same number of columns.

Example table

t1		t2		t3
i	c	j	c	d	k
1	red	-1	tan	1904-01-01	100
2	blue	1	red	2004-01-01	200
3	green			2004-01-01	200

Example query

SELECT i FROM t1
UNION SELECT j FROM t2
UNION SELECT k FROM t3

i
1
2
3
-1
100
200

Overview

• the names of columns equal those used in the first SELECT query
• data type of a column is based on the values received (e.g., if a column has both numbers and strings, the column will be of string type)
• columns are matched by the order (not names)

query	i	c
SELECT i, c FROM t1 UNION SELECT k, d FROM t3	1	red
	2	blue
	3	green
	100	1904-01-01
	200	2004-01-01

query	i	c
SELECT i, c FROM t1 UNION SELECT d, k FROM t3	1	red
	2	blue
	3	green
	1904-01-01	100
	2004-01-01	200

Including duplicated values

SELECT * FROM t1
UNION ALL
SELECT * FROM t2
UNION ALL
SELECT * FROM t3

If we use both UNION [DISTINCT] and UNION ALL, all queries fetching the unique values will be performed at first.

Additional clauses

• SELECT queries are contained within ()'s
• single queries may have their own clauses
• column names from the first SELECT query must be used
• if a sorted column is an alias, use the alias
• we can't refer to a table (tbl.col), we must create an alias for a column and use it instead

(SELECT i, c FROM t1)
UNION
(SELECT k, d FROM t3)
ORDER BY c