Google Git
Sign in
apache / iotdb-docs / refs/heads/main / . / src / zh / UserGuide / latest-Table / SQL-Manual / From-Join-Clause.md
blob: 4daa47dac894aadecd3820c8c1d6a4ceca918685 [file] [log] [blame] [view]
<!--
Licensed to the Apache Software Foundation (ASF) under one
or more contributor license agreements. See the NOTICE file
distributed with this work for additional information
regarding copyright ownership. The ASF licenses this file
to you under the Apache License, Version 2.0 (the
"License"); you may not use this file except in compliance
with the License. You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing,
software distributed under the License is distributed on an
"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
KIND, either express or implied. See the License for the
specific language governing permissions and limitations
under the License.
-->
# FROM & JOIN 子句
## 1. 语法概览
```sql
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语法如下所示：
```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语法如下所示：
```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语法如下所示：
```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语法如下所示：
```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语法如下所示：
```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语法如下所示：
```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 子查询来实现反连接。关于子查询详细说明可参考[嵌套查询](../SQL-Manual/Nested-Queries.md)
* 半连接语法示例如下：
```SQL
// 使用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;
```
* 反连接语法示例如下：
```SQL
// 使用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. 示例数据
在[示例数据页面](../Reference/Sample-Data.md)中，包含了用于构建表结构和插入数据的SQL语句，下载并在IoTDB CLI中执行这些语句，即可将数据导入IoTDB，您可以使用这些数据来测试和执行示例中的SQL语句，并获得相应的结果。
### 4.1 From 示例
#### 4.1.1 从单个表查询
示例 1：此查询将返回 `table1` 中的所有记录，并按时间排序。
```sql
SELECT * FROM table1 ORDER BY time;
```
查询结果：
```sql
+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+
| 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`的记录，并按时间排序。
```sql
SELECT * FROM table1 t1 where t1.device_id='101' order by time;
```
查询结果：
```sql
+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+
| 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` 中的记录总数。
```sql
SELECT COUNT(*) AS count FROM (SELECT * FROM table1);
```
查询结果：
```sql
+-----+
|count|
+-----+
| 18|
+-----+
Total line number = 1
It costs 0.072s
```
### 4.2 Join 示例
#### 4.2.1 Inner Join
示例 1：显式连接
```sql
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
```
查询结果：
```sql
+-----------------------------+-------+------------+-------+------------+
| 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：显式连接
```sql
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)
```
查询结果：
```sql
+-----------------------------+-------+------------+-------+------------+
| 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：隐式连接
```sql
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
```
查询结果：
```sql
+-----------------------------+-------+------------+-------+------------+
| 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 列等值连接
```SQL
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
```
查询结果：
```SQL
+-----------------------------+-------+------------+-------+------------+
| 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：显式连接
```SQL
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
```
查询结果：
```SQL
+-----------------------------+-----------------------------+-------+------------+-------+------------+
| 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：显式连接
```SQL
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)
```
查询结果：
```SQL
+-----------------------------+-------+------------+-------+------------+
| 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列
```SQL
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
```
查询结果：
```SQL
+------+-----------------------------+------------+-----------------------------+------------+
|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：显式连接
```SQL
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
```
查询结果：
```SQL
+-----------------------------+-----------------------------+-------+------------+-------+------------+
| 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：显式连接
```SQL
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)
```
查询结果：
```SQL
+-----------------------------+-------+------------+-------+------------+
| 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列
```SQL
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
```
查询结果：
```SQL
+------+-----------------------------+------------+-----------------------------+------------+
|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：显式连接
```sql
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
```
查询结果：
```sql
+-----------------------------+-----------------------------+-------+------------+-------+------------+
| 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：显式连接
```sql
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)
```
查询结果：
```sql
+-----------------------------+-------+------------+-------+------------+
| 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列
```sql
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
```
查询结果：
```sql
+------+-----------------------------+------------+-----------------------------+------------+
|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： 显示连接
```sql
SELECT table1.*, table2.* FROM table1 CROSS JOIN table2 LIMIT 8;
```
查询结果：
```sql
+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+
| 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： 隐式连接
```sql
SELECT table1.*, table2.* FROM table1, table2 LIMIT 8;
```
查询结果：
```sql
+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+
| 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 时间戳且时间戳最接近
```SQL
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;
```
查询结果
```SQL
+-----------------------------+-------+------------+-----------------------------+-------+------------+
| 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 时间戳且时间戳最接近
```SQL
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;
```
查询结果
```SQL
+-----------------------------+-------+------------+-----------------------------+-------+------------+
| 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 时间戳且时间戳最接近
```SQL
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;
```
查询结果
```SQL
+-----------------------------+------+------------+-----------------------------+------+------------+
| 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
```SQL
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
```
查询结果：
```SQL
+-----------------------------+-------+------------+-----------------------------+-------+------------+
| 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 时间戳且时间戳最接近
```SQL
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;
```
查询结果
```SQL
+-----------------------------+-------+------------+-----------------------------+-------+------------+
| 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 时间戳且时间戳最接近
```SQL
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;
```
查询结果
```SQL
+-----------------------------+------+------------+-----------------------------+------+------------+
| 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
```