Union¶
UNION can be used to combine results from multiple SELECT statements.
See also
Table of contents
Union All¶
If duplicates are allowed, use UNION ALL.
cr> select name from photos
... union all
... select name from countries
... union all
... select name from photos
... order by name;
+--------------+
| name |
+--------------+
| Austria |
| Berlin Wall |
| Berlin Wall |
| Eiffel Tower |
| Eiffel Tower |
| France |
| Germany |
| South Africa |
| Turkey |
+--------------+
SELECT 9 rows in set (... sec)
Union Distinct¶
To remove duplicates, use UNION DISTINCT or simply UNION.
cr> select name from photos
... union distinct
... select name from countries
... union
... select name from photos
... order by name;
+--------------+
| name |
+--------------+
| Austria |
| Berlin Wall |
| Eiffel Tower |
| France |
| Germany |
| South Africa |
| Turkey |
+--------------+
SELECT 7 rows in set (... sec)
Union of object types¶
When object types are unioned, the resulting object will contain the merged sub-columns from both of the input objects.
cr> SET error_on_unknown_object_key = FALSE;
SET OK, 0 rows affected (... sec)
cr> SELECT o, o['a'], o['b'] FROM (SELECT {a=1} AS o UNION SELECT {b=1} AS o) AS t;
+----------+--------+--------+
| o | o['a'] | o['b'] |
+----------+--------+--------+
| {"a": 1} | 1 | NULL |
| {"b": 1} | NULL | 1 |
+----------+--------+--------+
SELECT 2 rows in set (... sec)
Union of different types¶
If types do not match, CrateDB will choose one of the type with higher precedence and try to implicitly cast the values with lower precedence.
cr> SELECT pg_typeof(c) FROM (SELECT 1 AS c UNION SELECT '1' AS c UNION SELECT 1 AS c) AS t;
+-------------------------+
| pg_catalog.pg_typeof(c) |
+-------------------------+
| integer |
+-------------------------+
SELECT 1 row in set (... sec)