This is an automated email from the ASF dual-hosted git repository.
lihaopeng pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris-website.git
The following commit(s) were added to refs/heads/master by this push:
new 8b5b928d2ae [doc](node) add some doc about intersect/except node (#975)
8b5b928d2ae is described below
commit 8b5b928d2ae7f596f0672b2b60806727266b6243
Author: zhangstar333 <[email protected]>
AuthorDate: Sat Aug 10 18:28:23 2024 +0800
[doc](node) add some doc about intersect/except node (#975)
---
docs/query/query-data/select.md | 41 ++++++++++++++++++++++
.../current/query/query-data/select.md | 40 +++++++++++++++++++++
.../version-2.0/query/query-data/select.md | 40 +++++++++++++++++++++
.../version-2.1/query/query-data/select.md | 39 ++++++++++++++++++++
.../version-2.0/query/query-data/select.md | 41 ++++++++++++++++++++++
.../version-2.1/query/query-data/select.md | 41 ++++++++++++++++++++++
6 files changed, 242 insertions(+)
diff --git a/docs/query/query-data/select.md b/docs/query/query-data/select.md
index db96b2cfe88..acd8bfa1dcb 100644
--- a/docs/query/query-data/select.md
+++ b/docs/query/query-data/select.md
@@ -111,6 +111,31 @@ UNION [ALL| DISTINCT] SELECT ......
By default, `UNION` removes duplicate rows from the result. The optional
`DISTINCT` keyword has no effect beyond the default, as it also specifies
duplicate row removal. Using the optional `ALL` keyword, no duplicate row
removal occurs, and the result includes all matching rows from all `SELECT`
statements.
+INTERSECT:
+
+```sql
+SELECT ...
+INTERSECT [DISTINCT] SELECT ......
+[INTERSECT [DISTINCT] SELECT ...]
+```
+
+`INTERSECT` is used to return the intersection of results from multiple
`SELECT` statements, with duplicate results removed.
+The effect of `INTERSECT` is equivalent to `INTERSECT DISTINCT`. The `ALL`
keyword is not supported.
+Each `SELECT` query must return the same number of columns, And when the
column types are inconsistent, they will be `CAST` to the same type.
+
+EXCEPT/MINUS:
+
+```sql
+SELECT ...
+EXCEPT [DISTINCT] SELECT ......
+[EXCEPT [DISTINCT] SELECT ...]
+```
+
+The `EXCEPT` clause is used to return the complement between the results of
multiple queries, meaning it returns the data from the left query that does not
exist in the right query, with duplicates removed.
+`EXCEPT` is functionally equivalent to `MINUS`.
+The effect of `EXCEPT` is the same as `EXCEPT DISTINCT`. The `ALL` keyword is
not supported.
+Each `SELECT` query must return the same number of columns, And when the
column types are inconsistent, they will be `CAST` to the same type.
+
WITH:
To specify a common table expression, use a `WITH` clause with one or more
comma-separated subclauses. Each subclause provides a subquery that generates a
result set and associates a name with the subquery. The following example
defines CTEs named `cte1` and `cte2` in the `WITH` clause, and refers to them
in the top-level `SELECT `following the WITH clause.
@@ -206,6 +231,22 @@ UNION
SELECT a FROM t2 WHERE a = 11 AND B = 2 ORDER by a LIMIT 10;
```
+- INTERSECT
+
+```sql
+SELECT a FROM t1 WHERE a = 10 AND B = 1 ORDER by a LIMIT 10
+INTERSECT
+SELECT a FROM t2 WHERE a = 11 AND B = 2 ORDER by a LIMIT 10;
+```
+
+- EXCEPT
+
+```sql
+SELECT a FROM t1 WHERE a = 10 AND B = 1 ORDER by a LIMIT 10
+EXCEPT
+SELECT a FROM t2 WHERE a = 11 AND B = 2 ORDER by a LIMIT 10;
+```
+
- WITH clause
```
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/query/query-data/select.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/query/query-data/select.md
index d5e1d9fbac9..dcc543fb2b4 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/query/query-data/select.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/query/query-data/select.md
@@ -160,6 +160,30 @@ UNION [ALL| DISTINCT] SELECT ......
默认行为 `UNION`是从结果中删除重复的行。可选 `DISTINCT` 关键字除了默认值之外没有任何效果，因为它还指定了重复行删除。使用可选 `ALL`
关键字，不会发生重复行删除，结果包括所有 `SELECT` 语句中的 所有匹配行
+INTERSECT 语法：
+
+```sql
+SELECT ...
+INTERSECT [DISTINCT] SELECT ......
+[INTERSECT [DISTINCT] SELECT ...]
+```
+
+`INTERSECT` 用于返回多个 `SELECT` 语句的结果之间的交集，并对结果进行去重。
+`INTERSECT` 效果等同于 `INTERSECT DISTINCT`。不支持 `ALL` 关键字。
+每条 `SELECT` 查询返回的列数必须相同，且当列类型不一致时，会 `CAST` 到相同类型。
+
+EXCEPT/MINUS 语法：
+
+```sql
+SELECT ...
+EXCEPT [DISTINCT] SELECT ......
+[EXCEPT [DISTINCT] SELECT ...]
+```
+`EXCEPT` 子句用于返回多个查询结果之间的补集，即返回左侧查询中在右侧查询中不存在的数据，并对结果集去重。
+`EXCEPT` 和 `MINUS` 功能对等。
+`EXCEPT` 效果等同于 `EXCEPT DISTINCT`。不支持 `ALL` 关键字。
+每条 `SELECT` 查询返回的列数必须相同，且当列类型不一致时，会 `CAST` 到相同类型。
+
WITH 语句：
要指定公用表表达式，请使用 `WITH`
具有一个或多个逗号分隔子句的子句。每个子条款都提供一个子查询，用于生成结果集，并将名称与子查询相关联。下面的示例定义名为的 CTE `cte1` 和
`cte2` 中 `WITH` 子句，并且是指在它们的顶层 `SELECT` 下面的 `WITH` 子句：
@@ -259,6 +283,22 @@ UNION
SELECT a FROM t2 WHERE a = 11 AND B = 2 ORDER by a LIMIT 10;
```
+- INTERSECT 示例
+
+```sql
+SELECT a FROM t1 WHERE a = 10 AND B = 1 ORDER by a LIMIT 10
+INTERSECT
+SELECT a FROM t2 WHERE a = 11 AND B = 2 ORDER by a LIMIT 10;
+```
+
+- EXCEPT 示例
+
+```sql
+SELECT a FROM t1 WHERE a = 10 AND B = 1 ORDER by a LIMIT 10
+EXCEPT
+SELECT a FROM t2 WHERE a = 11 AND B = 2 ORDER by a LIMIT 10;
+```
+
- WITH 子句示例
```sql
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.0/query/query-data/select.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.0/query/query-data/select.md
index d5e1d9fbac9..dcc543fb2b4 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.0/query/query-data/select.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.0/query/query-data/select.md
@@ -160,6 +160,30 @@ UNION [ALL| DISTINCT] SELECT ......
默认行为 `UNION`是从结果中删除重复的行。可选 `DISTINCT` 关键字除了默认值之外没有任何效果，因为它还指定了重复行删除。使用可选 `ALL`
关键字，不会发生重复行删除，结果包括所有 `SELECT` 语句中的 所有匹配行
+INTERSECT 语法：
+
+```sql
+SELECT ...
+INTERSECT [DISTINCT] SELECT ......
+[INTERSECT [DISTINCT] SELECT ...]
+```
+
+`INTERSECT` 用于返回多个 `SELECT` 语句的结果之间的交集，并对结果进行去重。
+`INTERSECT` 效果等同于 `INTERSECT DISTINCT`。不支持 `ALL` 关键字。
+每条 `SELECT` 查询返回的列数必须相同，且当列类型不一致时，会 `CAST` 到相同类型。
+
+EXCEPT/MINUS 语法：
+
+```sql
+SELECT ...
+EXCEPT [DISTINCT] SELECT ......
+[EXCEPT [DISTINCT] SELECT ...]
+```
+`EXCEPT` 子句用于返回多个查询结果之间的补集，即返回左侧查询中在右侧查询中不存在的数据，并对结果集去重。
+`EXCEPT` 和 `MINUS` 功能对等。
+`EXCEPT` 效果等同于 `EXCEPT DISTINCT`。不支持 `ALL` 关键字。
+每条 `SELECT` 查询返回的列数必须相同，且当列类型不一致时，会 `CAST` 到相同类型。
+
WITH 语句：
要指定公用表表达式，请使用 `WITH`
具有一个或多个逗号分隔子句的子句。每个子条款都提供一个子查询，用于生成结果集，并将名称与子查询相关联。下面的示例定义名为的 CTE `cte1` 和
`cte2` 中 `WITH` 子句，并且是指在它们的顶层 `SELECT` 下面的 `WITH` 子句：
@@ -259,6 +283,22 @@ UNION
SELECT a FROM t2 WHERE a = 11 AND B = 2 ORDER by a LIMIT 10;
```
+- INTERSECT 示例
+
+```sql
+SELECT a FROM t1 WHERE a = 10 AND B = 1 ORDER by a LIMIT 10
+INTERSECT
+SELECT a FROM t2 WHERE a = 11 AND B = 2 ORDER by a LIMIT 10;
+```
+
+- EXCEPT 示例
+
+```sql
+SELECT a FROM t1 WHERE a = 10 AND B = 1 ORDER by a LIMIT 10
+EXCEPT
+SELECT a FROM t2 WHERE a = 11 AND B = 2 ORDER by a LIMIT 10;
+```
+
- WITH 子句示例
```sql
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/query/query-data/select.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/query/query-data/select.md
index d5e1d9fbac9..7bf36892080 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/query/query-data/select.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/query/query-data/select.md
@@ -160,6 +160,30 @@ UNION [ALL| DISTINCT] SELECT ......
默认行为 `UNION`是从结果中删除重复的行。可选 `DISTINCT` 关键字除了默认值之外没有任何效果，因为它还指定了重复行删除。使用可选 `ALL`
关键字，不会发生重复行删除，结果包括所有 `SELECT` 语句中的 所有匹配行
+INTERSECT 语法：
+
+```sql
+SELECT ...
+INTERSECT [DISTINCT] SELECT ......
+[INTERSECT [DISTINCT] SELECT ...]
+```
+
+`INTERSECT` 用于返回多个 `SELECT` 语句的结果之间的交集，并对结果进行去重。
+`INTERSECT` 效果等同于 `INTERSECT DISTINCT`。不支持 `ALL` 关键字。
+每条 `SELECT` 查询返回的列数必须相同，且当列类型不一致时，会 `CAST` 到相同类型。
+
+EXCEPT/MINUS 语法：
+
+```sql
+SELECT ...
+EXCEPT [DISTINCT] SELECT ......
+[EXCEPT [DISTINCT] SELECT ...]
+```
+`EXCEPT` 子句用于返回多个查询结果之间的补集，即返回左侧查询中在右侧查询中不存在的数据，并对结果集去重。
+`EXCEPT` 和 `MINUS` 功能对等。
+`EXCEPT` 效果等同于 `EXCEPT DISTINCT`。不支持 `ALL` 关键字。
+每条 `SELECT` 查询返回的列数必须相同，且当列类型不一致时，会 `CAST` 到相同类型。
+
WITH 语句：
要指定公用表表达式，请使用 `WITH`
具有一个或多个逗号分隔子句的子句。每个子条款都提供一个子查询，用于生成结果集，并将名称与子查询相关联。下面的示例定义名为的 CTE `cte1` 和
`cte2` 中 `WITH` 子句，并且是指在它们的顶层 `SELECT` 下面的 `WITH` 子句：
@@ -259,6 +283,21 @@ UNION
SELECT a FROM t2 WHERE a = 11 AND B = 2 ORDER by a LIMIT 10;
```
+INTERSECT 示例
+```sql
+SELECT a FROM t1 WHERE a = 10 AND B = 1 ORDER by a LIMIT 10
+INTERSECT
+SELECT a FROM t2 WHERE a = 11 AND B = 2 ORDER by a LIMIT 10;
+```
+
+- EXCEPT 示例
+
+```sql
+SELECT a FROM t1 WHERE a = 10 AND B = 1 ORDER by a LIMIT 10
+EXCEPT
+SELECT a FROM t2 WHERE a = 11 AND B = 2 ORDER by a LIMIT 10;
+```
+
- WITH 子句示例
```sql
diff --git a/versioned_docs/version-2.0/query/query-data/select.md
b/versioned_docs/version-2.0/query/query-data/select.md
index db96b2cfe88..acd8bfa1dcb 100644
--- a/versioned_docs/version-2.0/query/query-data/select.md
+++ b/versioned_docs/version-2.0/query/query-data/select.md
@@ -111,6 +111,31 @@ UNION [ALL| DISTINCT] SELECT ......
By default, `UNION` removes duplicate rows from the result. The optional
`DISTINCT` keyword has no effect beyond the default, as it also specifies
duplicate row removal. Using the optional `ALL` keyword, no duplicate row
removal occurs, and the result includes all matching rows from all `SELECT`
statements.
+INTERSECT:
+
+```sql
+SELECT ...
+INTERSECT [DISTINCT] SELECT ......
+[INTERSECT [DISTINCT] SELECT ...]
+```
+
+`INTERSECT` is used to return the intersection of results from multiple
`SELECT` statements, with duplicate results removed.
+The effect of `INTERSECT` is equivalent to `INTERSECT DISTINCT`. The `ALL`
keyword is not supported.
+Each `SELECT` query must return the same number of columns, And when the
column types are inconsistent, they will be `CAST` to the same type.
+
+EXCEPT/MINUS:
+
+```sql
+SELECT ...
+EXCEPT [DISTINCT] SELECT ......
+[EXCEPT [DISTINCT] SELECT ...]
+```
+
+The `EXCEPT` clause is used to return the complement between the results of
multiple queries, meaning it returns the data from the left query that does not
exist in the right query, with duplicates removed.
+`EXCEPT` is functionally equivalent to `MINUS`.
+The effect of `EXCEPT` is the same as `EXCEPT DISTINCT`. The `ALL` keyword is
not supported.
+Each `SELECT` query must return the same number of columns, And when the
column types are inconsistent, they will be `CAST` to the same type.
+
WITH:
To specify a common table expression, use a `WITH` clause with one or more
comma-separated subclauses. Each subclause provides a subquery that generates a
result set and associates a name with the subquery. The following example
defines CTEs named `cte1` and `cte2` in the `WITH` clause, and refers to them
in the top-level `SELECT `following the WITH clause.
@@ -206,6 +231,22 @@ UNION
SELECT a FROM t2 WHERE a = 11 AND B = 2 ORDER by a LIMIT 10;
```
+- INTERSECT
+
+```sql
+SELECT a FROM t1 WHERE a = 10 AND B = 1 ORDER by a LIMIT 10
+INTERSECT
+SELECT a FROM t2 WHERE a = 11 AND B = 2 ORDER by a LIMIT 10;
+```
+
+- EXCEPT
+
+```sql
+SELECT a FROM t1 WHERE a = 10 AND B = 1 ORDER by a LIMIT 10
+EXCEPT
+SELECT a FROM t2 WHERE a = 11 AND B = 2 ORDER by a LIMIT 10;
+```
+
- WITH clause
```
diff --git a/versioned_docs/version-2.1/query/query-data/select.md
b/versioned_docs/version-2.1/query/query-data/select.md
index db96b2cfe88..acd8bfa1dcb 100644
--- a/versioned_docs/version-2.1/query/query-data/select.md
+++ b/versioned_docs/version-2.1/query/query-data/select.md
@@ -111,6 +111,31 @@ UNION [ALL| DISTINCT] SELECT ......
By default, `UNION` removes duplicate rows from the result. The optional
`DISTINCT` keyword has no effect beyond the default, as it also specifies
duplicate row removal. Using the optional `ALL` keyword, no duplicate row
removal occurs, and the result includes all matching rows from all `SELECT`
statements.
+INTERSECT:
+
+```sql
+SELECT ...
+INTERSECT [DISTINCT] SELECT ......
+[INTERSECT [DISTINCT] SELECT ...]
+```
+
+`INTERSECT` is used to return the intersection of results from multiple
`SELECT` statements, with duplicate results removed.
+The effect of `INTERSECT` is equivalent to `INTERSECT DISTINCT`. The `ALL`
keyword is not supported.
+Each `SELECT` query must return the same number of columns, And when the
column types are inconsistent, they will be `CAST` to the same type.
+
+EXCEPT/MINUS:
+
+```sql
+SELECT ...
+EXCEPT [DISTINCT] SELECT ......
+[EXCEPT [DISTINCT] SELECT ...]
+```
+
+The `EXCEPT` clause is used to return the complement between the results of
multiple queries, meaning it returns the data from the left query that does not
exist in the right query, with duplicates removed.
+`EXCEPT` is functionally equivalent to `MINUS`.
+The effect of `EXCEPT` is the same as `EXCEPT DISTINCT`. The `ALL` keyword is
not supported.
+Each `SELECT` query must return the same number of columns, And when the
column types are inconsistent, they will be `CAST` to the same type.
+
WITH:
To specify a common table expression, use a `WITH` clause with one or more
comma-separated subclauses. Each subclause provides a subquery that generates a
result set and associates a name with the subquery. The following example
defines CTEs named `cte1` and `cte2` in the `WITH` clause, and refers to them
in the top-level `SELECT `following the WITH clause.
@@ -206,6 +231,22 @@ UNION
SELECT a FROM t2 WHERE a = 11 AND B = 2 ORDER by a LIMIT 10;
```
+- INTERSECT
+
+```sql
+SELECT a FROM t1 WHERE a = 10 AND B = 1 ORDER by a LIMIT 10
+INTERSECT
+SELECT a FROM t2 WHERE a = 11 AND B = 2 ORDER by a LIMIT 10;
+```
+
+- EXCEPT
+
+```sql
+SELECT a FROM t1 WHERE a = 10 AND B = 1 ORDER by a LIMIT 10
+EXCEPT
+SELECT a FROM t2 WHERE a = 11 AND B = 2 ORDER by a LIMIT 10;
+```
+
- WITH clause
```
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
