集合操作

IoTDB 原生支持 SQL 标准集合操作，包括 UNION（并集）、INTERSECT（交集）和EXCEPT（差集）三种核心运算符。通过执行这些操作，可实现无缝合并、比较和筛选多源时序数据查询结果，显著提升时序数据分析的灵活性与效率。

注意：该功能从 V2.0.9-beta 版本开始提供。

1. UNION

1.1 概述

UNION 操作将两个查询结果集的所有行合并（不保证结果顺序），支持去重（默认）和保留重复两种模式。

1.2 语法定义

query UNION (ALL | DISTINCT) query

说明：

  1. 去重规则：

    1. 默认（UNION 或 UNION DISTINCT）：自动去除重复行。
    2. UNION ALL：保留所有行（包括重复项），性能更高。

  2. 输入要求：

    1. 两个查询结果的列数必须相同。
    2. 对应列数据类型需兼容，兼容性规则如下：
      • 数值类型互容：INT32、INT64、FLOAT、DOUBLE 之间完全兼容。
      • 字符串类型互容：TEXT 与 STRING 完全兼容。
      • 特殊规则：INT64 与 TIMESTAMP 兼容。

  3. 结果集规则：

    1. 列名及顺序继承第一个查询的定义。

1.3 使用示例

以示例数据为原始数据。

  1. 获取 table1 和 table2 中设备及温度的非空数据集合（去重）
select device_id,temperature from table1 where temperature is not null
union 
select device_id,temperature from table2 where temperature is not null;

--等价于;
select device_id,temperature from table1 where temperature is not null 
union distinct
select device_id,temperature from table2 where temperature is not null;

执行结果：

+---------+-----------+
|device_id|temperature|
+---------+-----------+
|      101|       90.0|
|      101|       85.0|
|      100|       90.0|
|      100|       85.0|
|      100|       88.0|
+---------+-----------+
Total line number = 5
It costs 0.074s
  1. 获取 table1 和 table2 中设备及温度的非空数据集合（保留重复）
select device_id,temperature from table1 where temperature is not null 
union all 
select device_id,temperature from table2 where temperature is not null;

执行结果：

+---------+-----------+
|device_id|temperature|
+---------+-----------+
|      101|       90.0|
|      101|       90.0|
|      101|       85.0|
|      101|       85.0|
|      101|       85.0|
|      101|       85.0|
|      100|       90.0|
|      100|       85.0|
|      100|       85.0|
|      100|       88.0|
|      100|       90.0|
|      100|       90.0|
|      101|       90.0|
|      101|       85.0|
|      101|       85.0|
|      100|       85.0|
|      100|       90.0|
+---------+-----------+
Total line number = 17
It costs 0.108s

​注意​：

  • 集合操作​不保证结果顺序​，实际输出顺序可能与示例不同。

2. INTERSECT

2.1 概述

INTERSECT 操作返回两个查询结果集中共同存在的行（不保证结果顺序），支持去重（默认）和保留重复两种模式。

2.2 语法定义

query1 INTERSECT [ALL | DISTINCT] query2

​说明​：

  1. ​去重规则​：

    1. 默认（INTERSECT 或 INTERSECT DISTINCT）：自动去除重复行。
    2. INTERSECT ALL：保留所有重复行（包括重复项），性能略低。

  2. ​优先级规则​：

    1. INTERSECT 优先级高于 UNION 和 EXCEPT（如 A UNION B INTERSECT C 等价于 A UNION (B INTERSECT C)）。
    2. 从左到右计算（A INTERSECT B INTERSECT C 等价于 (A INTERSECT B) INTERSECT C）。

  3. ​输入要求​：

    1. 两个查询结果的列数必须相同。
    2. 对应列数据类型需兼容（兼容性规则同 UNION）：
      • 数值类型互容：INT32、INT64、FLOAT、DOUBLE 之间完全兼容。
      • 字符串类型互容：TEXT 与 STRING 完全兼容。
      • 特殊规则：INT64 与 TIMESTAMP 兼容。
    3. NULL 值视为相等（NULL IS NOT DISTINCT FROM NULL）。
    4. 若 SELECT 未包含 time 列，则 time 列不参与比较，结果集无 time 列。

  4. ​结果集规则​：

    1. 列名及顺序继承第一个查询的定义。

2.3 使用示例

基于 示例数据：

  1. 获取 table1 和 table2 中设备及温度的共同数据（去重）

    select device_id, temperature from table1 
intersect
select device_id, temperature from table2;

--等价于;
select device_id, temperature from table1
intersect distinct
select device_id, temperature from table2;

    执行结果：

    +---------+-----------+
|device_id|temperature|
+---------+-----------+
|      101|       90.0|
|      101|       85.0|
|      100|       null|
|      100|       90.0|
|      100|       85.0|
+---------+-----------+
Total line number = 5
It costs 0.087s

  2. 获取 table1 和 table2 中设备及温度的共同数据（保留重复）

    select device_id, temperature from table1 
intersect all
select device_id, temperature from table2;

    执行结果：

    +---------+-----------+
|device_id|temperature|
+---------+-----------+
|      100|       85.0|
|      100|       90.0|
|      100|       null|
|      101|       85.0|
|      101|       85.0|
|      101|       90.0|
+---------+-----------+
Total line number = 6
It costs 0.139s

​注意​：

  • 集合操作​不保证结果顺序​，实际输出顺序可能与示例不同。
  • 与 UNION/EXCEPT 混合使用时，需通过括号明确优先级（如 A INTERSECT (B UNION C)）。

3. EXCEPT

3.1 概述

EXCEPT 操作返回第一个查询结果集存在但第二个查询结果集中不存在的行（不保证结果顺序），支持去重（默认）和保留重复两种模式。

3.2 语法定义

query1 EXCEPT [ALL | DISTINCT] query2

​说明​：

  1. ​去重规则​：

    1. 默认（EXCEPT 或 EXCEPT DISTINCT）：自动去除重复行。
    2. EXCEPT ALL：保留所有重复行（包括重复项），性能略低。

  2. ​优先级规则​：

    1. EXCEPT 与 UNION 优先级相同，低于 INTERSECT（如 A INTERSECT B EXCEPT C 等价于 (A INTERSECT B) EXCEPT C）。
    2. 从左到右计算（A EXCEPT B EXCEPT C 等价于 (A EXCEPT B) EXCEPT C）。

  3. ​输入要求​：

    1. 两个查询结果的列数必须相同。
    2. 对应列数据类型需兼容（兼容性规则同 UNION）：
      • 数值类型互容：INT32、INT64、FLOAT、DOUBLE 之间完全兼容。
      • 字符串类型互容：TEXT 与 STRING 完全兼容。
      • 特殊规则：INT64 与 TIMESTAMP 兼容。
    3. NULL 值视为相等（NULL IS NOT DISTINCT FROM NULL）。
    4. 若 SELECT 未包含 time 列，则 time 列不参与比较，结果集无 time 列。

  4. ​结果集规则​：

    1. 列名及顺序继承第一个查询的定义。

3.3 使用示例

基于 示例数据：

  1. 获取 table1 中存在但 table2 中不存在的设备及温度数据（去重）

    select device_id, temperature from table1 
except
select device_id, temperature from table2;

--等价于;
select device_id, temperature from table1 
except distinct
select device_id, temperature from table2;

    执行结果：

    +---------+-----------+
|device_id|temperature|
+---------+-----------+
|      101|       null|
|      100|       88.0|
+---------+-----------+
Total line number = 2
It costs 0.173s

  2. 获取 table1 中存在但 table2 中不存在的设备及温度数据（保留重复）

    select device_id, temperature from table1
except all
select device_id, temperature from table2;

    执行结果：

    +---------+-----------+
|device_id|temperature|
+---------+-----------+
|      100|       85.0|
|      100|       88.0|
|      100|       90.0|
|      100|       90.0|
|      100|       null|
|      101|       85.0|
|      101|       85.0|
|      101|       90.0|
|      101|       null|
|      101|       null|
|      101|       null|
|      101|       null|
+---------+-----------+
Total line number = 12
It costs 0.155s

​注意​：

  • 集合操作​不保证结果顺序​，实际输出顺序可能与示例不同。
  • 与 UNION/INTERSECT 混合使用时，需通过括号明确优先级（如 A EXCEPT (B INTERSECT C)）。