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!


Introducing InfluxDB 3.0

The new core of InfluxDB built with Rust and Apache Arrow. Available today in InfluxDB Cloud Dedicated.

Learn more

State of the InfluxDB Cloud Serverless documentation

The new documentation for InfluxDB Cloud Serverless is a work in progress. We are adding new information and content almost daily. Thank you for your patience!

If there is specific information you’re looking for, please submit a documentation issue.

InfluxDB Cloud Serverless powered by IOx