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_2
Examples
- 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, time
Was 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 and this documentation. To find support, use the following resources:
Customers with an annual or support contract can contact InfluxData Support.