FROM & JOIN 子句

1. 语法概览

FROM relation (',' relation)*

relation
    : relation joinType JOIN relation joinCriteria 
    | aliasedRelation
    ;

joinType
    : INNER?
    | OUTER?
    | CROSS?
    | ASOF?
    ;

joinCriteria
    : ON booleanExpression
    | USING '(' identifier (',' identifier)* ')'
    ;

aliasedRelation
    : relationPrimary (AS? identifier columnAliases?)?
    ;

columnAliases
    : '(' identifier (',' identifier)* ')'
    ;

relationPrimary
    : qualifiedName                                              #tableName
    | '(' query ')'                                              #subqueryRelation
    | '(' relation ')'                                           #parenthesizedRelation
    ;

qualifiedName
    : identifier ('.' identifier)*
    ;

2. FROM 子句

FROM 子句指定了查询操作的数据源。在逻辑上，查询的执行从 FROM 子句开始。FROM 子句可以包含单个表、使用 JOIN 子句连接的多个表的组合，或者子查询中的另一个 SELECT 查询。

3. JOIN 子句

JOIN 用于将两个表基于某些条件连接起来，通常，连接条件是一个谓词，但也可以指定其他隐含的规则。

在当前版本的 IoTDB 中，支持以下连接方式：

• 内连接（Inner Join），连接条件可以是任意等值表达式。

• 外连接（Outer Join），连接条件可以是任意等值表达式。

• 交叉连接（Cross Join）
• ASOF JOIN（AS OF a specific point in time，特定时间点），是一种基于时间或近似匹配条件的特殊连接操作，适用于两个数据集的时间戳不完全对齐的场景。它能够为左表的每一行找到右表中时间最接近且满足条件的对应行，常用于处理时间序列数据（如传感器数据、金融行情等）。

3.1 内连接（Inner Join）

INNER JOIN 表示内连接，其中 INNER 关键字可以省略。它返回两个表中满足连接条件的记录，舍弃不满足的记录，等同于两个表的交集。

3.1.1 显式指定连接条件（推荐）

显式连接需要使用 JOIN + ON 或 JOIN + USING 语法，在 ON 或 USING 关键字后指定连接条件。

SQL语法如下所示：

// 显式连接, 在ON关键字后指定连接条件或在Using关键字后指定连接列
SELECT selectExpr [, selectExpr] ... FROM <TABLE_NAME> [INNER] JOIN <TABLE_NAME> joinCriteria [WHERE whereCondition]

joinCriteria
    : ON booleanExpression
    | USING '(' identifier (',' identifier)* ')'
    ;

注意：USING 和 ON 的区别

USING 是显式连接条件的缩写语法，它接收一个用逗号分隔的字段名列表，这些字段必须是连接表共有的字段。例如，USING (time) 等效于 ON (t1.time = t2.time)。当使用 ON 关键字时，两个表中的 time 字段在逻辑上是区分的，分别表示为 t1.time 和 t2.time。而当使用 USING 关键字时，逻辑上只会有一个 time 字段。而最终的查询结果取决于 SELECT 语句中指定的字段。

3.1.2 隐式指定连接条件

隐式连接不需要出现 JOIN、ON、USING 关键字，而是通过在 WHERE 子句中指定条件来实现表与表之间的连接。

SQL语法如下所示：

// 隐式连接, 在WHERE子句里指定连接条件
SELECT selectExpr [, selectExpr] ... FROM <TABLE_NAME> [, <TABLE_NAME>] ... [WHERE whereCondition] 

3.2 外连接（Outer Join）

如果没有匹配的行，仍然可以通过指定外连接返回行。外连接可以是：

• LEFT（左侧表的所有行至少出现一次）
• RIGHT（右侧表的所有行至少出现一次）
• FULL（两个表的所有行至少出现一次）

3.2.1 左外连接

LEFT [OUTER] JOIN，即左外连接，返回左表中的所有记录，以及右表中与左表满足连接条件的记录。对于右表中没有与左表匹配的记录返回NULL值。因为需要指定LEFT JOIN关键字，所以左外连接一般只使用显示连接的语法，即在ON或USING后指定连接条件。

V 2.0.5 及以后版本支持

SQL语法如下所示：

// 显示连接, 在ON关键字后指定连接条件或在Using关键字后指定连接列
SELECT selectExpr [, selectExpr] ... FROM <TABLE_NAME> LEFT [OUTER] JOIN <TABLE_NAME> joinCriteria [WHERE whereCondition]

joinCriteria
    : ON booleanExpression
    | USING '(' identifier (',' identifier)* ')'
    ;

3.2.2 右外连接

RIGHT [OUTER] JOIN， 即右外连接，返回右表中的所有记录，以及左表中与右表满足连接条件的记录。对于左表中没有与右表匹配的记录返回NULL值。

V 2.0.5 及以后版本支持

RIGHT JOIN与LEFT JOIN是“对称”的操作，通常情况下，使用 LEFT JOIN 比较常见，因为在实际应用中通常更关心左表的数据。而且RIGHT JOIN 总可以转换为LEFT JOIN ，即A RIGHT JOIN B ON A.id=B.id 的查询结果与 B LEFT JOIN A on B.id = A.id是等价的。

SQL语法如下所示：

// 在ON关键字后指定连接条件或在Using关键字后指定连接列
SELECT selectExpr [, selectExpr] ... FROM <TABLE_NAME> RIGHT [OUTER] JOIN <TABLE_NAME> joinCriteria [WHERE whereCondition]

joinCriteria
    : ON booleanExpression
    | USING '(' identifier (',' identifier)* ')'
    ;

3.2.3 全外连接

FULL [OUTER] JOIN，即全外连接，返回左表和右表连接后的所有记录。如果某个表中的记录没有与另一个表中的记录匹配，则会返回 NULL 值。FULL JOIN 只能使用显式连接方式。

SQL语法如下所示：

// 在ON关键字后指定连接条件或在Using关键字后指定连接列
SELECT selectExpr [, selectExpr] ... FROM <TABLE_NAME> FULL [OUTER] JOIN <TABLE_NAME> joinCriteria [WHERE whereCondition]

joinCriteria
    : ON booleanExpression
    | USING '(' identifier (',' identifier)* ')'
    ;

3.3 交叉连接（Cross Join）

交叉连接表示两个表的的笛卡尔积，返回左表N行记录和右表M行记录的N*M种组合。该种连接方式在实际中使用最少。

3.4 非精确点连接（​ASOF JOIN​）

IoTDB ASOF JOIN 即非精确点连接方式，允许用户按照指定的规则以时间戳最接近的方式进行匹配。目前版本只支持 ASOF INNER/LEFT JOIN。

ASOF LEFT JOIN 方式 V2.0.5 及以后版本支持

SQL语法如下所示：

SELECT selectExpr [, selectExpr] ... FROM 
<TABLE_NAME1> ASOF[(tolerance theta)] [INNER|LEFT] JOIN <TABLE_NAME2> joinCriteria 
[WHERE whereCondition]
WHERE a.time = tolerance(b.time, 1s)

joinCriteria
    : ON <TABLE_NAME1>.time comparisonOperator <TABLE_NAME2>.time 
    ;

comparisonOperator
    : < | <= | > | >=
    ;

说明：

• ASOF JOIN 默认使用 ASOF INNER JOIN 实现
• 当使用 ON 关键字进行连接时，连接条件里必须包含左右均为 TIMESTAMP 类型的不等式连接条件（即主连接条件），不等式仅支持 ">", ">=", "<", "<=" 四种操作符，其代表的连接匹配规则如下：其中 lt 表示 left table, rt 表示 right table

运算符	连接方式
lt.time >= rt.time	左表中时间戳大于等于右表时间戳且时间戳最接近
lt.time > rt.time	左表中时间戳大于左表时间戳且时间戳最接近
lt.time <= rt.time	左表中时间戳小于等于右表时间戳且时间戳最接近
lt.time < rt.time	左表中时间戳小于右表时间戳且时间戳最接近

• Tolerance 参数：容差，表示右表数据查找允许的最大时间差。（用 TimeDuration 表示，如 1d 表示1天）。如果不指定 Tolerance 参数，则表示查找时间范围为​正无穷​。注意：目前仅 ASOF INNER JOIN 中支持该参数
• ASOF 除了主连接条件外，还可以指定针对其它列（ID、Attribute、Measurement）的等值连接条件，表示查询结果按照其它列进行分组。主连接条件必须放在最后，并且主连接条件与其他条件（如果有的话）之间必须使用“AND”进行连接。

3.5 半连接/反连接（SEMI JOIN/ANTI-SEMI JOIN）

半连接是一种特殊的连接操作，其目的是确定一个表中的行是否存在于另一个表中。半连接返回的结果集包含符合连接条件的第一个表的行，而不包含第二个表的实际数据。与半连接对应的是反连接，反连接目的是确定两个表之间没有匹配的行。反连接返回的结果集包含满足连接条件的第一个表中的行，但不包含第二个表中与之匹配的行。

在IoTDB表模型中，不提供`SEMI JOIN`语法，支持使用 IN 子查询或 EXISTS 子查询来实现半连接；同样，也不提供`ANTI SEMI JOIN`语法，支持使用 NOT IN 或 NOT EXISTS 子查询来实现反连接。关于子查询详细说明可参考嵌套查询

• 半连接语法示例如下：

// 使用IN实现半连接
SELECT *
FROM table1
WHERE time IN (SELECT time FROM table2);

// 使用EXISTS实现半连接
SELECT *
FROM table1 t1
WHERE EXISTS (SELECT 1 FROM table2 t2 WHERE t1.time = t2.time);

// 等同于其他数据库的SEMI JOIN语法
SELECT table1.*
FROM table1 SEMI JOIN table2
on table1.time=table2.time;

• 反连接语法示例如下：

// 使用NOT IN实现反连接
SELECT *
FROM table1
WHERE time NOT IN (SELECT time FROM table2);

// 使用NOT EXISTS实现反连接
SELECT *
FROM table1 t1
WHERE NOT EXISTS (SELECT 1 FROM table2 t2 WHERE t1.time = t2.time);

// 等同于其他数据库的ANTI SEMI JOIN语法
SELECT table1.*
FROM table1 ANTI JOIN table2
on table1.time=table2.time;

4. 示例数据

在示例数据页面中，包含了用于构建表结构和插入数据的SQL语句，下载并在IoTDB CLI中执行这些语句，即可将数据导入IoTDB，您可以使用这些数据来测试和执行示例中的SQL语句，并获得相应的结果。

4.1 From 示例

4.1.1 从单个表查询

示例 1：此查询将返回 table1 中的所有记录，并按时间排序。

SELECT * FROM table1 ORDER BY time;

查询结果：

+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+
|                         time|region|plant_id|device_id|model_id|maintenance|temperature|humidity|status|                arrival_time|
+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+
|2024-11-26T13:37:00.000+08:00|  北京|    1001|      100|       A|        180|       90.0|    35.1|  true|2024-11-26T13:37:34.000+08:00|
|2024-11-26T13:38:00.000+08:00|  北京|    1001|      100|       A|        180|       90.0|    35.1|  true|2024-11-26T13:38:25.000+08:00|
|2024-11-27T16:38:00.000+08:00|  北京|    1001|      101|       B|        180|       null|    35.1|  true|2024-11-27T16:37:01.000+08:00|
|2024-11-27T16:39:00.000+08:00|  北京|    1001|      101|       B|        180|       85.0|    35.3|  null|                         null|
|2024-11-27T16:40:00.000+08:00|  北京|    1001|      101|       B|        180|       85.0|    null|  null|2024-11-27T16:37:03.000+08:00|
|2024-11-27T16:41:00.000+08:00|  北京|    1001|      101|       B|        180|       85.0|    null|  null|2024-11-27T16:37:04.000+08:00|
|2024-11-27T16:42:00.000+08:00|  北京|    1001|      101|       B|        180|       null|    35.2| false|                         null|
|2024-11-27T16:43:00.000+08:00|  北京|    1001|      101|       B|        180|       null|    null| false|                         null|
|2024-11-27T16:44:00.000+08:00|  北京|    1001|      101|       B|        180|       null|    null| false|2024-11-27T16:37:08.000+08:00|
|2024-11-28T08:00:00.000+08:00|  上海|    3001|      100|       C|         90|       85.0|    null|  null|2024-11-28T08:00:09.000+08:00|
|2024-11-28T09:00:00.000+08:00|  上海|    3001|      100|       C|         90|       null|    40.9|  true|                         null|
|2024-11-28T10:00:00.000+08:00|  上海|    3001|      100|       C|         90|       85.0|    35.2|  null|2024-11-28T10:00:11.000+08:00|
|2024-11-28T11:00:00.000+08:00|  上海|    3001|      100|       C|         90|       88.0|    45.1|  true|2024-11-28T11:00:12.000+08:00|
|2024-11-29T10:00:00.000+08:00|  上海|    3001|      101|       D|        360|       85.0|    null|  null|2024-11-29T10:00:13.000+08:00|
|2024-11-29T11:00:00.000+08:00|  上海|    3002|      100|       E|        180|       null|    45.1|  true|                         null|
|2024-11-29T18:30:00.000+08:00|  上海|    3002|      100|       E|        180|       90.0|    35.4|  true|2024-11-29T18:30:15.000+08:00|
|2024-11-30T09:30:00.000+08:00|  上海|    3002|      101|       F|        360|       90.0|    35.2|  true|                         null|
|2024-11-30T14:30:00.000+08:00|  上海|    3002|      101|       F|        360|       90.0|    34.8|  true|2024-11-30T14:30:17.000+08:00|
+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+
Total line number = 18
It costs 0.085s

示例 2：此查询将返回 table1中device为101的记录，并按时间排序。

SELECT * FROM table1 t1 where t1.device_id='101' order by time;

查询结果：

+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+
|                         time|region|plant_id|device_id|model_id|maintenance|temperature|humidity|status|                arrival_time|
+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+
|2024-11-27T16:38:00.000+08:00|  北京|    1001|      101|       B|        180|       null|    35.1|  true|2024-11-27T16:37:01.000+08:00|
|2024-11-27T16:39:00.000+08:00|  北京|    1001|      101|       B|        180|       85.0|    35.3|  null|                         null|
|2024-11-27T16:40:00.000+08:00|  北京|    1001|      101|       B|        180|       85.0|    null|  null|2024-11-27T16:37:03.000+08:00|
|2024-11-27T16:41:00.000+08:00|  北京|    1001|      101|       B|        180|       85.0|    null|  null|2024-11-27T16:37:04.000+08:00|
|2024-11-27T16:42:00.000+08:00|  北京|    1001|      101|       B|        180|       null|    35.2| false|                         null|
|2024-11-27T16:43:00.000+08:00|  北京|    1001|      101|       B|        180|       null|    null| false|                         null|
|2024-11-27T16:44:00.000+08:00|  北京|    1001|      101|       B|        180|       null|    null| false|2024-11-27T16:37:08.000+08:00|
|2024-11-29T10:00:00.000+08:00|  上海|    3001|      101|       D|        360|       85.0|    null|  null|2024-11-29T10:00:13.000+08:00|
|2024-11-30T09:30:00.000+08:00|  上海|    3002|      101|       F|        360|       90.0|    35.2|  true|                         null|
|2024-11-30T14:30:00.000+08:00|  上海|    3002|      101|       F|        360|       90.0|    34.8|  true|2024-11-30T14:30:17.000+08:00|
+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+
Total line number = 10
It costs 0.061s

4.1.2 从子查询中查询

示例 1：此查询将返回 table1 中的记录总数。

SELECT COUNT(*) AS count FROM (SELECT * FROM table1);

查询结果：

+-----+
|count|
+-----+
|   18|
+-----+
Total line number = 1
It costs 0.072s

4.2 Join 示例

4.2.1 Inner Join

示例 1：显式连接

SELECT 
  t1.time, 
  t1.device_id as device1, 
  t1.temperature as temperature1, 
  t2.device_id as device2, 
  t2.temperature as temperature2 
FROM 
  table1 t1 JOIN table2 t2 
ON t1.time = t2.time

查询结果：

+-----------------------------+-------+------------+-------+------------+
|                         time|device1|temperature1|device2|temperature2|
+-----------------------------+-------+------------+-------+------------+
|2024-11-26T13:37:00.000+08:00|    100|        90.0|    100|        90.0|
|2024-11-28T08:00:00.000+08:00|    100|        85.0|    100|        85.0|
|2024-11-29T11:00:00.000+08:00|    100|        null|    100|        null|
+-----------------------------+-------+------------+-------+------------+
Total line number = 3
It costs 0.076s

示例 2：显式连接

SELECT time,
  t1.device_id as device1, 
  t1.temperature as temperature1, 
  t2.device_id as device2, 
  t2.temperature as temperature2   
FROM 
  table1 t1 JOIN table2 t2
USING(time)

查询结果：

+-----------------------------+-------+------------+-------+------------+
|                         time|device1|temperature1|device2|temperature2|
+-----------------------------+-------+------------+-------+------------+
|2024-11-26T13:37:00.000+08:00|    100|        90.0|    100|        90.0|
|2024-11-28T08:00:00.000+08:00|    100|        85.0|    100|        85.0|
|2024-11-29T11:00:00.000+08:00|    100|        null|    100|        null|
+-----------------------------+-------+------------+-------+------------+
Total line number = 3
It costs 0.081s

示例 3：隐式连接

SELECT t1.time, 
  t1.device_id as device1, 
  t1.temperature as temperature1, 
  t2.device_id as device2, 
  t2.temperature as temperature2 
FROM 
table1 t1, table2 t2
WHERE
t1.time=t2.time

查询结果：

+-----------------------------+-------+------------+-------+------------+
|                         time|device1|temperature1|device2|temperature2|
+-----------------------------+-------+------------+-------+------------+
|2024-11-26T13:37:00.000+08:00|    100|        90.0|    100|        90.0|
|2024-11-28T08:00:00.000+08:00|    100|        85.0|    100|        85.0|
|2024-11-29T11:00:00.000+08:00|    100|        null|    100|        null|
+-----------------------------+-------+------------+-------+------------+
Total line number = 3
It costs 0.082s

示例4：非 time 列等值连接

SELECT 
  t1.time, 
  t1.device_id as device1, 
  t1.temperature as temperature1, 
  t2.device_id as device2, 
  t2.temperature as temperature2 
FROM 
  table1 t1 JOIN table2 t2 
ON t1.device_id = t2.device_id 
ORDER BY t1.time
LIMIT 10

查询结果：

+-----------------------------+-------+------------+-------+------------+
|                         time|device1|temperature1|device2|temperature2|
+-----------------------------+-------+------------+-------+------------+
|2024-11-26T13:37:00.000+08:00|    100|        90.0|    100|        null|
|2024-11-26T13:37:00.000+08:00|    100|        90.0|    100|        90.0|
|2024-11-26T13:37:00.000+08:00|    100|        90.0|    100|        85.0|
|2024-11-26T13:38:00.000+08:00|    100|        90.0|    100|        null|
|2024-11-26T13:38:00.000+08:00|    100|        90.0|    100|        90.0|
|2024-11-26T13:38:00.000+08:00|    100|        90.0|    100|        85.0|
|2024-11-27T16:38:00.000+08:00|    101|        null|    101|        90.0|
|2024-11-27T16:38:00.000+08:00|    101|        null|    101|        85.0|
|2024-11-27T16:38:00.000+08:00|    101|        null|    101|        85.0|
|2024-11-27T16:39:00.000+08:00|    101|        85.0|    101|        90.0|
+-----------------------------+-------+------------+-------+------------+
Total line number = 10
It costs 0.030s

4.2.2 Outer Join

1. LEFT JOIN

示例 1：显式连接

SELECT 
  t1.time as time1, t2.time as time2, 
  t1.device_id as device1, 
  t1.temperature as temperature1, 
  t2.device_id as device2, 
  t2.temperature as temperature2  
FROM 
  table1 t1 LEFT JOIN table2 t2 
ON t1.time = t2.time

查询结果：

+-----------------------------+-----------------------------+-------+------------+-------+------------+
|                        time1|                        time2|device1|temperature1|device2|temperature2|
+-----------------------------+-----------------------------+-------+------------+-------+------------+
|2024-11-26T13:37:00.000+08:00|2024-11-26T13:37:00.000+08:00|    100|        90.0|    100|        90.0|
|2024-11-26T13:38:00.000+08:00|                         null|    100|        90.0|   null|        null|
|2024-11-27T16:38:00.000+08:00|                         null|    101|        null|   null|        null|
|2024-11-27T16:39:00.000+08:00|                         null|    101|        85.0|   null|        null|
|2024-11-27T16:40:00.000+08:00|                         null|    101|        85.0|   null|        null|
|2024-11-27T16:41:00.000+08:00|                         null|    101|        85.0|   null|        null|
|2024-11-27T16:42:00.000+08:00|                         null|    101|        null|   null|        null|
|2024-11-27T16:43:00.000+08:00|                         null|    101|        null|   null|        null|
|2024-11-27T16:44:00.000+08:00|                         null|    101|        null|   null|        null|
|2024-11-28T08:00:00.000+08:00|2024-11-28T08:00:00.000+08:00|    100|        85.0|    100|        85.0|
|2024-11-28T09:00:00.000+08:00|                         null|    100|        null|   null|        null|
|2024-11-28T10:00:00.000+08:00|                         null|    100|        85.0|   null|        null|
|2024-11-28T11:00:00.000+08:00|                         null|    100|        88.0|   null|        null|
|2024-11-29T10:00:00.000+08:00|                         null|    101|        85.0|   null|        null|
|2024-11-29T11:00:00.000+08:00|2024-11-29T11:00:00.000+08:00|    100|        null|    100|        null|
|2024-11-29T18:30:00.000+08:00|                         null|    100|        90.0|   null|        null|
|2024-11-30T09:30:00.000+08:00|                         null|    101|        90.0|   null|        null|
|2024-11-30T14:30:00.000+08:00|                         null|    101|        90.0|   null|        null|
+-----------------------------+-----------------------------+-------+------------+-------+------------+
Total line number = 18
It costs 0.031s

示例 2：显式连接

SELECT 
  time, 
  t1.device_id as device1, 
  t1.temperature as temperature1, 
  t2.device_id as device2, 
  t2.temperature as temperature2  
FROM 
  table1 t1 LEFT JOIN table2 t2 
USING(time)

查询结果：

+-----------------------------+-------+------------+-------+------------+
|                         time|device1|temperature1|device2|temperature2|
+-----------------------------+-------+------------+-------+------------+
|2024-11-26T13:37:00.000+08:00|    100|        90.0|    100|        90.0|
|2024-11-26T13:38:00.000+08:00|    100|        90.0|   null|        null|
|2024-11-27T16:38:00.000+08:00|    101|        null|   null|        null|
|2024-11-27T16:39:00.000+08:00|    101|        85.0|   null|        null|
|2024-11-27T16:40:00.000+08:00|    101|        85.0|   null|        null|
|2024-11-27T16:41:00.000+08:00|    101|        85.0|   null|        null|
|2024-11-27T16:42:00.000+08:00|    101|        null|   null|        null|
|2024-11-27T16:43:00.000+08:00|    101|        null|   null|        null|
|2024-11-27T16:44:00.000+08:00|    101|        null|   null|        null|
|2024-11-28T08:00:00.000+08:00|    100|        85.0|    100|        85.0|
|2024-11-28T09:00:00.000+08:00|    100|        null|   null|        null|
|2024-11-28T10:00:00.000+08:00|    100|        85.0|   null|        null|
|2024-11-28T11:00:00.000+08:00|    100|        88.0|   null|        null|
|2024-11-29T10:00:00.000+08:00|    101|        85.0|   null|        null|
|2024-11-29T11:00:00.000+08:00|    100|        null|    100|        null|
|2024-11-29T18:30:00.000+08:00|    100|        90.0|   null|        null|
|2024-11-30T09:30:00.000+08:00|    101|        90.0|   null|        null|
|2024-11-30T14:30:00.000+08:00|    101|        90.0|   null|        null|
+-----------------------------+-------+------------+-------+------------+
Total line number = 18
It costs 0.031s

示例3：连接条件为非time列

SELECT 
  region,
  t1.time as time1,
  t1.temperature as temperature1, 
  t2.time as time2, 
  t2.temperature as temperature2 
FROM 
  table1 t1 LEFT JOIN table2 t2 
USING(region)
LIMIT 10

查询结果：

+------+-----------------------------+------------+-----------------------------+------------+
|region|                        time1|temperature1|                        time2|temperature2|
+------+-----------------------------+------------+-----------------------------+------------+
|  上海|2024-11-29T11:00:00.000+08:00|        null|2024-11-29T11:00:00.000+08:00|        null|
|  上海|2024-11-29T11:00:00.000+08:00|        null|2024-11-28T08:00:00.000+08:00|        85.0|
|  上海|2024-11-29T11:00:00.000+08:00|        null|2024-11-30T00:00:00.000+08:00|        90.0|
|  上海|2024-11-29T11:00:00.000+08:00|        null|2024-11-29T00:00:00.000+08:00|        85.0|
|  上海|2024-11-30T09:30:00.000+08:00|        90.0|2024-11-29T11:00:00.000+08:00|        null|
|  上海|2024-11-30T09:30:00.000+08:00|        90.0|2024-11-28T08:00:00.000+08:00|        85.0|
|  上海|2024-11-30T09:30:00.000+08:00|        90.0|2024-11-30T00:00:00.000+08:00|        90.0|
|  上海|2024-11-30T09:30:00.000+08:00|        90.0|2024-11-29T00:00:00.000+08:00|        85.0|
|  上海|2024-11-29T18:30:00.000+08:00|        90.0|2024-11-29T11:00:00.000+08:00|        null|
|  上海|2024-11-29T18:30:00.000+08:00|        90.0|2024-11-28T08:00:00.000+08:00|        85.0|
+------+-----------------------------+------------+-----------------------------+------------+
Total line number = 10
It costs 0.038s

2. RIGHT JOIN

示例 1：显式连接

SELECT 
  t1.time as time1, t2.time as time2, 
  t1.device_id as device1, 
  t1.temperature as temperature1, 
  t2.device_id as device2, 
  t2.temperature as temperature2  
FROM 
  table1 t1 RIGHT JOIN table2 t2 
ON t1.time = t2.time

查询结果：

+-----------------------------+-----------------------------+-------+------------+-------+------------+
|                        time1|                        time2|device1|temperature1|device2|temperature2|
+-----------------------------+-----------------------------+-------+------------+-------+------------+
|2024-11-26T13:37:00.000+08:00|2024-11-26T13:37:00.000+08:00|    100|        90.0|    100|        90.0|
|                         null|2024-11-27T00:00:00.000+08:00|   null|        null|    101|        85.0|
|2024-11-28T08:00:00.000+08:00|2024-11-28T08:00:00.000+08:00|    100|        85.0|    100|        85.0|
|                         null|2024-11-29T00:00:00.000+08:00|   null|        null|    101|        85.0|
|2024-11-29T11:00:00.000+08:00|2024-11-29T11:00:00.000+08:00|    100|        null|    100|        null|
|                         null|2024-11-30T00:00:00.000+08:00|   null|        null|    101|        90.0|
+-----------------------------+-----------------------------+-------+------------+-------+------------+
Total line number = 6
It costs 0.030s

示例 2：显式连接

SELECT 
  time, 
  t1.device_id as device1, 
  t1.temperature as temperature1, 
  t2.device_id as device2, 
  t2.temperature as temperature2  
FROM 
  table1 t1 RIGHT JOIN table2 t2 
USING(time)

查询结果：

+-----------------------------+-------+------------+-------+------------+
|                         time|device1|temperature1|device2|temperature2|
+-----------------------------+-------+------------+-------+------------+
|2024-11-26T13:37:00.000+08:00|    100|        90.0|    100|        90.0|
|2024-11-27T00:00:00.000+08:00|   null|        null|    101|        85.0|
|2024-11-28T08:00:00.000+08:00|    100|        85.0|    100|        85.0|
|2024-11-29T00:00:00.000+08:00|   null|        null|    101|        85.0|
|2024-11-29T11:00:00.000+08:00|    100|        null|    100|        null|
|2024-11-30T00:00:00.000+08:00|   null|        null|    101|        90.0|
+-----------------------------+-------+------------+-------+------------+
Total line number = 6
It costs 0.053s

示例3：连接条件为非time列

SELECT 
  region,
  t1.time as time1,
  t1.temperature as temperature1, 
  t2.time as time2, 
  t2.temperature as temperature2 
FROM 
  table1 t1 RIGHT JOIN table2 t2 
USING(region)
LIMIT 10

查询结果：

+------+-----------------------------+------------+-----------------------------+------------+
|region|                        time1|temperature1|                        time2|temperature2|
+------+-----------------------------+------------+-----------------------------+------------+
|  上海|2024-11-29T11:00:00.000+08:00|        null|2024-11-29T11:00:00.000+08:00|        null|
|  上海|2024-11-30T09:30:00.000+08:00|        90.0|2024-11-29T11:00:00.000+08:00|        null|
|  上海|2024-11-29T18:30:00.000+08:00|        90.0|2024-11-29T11:00:00.000+08:00|        null|
|  上海|2024-11-28T08:00:00.000+08:00|        85.0|2024-11-29T11:00:00.000+08:00|        null|
|  上海|2024-11-30T14:30:00.000+08:00|        90.0|2024-11-29T11:00:00.000+08:00|        null|
|  上海|2024-11-29T10:00:00.000+08:00|        85.0|2024-11-29T11:00:00.000+08:00|        null|
|  上海|2024-11-28T09:00:00.000+08:00|        null|2024-11-29T11:00:00.000+08:00|        null|
|  上海|2024-11-28T10:00:00.000+08:00|        85.0|2024-11-29T11:00:00.000+08:00|        null|
|  上海|2024-11-28T11:00:00.000+08:00|        88.0|2024-11-29T11:00:00.000+08:00|        null|
|  上海|2024-11-29T11:00:00.000+08:00|        null|2024-11-28T08:00:00.000+08:00|        85.0|
+------+-----------------------------+------------+-----------------------------+------------+
Total line number = 10
It costs 0.029s

3. FULL JOIN

示例 1：显式连接

SELECT 
  t1.time as time1, t2.time as time2, 
  t1.device_id as device1, 
  t1.temperature as temperature1, 
  t2.device_id as device2, 
  t2.temperature as temperature2  
FROM 
  table1 t1 FULL JOIN table2 t2 
ON t1.time = t2.time

查询结果：

+-----------------------------+-----------------------------+-------+------------+-------+------------+
|                        time1|                        time2|device1|temperature1|device2|temperature2|
+-----------------------------+-----------------------------+-------+------------+-------+------------+
|2024-11-26T13:37:00.000+08:00|2024-11-26T13:37:00.000+08:00|    100|        90.0|    100|        90.0|
|2024-11-26T13:38:00.000+08:00|                         null|    100|        90.0|   null|        null|
|                         null|2024-11-27T00:00:00.000+08:00|   null|        null|    101|        85.0|
|2024-11-27T16:38:00.000+08:00|                         null|    101|        null|   null|        null|
|2024-11-27T16:39:00.000+08:00|                         null|    101|        85.0|   null|        null|
|2024-11-27T16:40:00.000+08:00|                         null|    101|        85.0|   null|        null|
|2024-11-27T16:41:00.000+08:00|                         null|    101|        85.0|   null|        null|
|2024-11-27T16:42:00.000+08:00|                         null|    101|        null|   null|        null|
|2024-11-27T16:43:00.000+08:00|                         null|    101|        null|   null|        null|
|2024-11-27T16:44:00.000+08:00|                         null|    101|        null|   null|        null|
|2024-11-28T08:00:00.000+08:00|2024-11-28T08:00:00.000+08:00|    100|        85.0|    100|        85.0|
|2024-11-28T09:00:00.000+08:00|                         null|    100|        null|   null|        null|
|2024-11-28T10:00:00.000+08:00|                         null|    100|        85.0|   null|        null|
|2024-11-28T11:00:00.000+08:00|                         null|    100|        88.0|   null|        null|
|                         null|2024-11-29T00:00:00.000+08:00|   null|        null|    101|        85.0|
|2024-11-29T10:00:00.000+08:00|                         null|    101|        85.0|   null|        null|
|2024-11-29T11:00:00.000+08:00|2024-11-29T11:00:00.000+08:00|    100|        null|    100|        null|
|2024-11-29T18:30:00.000+08:00|                         null|    100|        90.0|   null|        null|
|                         null|2024-11-30T00:00:00.000+08:00|   null|        null|    101|        90.0|
|2024-11-30T09:30:00.000+08:00|                         null|    101|        90.0|   null|        null|
|2024-11-30T14:30:00.000+08:00|                         null|    101|        90.0|   null|        null|
+-----------------------------+-----------------------------+-------+------------+-------+------------+
Total line number = 21
It costs 0.071s

示例 2：显式连接

SELECT 
  time, 
  t1.device_id as device1, 
  t1.temperature as temperature1, 
  t2.device_id as device2, 
  t2.temperature as temperature2  
FROM 
  table1 t1 FULL JOIN table2 t2 
USING(time)

查询结果：

+-----------------------------+-------+------------+-------+------------+
|                         time|device1|temperature1|device2|temperature2|
+-----------------------------+-------+------------+-------+------------+
|2024-11-26T13:37:00.000+08:00|    100|        90.0|    100|        90.0|
|2024-11-26T13:38:00.000+08:00|    100|        90.0|   null|        null|
|2024-11-27T00:00:00.000+08:00|   null|        null|    101|        85.0|
|2024-11-27T16:38:00.000+08:00|    101|        null|   null|        null|
|2024-11-27T16:39:00.000+08:00|    101|        85.0|   null|        null|
|2024-11-27T16:40:00.000+08:00|    101|        85.0|   null|        null|
|2024-11-27T16:41:00.000+08:00|    101|        85.0|   null|        null|
|2024-11-27T16:42:00.000+08:00|    101|        null|   null|        null|
|2024-11-27T16:43:00.000+08:00|    101|        null|   null|        null|
|2024-11-27T16:44:00.000+08:00|    101|        null|   null|        null|
|2024-11-28T08:00:00.000+08:00|    100|        85.0|    100|        85.0|
|2024-11-28T09:00:00.000+08:00|    100|        null|   null|        null|
|2024-11-28T10:00:00.000+08:00|    100|        85.0|   null|        null|
|2024-11-28T11:00:00.000+08:00|    100|        88.0|   null|        null|
|2024-11-29T00:00:00.000+08:00|   null|        null|    101|        85.0|
|2024-11-29T10:00:00.000+08:00|    101|        85.0|   null|        null|
|2024-11-29T11:00:00.000+08:00|    100|        null|    100|        null|
|2024-11-29T18:30:00.000+08:00|    100|        90.0|   null|        null|
|2024-11-30T00:00:00.000+08:00|   null|        null|    101|        90.0|
|2024-11-30T09:30:00.000+08:00|    101|        90.0|   null|        null|
|2024-11-30T14:30:00.000+08:00|    101|        90.0|   null|        null|
+-----------------------------+-------+------------+-------+------------+
Total line number = 21
It costs 0.073s

示例3：连接条件为非time列

SELECT 
  region,
  t1.time as time1,
  t1.temperature as temperature1, 
  t2.time as time2, 
  t2.temperature as temperature2 
FROM 
  table1 t1 FULL JOIN table2 t2 
USING(region)
LIMIT 10

查询结果：

+------+-----------------------------+------------+-----------------------------+------------+
|region|                        time1|temperature1|                        time2|temperature2|
+------+-----------------------------+------------+-----------------------------+------------+
|  上海|2024-11-29T11:00:00.000+08:00|        null|2024-11-29T11:00:00.000+08:00|        null|
|  上海|2024-11-29T11:00:00.000+08:00|        null|2024-11-28T08:00:00.000+08:00|        85.0|
|  上海|2024-11-29T11:00:00.000+08:00|        null|2024-11-30T00:00:00.000+08:00|        90.0|
|  上海|2024-11-29T11:00:00.000+08:00|        null|2024-11-29T00:00:00.000+08:00|        85.0|
|  上海|2024-11-30T09:30:00.000+08:00|        90.0|2024-11-29T11:00:00.000+08:00|        null|
|  上海|2024-11-30T09:30:00.000+08:00|        90.0|2024-11-28T08:00:00.000+08:00|        85.0|
|  上海|2024-11-30T09:30:00.000+08:00|        90.0|2024-11-30T00:00:00.000+08:00|        90.0|
|  上海|2024-11-30T09:30:00.000+08:00|        90.0|2024-11-29T00:00:00.000+08:00|        85.0|
|  上海|2024-11-29T18:30:00.000+08:00|        90.0|2024-11-29T11:00:00.000+08:00|        null|
|  上海|2024-11-29T18:30:00.000+08:00|        90.0|2024-11-28T08:00:00.000+08:00|        85.0|
+------+-----------------------------+------------+-----------------------------+------------+
Total line number = 10
It costs 0.040s

4.2.3 Cross Join

示例1： 显示连接

SELECT table1.*, table2.* FROM table1 CROSS JOIN table2 LIMIT 8;

查询结果：

+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+
|                         time|region|plant_id|device_id|model_id|maintenance|temperature|humidity|status|                 arrival_time|                         time|region|plant_id|device_id|model_id|maintenance|temperature|humidity|status|                 arrival_time|
+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+
|2024-11-30T09:30:00.000+08:00|  上海|    3002|      101|       F|        360|       90.0|    35.2|  true|                         null|2024-11-30T00:00:00.000+08:00|  上海|    3002|      101|       F|        360|       90.0|    35.2|  true|                         null|
|2024-11-30T09:30:00.000+08:00|  上海|    3002|      101|       F|        360|       90.0|    35.2|  true|                         null|2024-11-29T00:00:00.000+08:00|  上海|    3001|      101|       D|        360|       85.0|    35.1|  null|2024-11-29T10:00:13.000+08:00|
|2024-11-30T09:30:00.000+08:00|  上海|    3002|      101|       F|        360|       90.0|    35.2|  true|                         null|2024-11-27T00:00:00.000+08:00|  北京|    1001|      101|       B|        180|       85.0|    35.1|  true|2024-11-27T16:37:01.000+08:00|
|2024-11-30T09:30:00.000+08:00|  上海|    3002|      101|       F|        360|       90.0|    35.2|  true|                         null|2024-11-29T11:00:00.000+08:00|  上海|    3002|      100|       E|        180|       null|    45.1|  true|                         null|
|2024-11-30T09:30:00.000+08:00|  上海|    3002|      101|       F|        360|       90.0|    35.2|  true|                         null|2024-11-28T08:00:00.000+08:00|  上海|    3001|      100|       C|         90|       85.0|    35.2| false|2024-11-28T08:00:09.000+08:00|
|2024-11-30T09:30:00.000+08:00|  上海|    3002|      101|       F|        360|       90.0|    35.2|  true|                         null|2024-11-26T13:37:00.000+08:00|  北京|    1001|      100|       A|        180|       90.0|    35.1|  true|2024-11-26T13:37:34.000+08:00|
|2024-11-30T14:30:00.000+08:00|  上海|    3002|      101|       F|        360|       90.0|    34.8|  true|2024-11-30T14:30:17.000+08:00|2024-11-30T00:00:00.000+08:00|  上海|    3002|      101|       F|        360|       90.0|    35.2|  true|                         null|
|2024-11-30T14:30:00.000+08:00|  上海|    3002|      101|       F|        360|       90.0|    34.8|  true|2024-11-30T14:30:17.000+08:00|2024-11-29T00:00:00.000+08:00|  上海|    3001|      101|       D|        360|       85.0|    35.1|  null|2024-11-29T10:00:13.000+08:00|
+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+
Total line number = 8
It costs 0.282s

示例2： 隐式连接

SELECT table1.*, table2.* FROM table1, table2 LIMIT 8;

查询结果：

+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+
|                         time|region|plant_id|device_id|model_id|maintenance|temperature|humidity|status|                 arrival_time|                         time|region|plant_id|device_id|model_id|maintenance|temperature|humidity|status|                 arrival_time|
+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+
|2024-11-30T09:30:00.000+08:00|  上海|    3002|      101|       F|        360|       90.0|    35.2|  true|                         null|2024-11-30T00:00:00.000+08:00|  上海|    3002|      101|       F|        360|       90.0|    35.2|  true|                         null|
|2024-11-30T09:30:00.000+08:00|  上海|    3002|      101|       F|        360|       90.0|    35.2|  true|                         null|2024-11-29T00:00:00.000+08:00|  上海|    3001|      101|       D|        360|       85.0|    35.1|  null|2024-11-29T10:00:13.000+08:00|
|2024-11-30T09:30:00.000+08:00|  上海|    3002|      101|       F|        360|       90.0|    35.2|  true|                         null|2024-11-27T00:00:00.000+08:00|  北京|    1001|      101|       B|        180|       85.0|    35.1|  true|2024-11-27T16:37:01.000+08:00|
|2024-11-30T09:30:00.000+08:00|  上海|    3002|      101|       F|        360|       90.0|    35.2|  true|                         null|2024-11-29T11:00:00.000+08:00|  上海|    3002|      100|       E|        180|       null|    45.1|  true|                         null|
|2024-11-30T09:30:00.000+08:00|  上海|    3002|      101|       F|        360|       90.0|    35.2|  true|                         null|2024-11-28T08:00:00.000+08:00|  上海|    3001|      100|       C|         90|       85.0|    35.2| false|2024-11-28T08:00:09.000+08:00|
|2024-11-30T09:30:00.000+08:00|  上海|    3002|      101|       F|        360|       90.0|    35.2|  true|                         null|2024-11-26T13:37:00.000+08:00|  北京|    1001|      100|       A|        180|       90.0|    35.1|  true|2024-11-26T13:37:34.000+08:00|
|2024-11-30T14:30:00.000+08:00|  上海|    3002|      101|       F|        360|       90.0|    34.8|  true|2024-11-30T14:30:17.000+08:00|2024-11-30T00:00:00.000+08:00|  上海|    3002|      101|       F|        360|       90.0|    35.2|  true|                         null|
|2024-11-30T14:30:00.000+08:00|  上海|    3002|      101|       F|        360|       90.0|    34.8|  true|2024-11-30T14:30:17.000+08:00|2024-11-29T00:00:00.000+08:00|  上海|    3001|      101|       D|        360|       85.0|    35.1|  null|2024-11-29T10:00:13.000+08:00|
+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+
Total line number = 8
It costs 0.047s

4.2.4 Asof join

1. ASOF INNER JOIN

示例1：不指定 Tolerance 参数，且 table1 中时间戳大于等于 table2 时间戳且时间戳最接近

SELECT t1.time as time1, t1.device_id as device1, t1.temperature as temperature1, t2.time as time2, t2.device_id as device2, t2.temperature as temperature2 FROM  table1 t1 ASOF JOIN table2 t2 ON t1.time>=t2.time;

查询结果

+-----------------------------+-------+------------+-----------------------------+-------+------------+
|                        time1|device1|temperature1|                        time2|device2|temperature2|
+-----------------------------+-------+------------+-----------------------------+-------+------------+
|2024-11-30T14:30:00.000+08:00|    101|        90.0|2024-11-30T00:00:00.000+08:00|    101|        90.0|
|2024-11-30T09:30:00.000+08:00|    101|        90.0|2024-11-30T00:00:00.000+08:00|    101|        90.0|
|2024-11-29T18:30:00.000+08:00|    100|        90.0|2024-11-29T11:00:00.000+08:00|    100|        null|
|2024-11-29T11:00:00.000+08:00|    100|        null|2024-11-29T11:00:00.000+08:00|    100|        null|
|2024-11-29T10:00:00.000+08:00|    101|        85.0|2024-11-29T00:00:00.000+08:00|    101|        85.0|
|2024-11-28T11:00:00.000+08:00|    100|        88.0|2024-11-28T08:00:00.000+08:00|    100|        85.0|
|2024-11-28T10:00:00.000+08:00|    100|        85.0|2024-11-28T08:00:00.000+08:00|    100|        85.0|
|2024-11-28T09:00:00.000+08:00|    100|        null|2024-11-28T08:00:00.000+08:00|    100|        85.0|
|2024-11-28T08:00:00.000+08:00|    100|        85.0|2024-11-28T08:00:00.000+08:00|    100|        85.0|
|2024-11-27T16:44:00.000+08:00|    101|        null|2024-11-27T00:00:00.000+08:00|    101|        85.0|
|2024-11-27T16:43:00.000+08:00|    101|        null|2024-11-27T00:00:00.000+08:00|    101|        85.0|
|2024-11-27T16:42:00.000+08:00|    101|        null|2024-11-27T00:00:00.000+08:00|    101|        85.0|
|2024-11-27T16:41:00.000+08:00|    101|        85.0|2024-11-27T00:00:00.000+08:00|    101|        85.0|
|2024-11-27T16:40:00.000+08:00|    101|        85.0|2024-11-27T00:00:00.000+08:00|    101|        85.0|
|2024-11-27T16:39:00.000+08:00|    101|        85.0|2024-11-27T00:00:00.000+08:00|    101|        85.0|
|2024-11-27T16:38:00.000+08:00|    101|        null|2024-11-27T00:00:00.000+08:00|    101|        85.0|
|2024-11-26T13:38:00.000+08:00|    100|        90.0|2024-11-26T13:37:00.000+08:00|    100|        90.0|
|2024-11-26T13:37:00.000+08:00|    100|        90.0|2024-11-26T13:37:00.000+08:00|    100|        90.0|
+-----------------------------+-------+------------+-----------------------------+-------+------------+

示例2：指定 Tolerance 参数，且 table1 中时间戳大于等于 table2 时间戳且时间戳最接近

SELECT t1.time as time1, t1.device_id as device1, t1.temperature as temperature1, t2.time as time2, t2.device_id as device2, t2.temperature as temperature2 FROM table1 t1 ASOF(tolerance 2s) JOIN table2 t2 ON t1.time>=t2.time;

查询结果

+-----------------------------+-------+------------+-----------------------------+-------+------------+
|                        time1|device1|temperature1|                        time2|device2|temperature2|
+-----------------------------+-------+------------+-----------------------------+-------+------------+
|2024-11-29T11:00:00.000+08:00|    100|        null|2024-11-29T11:00:00.000+08:00|    100|        null|
|2024-11-28T08:00:00.000+08:00|    100|        85.0|2024-11-28T08:00:00.000+08:00|    100|        85.0|
|2024-11-26T13:37:00.000+08:00|    100|        90.0|2024-11-26T13:37:00.000+08:00|    100|        90.0|
+-----------------------------+-------+------------+-----------------------------+-------+------------+

示例3：指定 Tolerance 参数，plant_id 相同且 table1 中时间戳大于等于 table2 时间戳且时间戳最接近

SELECT t1.time as time1, t1.plant_id as plant1, t1.temperature as temperature1, t2.time as time2, t2.plant_id as plant2, t2.temperature as temperature2 FROM table1 t1 ASOF(tolerance 2s) JOIN table2 t2 ON t1.plant_id=t2.plant_id and t1.time>=t2.time;

查询结果

+-----------------------------+------+------------+-----------------------------+------+------------+
|                        time1|plant1|temperature1|                        time2|plant2|temperature2|
+-----------------------------+------+------------+-----------------------------+------+------------+
|2024-11-26T13:37:00.000+08:00|  1001|        90.0|2024-11-26T13:37:00.000+08:00|  1001|        90.0|
|2024-11-28T08:00:00.000+08:00|  3001|        85.0|2024-11-28T08:00:00.000+08:00|  3001|        85.0|
|2024-11-29T11:00:00.000+08:00|  3002|        null|2024-11-29T11:00:00.000+08:00|  3002|        null|
+-----------------------------+------+------------+-----------------------------+------+------------+
Total line number = 3
It costs 0.046s

示例4： 今天某时间段与上周某时间段的 asof join

SELECT * FROM   (SELECT time, device_id AS device1, temperature AS temperature1  FROM table1 ) AS t1 ASOF JOIN   (SELECT time,  device_id AS device2,  temperature AS temperature2  FROM table1) AS t2 ON t1.time>date_bin(1w, t2.time) limit 10

查询结果：

+-----------------------------+-------+------------+-----------------------------+-------+------------+
|                         time|device1|temperature1|                         time|device2|temperature2|
+-----------------------------+-------+------------+-----------------------------+-------+------------+
|2024-11-30T14:30:00.000+08:00|    101|        90.0|2024-11-29T11:00:00.000+08:00|    100|        null|
|2024-11-30T14:30:00.000+08:00|    101|        90.0|2024-11-30T09:30:00.000+08:00|    101|        90.0|
|2024-11-30T14:30:00.000+08:00|    101|        90.0|2024-11-29T18:30:00.000+08:00|    100|        90.0|
|2024-11-30T14:30:00.000+08:00|    101|        90.0|2024-11-30T14:30:00.000+08:00|    101|        90.0|
|2024-11-30T14:30:00.000+08:00|    101|        90.0|2024-11-28T08:00:00.000+08:00|    100|        85.0|
|2024-11-30T14:30:00.000+08:00|    101|        90.0|2024-11-29T10:00:00.000+08:00|    101|        85.0|
|2024-11-30T14:30:00.000+08:00|    101|        90.0|2024-11-28T09:00:00.000+08:00|    100|        null|
|2024-11-30T14:30:00.000+08:00|    101|        90.0|2024-11-28T10:00:00.000+08:00|    100|        85.0|
|2024-11-30T14:30:00.000+08:00|    101|        90.0|2024-11-28T11:00:00.000+08:00|    100|        88.0|
|2024-11-30T09:30:00.000+08:00|    101|        90.0|2024-11-29T11:00:00.000+08:00|    100|        null|
+-----------------------------+-------+------------+-----------------------------+-------+------------+

2. ASOF LEFT JOIN

示例1：table1 中时间戳大于等于 table2 时间戳且时间戳最接近

SELECT t1.time as time1, t1.device_id as device1, t1.temperature as temperature1, t2.time as time2, t2.device_id as device2, t2.temperature as temperature2 FROM  table1 t1 ASOF LEFT JOIN table2 t2 ON t1.time>=t2.time order by time1;

查询结果

+-----------------------------+-------+------------+-----------------------------+-------+------------+
|                        time1|device1|temperature1|                        time2|device2|temperature2|
+-----------------------------+-------+------------+-----------------------------+-------+------------+
|2024-11-26T13:37:00.000+08:00|    100|        90.0|2024-11-26T13:37:00.000+08:00|    100|        90.0|
|2024-11-26T13:38:00.000+08:00|    100|        90.0|2024-11-26T13:37:00.000+08:00|    100|        90.0|
|2024-11-27T16:38:00.000+08:00|    101|        null|2024-11-27T00:00:00.000+08:00|    101|        85.0|
|2024-11-27T16:39:00.000+08:00|    101|        85.0|2024-11-27T00:00:00.000+08:00|    101|        85.0|
|2024-11-27T16:40:00.000+08:00|    101|        85.0|2024-11-27T00:00:00.000+08:00|    101|        85.0|
|2024-11-27T16:41:00.000+08:00|    101|        85.0|2024-11-27T00:00:00.000+08:00|    101|        85.0|
|2024-11-27T16:42:00.000+08:00|    101|        null|2024-11-27T00:00:00.000+08:00|    101|        85.0|
|2024-11-27T16:43:00.000+08:00|    101|        null|2024-11-27T00:00:00.000+08:00|    101|        85.0|
|2024-11-27T16:44:00.000+08:00|    101|        null|2024-11-27T00:00:00.000+08:00|    101|        85.0|
|2024-11-28T08:00:00.000+08:00|    100|        85.0|2024-11-28T08:00:00.000+08:00|    100|        85.0|
|2024-11-28T09:00:00.000+08:00|    100|        null|2024-11-28T08:00:00.000+08:00|    100|        85.0|
|2024-11-28T10:00:00.000+08:00|    100|        85.0|2024-11-28T08:00:00.000+08:00|    100|        85.0|
|2024-11-28T11:00:00.000+08:00|    100|        88.0|2024-11-28T08:00:00.000+08:00|    100|        85.0|
|2024-11-29T10:00:00.000+08:00|    101|        85.0|2024-11-29T00:00:00.000+08:00|    101|        85.0|
|2024-11-29T11:00:00.000+08:00|    100|        null|2024-11-29T11:00:00.000+08:00|    100|        null|
|2024-11-29T18:30:00.000+08:00|    100|        90.0|2024-11-29T11:00:00.000+08:00|    100|        null|
|2024-11-30T09:30:00.000+08:00|    101|        90.0|2024-11-30T00:00:00.000+08:00|    101|        90.0|
|2024-11-30T14:30:00.000+08:00|    101|        90.0|2024-11-30T00:00:00.000+08:00|    101|        90.0|
+-----------------------------+-------+------------+-----------------------------+-------+------------+
Total line number = 18
It costs 0.058s

示例2：plant_id 相同且 table1 中时间戳大于等于 table2 时间戳且时间戳最接近

SELECT t1.time as time1, t1.plant_id as plant1, t1.temperature as temperature1, t2.time as time2, t2.plant_id as plant2, t2.temperature as temperature2 FROM table1 t1 ASOF LEFT JOIN table2 t2 ON t1.plant_id=t2.plant_id and t1.time>=t2.time ORDER BY time1;

查询结果

+-----------------------------+------+------------+-----------------------------+------+------------+
|                        time1|plant1|temperature1|                        time2|plant2|temperature2|
+-----------------------------+------+------------+-----------------------------+------+------------+
|2024-11-26T13:37:00.000+08:00|  1001|        90.0|2024-11-26T13:37:00.000+08:00|  1001|        90.0|
|2024-11-26T13:38:00.000+08:00|  1001|        90.0|2024-11-26T13:37:00.000+08:00|  1001|        90.0|
|2024-11-27T16:38:00.000+08:00|  1001|        null|2024-11-27T00:00:00.000+08:00|  1001|        85.0|
|2024-11-27T16:39:00.000+08:00|  1001|        85.0|2024-11-27T00:00:00.000+08:00|  1001|        85.0|
|2024-11-27T16:40:00.000+08:00|  1001|        85.0|2024-11-27T00:00:00.000+08:00|  1001|        85.0|
|2024-11-27T16:41:00.000+08:00|  1001|        85.0|2024-11-27T00:00:00.000+08:00|  1001|        85.0|
|2024-11-27T16:42:00.000+08:00|  1001|        null|2024-11-27T00:00:00.000+08:00|  1001|        85.0|
|2024-11-27T16:43:00.000+08:00|  1001|        null|2024-11-27T00:00:00.000+08:00|  1001|        85.0|
|2024-11-27T16:44:00.000+08:00|  1001|        null|2024-11-27T00:00:00.000+08:00|  1001|        85.0|
|2024-11-28T08:00:00.000+08:00|  3001|        85.0|2024-11-28T08:00:00.000+08:00|  3001|        85.0|
|2024-11-28T09:00:00.000+08:00|  3001|        null|2024-11-28T08:00:00.000+08:00|  3001|        85.0|
|2024-11-28T10:00:00.000+08:00|  3001|        85.0|2024-11-28T08:00:00.000+08:00|  3001|        85.0|
|2024-11-28T11:00:00.000+08:00|  3001|        88.0|2024-11-28T08:00:00.000+08:00|  3001|        85.0|
|2024-11-29T10:00:00.000+08:00|  3001|        85.0|2024-11-29T00:00:00.000+08:00|  3001|        85.0|
|2024-11-29T11:00:00.000+08:00|  3002|        null|2024-11-29T11:00:00.000+08:00|  3002|        null|
|2024-11-29T18:30:00.000+08:00|  3002|        90.0|2024-11-29T11:00:00.000+08:00|  3002|        null|
|2024-11-30T09:30:00.000+08:00|  3002|        90.0|2024-11-30T00:00:00.000+08:00|  3002|        90.0|
|2024-11-30T14:30:00.000+08:00|  3002|        90.0|2024-11-30T00:00:00.000+08:00|  3002|        90.0|
+-----------------------------+------+------------+-----------------------------+------+------------+
Total line number = 18
It costs 0.022s