UNION clause
InfluxDB 3 Core is in Public Alpha
InfluxDB 3 Core is in public alpha and available for testing and feedback, but is not meant for production use. Both the product and this documentation are works in progress. We welcome and encourage your input about your experience with the alpha and invite you to join our public channels for updates and to share feedback.
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 3 Core and this documentation. To find support, use the following resources:
Customers with an annual or support contract can contact InfluxData Support.