Documentation

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

(
  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
)

View example results

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
)

View example results

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

View example results


Was this page helpful?

Thank you for your feedback!


The future of Flux

Flux is going into maintenance mode. You can continue using it as you currently are without any changes to your code.

Read more

InfluxDB v3 enhancements and InfluxDB Clustered is now generally available

New capabilities, including faster query performance and management tooling advance the InfluxDB v3 product line. InfluxDB Clustered is now generally available.

InfluxDB v3 performance and features

The InfluxDB v3 product line has seen significant enhancements in query performance and has made new management tooling available. These enhancements include an operational dashboard to monitor the health of your InfluxDB cluster, single sign-on (SSO) support in InfluxDB Cloud Dedicated, and new management APIs for tokens and databases.

Learn about the new v3 enhancements


InfluxDB Clustered general availability

InfluxDB Clustered is now generally available and gives you the power of InfluxDB v3 in your self-managed stack.

Talk to us about InfluxDB Clustered

InfluxDB Cloud Serverless