UNION clause
The UNION clause combines the results of two or more SELECT statements into a single result set. By default, UNION only keeps unique rows. To keep all rows, including duplicates, use UNION ALL.
When using the UNION clause:
- The number of columns in each result set must be the same.
- Columns must be in the same order and of the same or compatible data types.
Syntax
SELECT expression[,...n] FROM measurement_1 UNION [ALL] SELECT expression[,...n] FROM measurement_2Examples
- Union results from different measurements
- Return the highest and lowest three results in a single result set
- Union query results with custom data
Union results from different measurements
( SELECT 'h2o_pH' AS measurement, time, "pH" AS "water_pH" FROM "h2o_pH" LIMIT 4 ) UNION ( SELECT 'h2o_quality' AS measurement, time, index FROM h2o_quality LIMIT 4 )Return the highest and lowest three results in a single result set
The following example uses the sample data set provided in Get started with InfluxDB tutorial.
( SELECT 'low' as type, time, co FROM home ORDER BY co ASC LIMIT 3 ) UNION ( SELECT 'high' as type, time, co FROM home ORDER BY co DESC LIMIT 3 )Union query results with custom data
The following example uses the sample data set provided in Get started with InfluxDB tutorial. It also uses the table value constructor to build a table with custom data.
SELECT * FROM home WHERE time >= '2022-01-01T08:00:00Z' AND time <= '2022-01-01T12:00:00Z' UNION SELECT * FROM (VALUES (0, 34.2, 'Bedroom', 21.1, '2022-01-01T08:00:00Z'::TIMESTAMP), (0, 34.5, 'Bedroom', 21.2, '2022-01-01T09:00:00Z'::TIMESTAMP), (0, 34.6, 'Bedroom', 21.5, '2022-01-01T10:00:00Z'::TIMESTAMP), (0, 34.5, 'Bedroom', 21.8, '2022-01-01T11:00:00Z'::TIMESTAMP), (0, 33.9, 'Bedroom', 22.0, '2022-01-01T12:00:00Z'::TIMESTAMP) ) newRoom(co, hum, room, temp, time) ORDER BY room, timeWas this page helpful?
Thank you for your feedback!
Support and feedback
Thank you for being part of our community! We welcome and encourage your feedback and bug reports for InfluxDB 3 Enterprise and this documentation. To find support, use the following resources:
Customers with an annual or support contract can contact InfluxData Support. Customers using a trial license can email trial@influxdata.com for assistance.