This is an automated email from the ASF dual-hosted git repository. jark pushed a commit to branch master in repository https://gitbox.apache.org/repos/asf/flink.git
commit 0ee0fcabf4fe5a86b8b0212e5668f2f60b829285 Author: luoyuxia <[email protected]> AuthorDate: Mon Aug 29 15:12:58 2022 +0800 [FLINK-29025][docs] add set operation page for Hive dialect --- .../hiveDialect/Queries/set-op.md | 95 ++++++++++++++++++++++ .../hiveDialect/Queries/set-op.md | 95 ++++++++++++++++++++++ 2 files changed, 190 insertions(+) diff --git a/docs/content.zh/docs/dev/table/hiveCompatibility/hiveDialect/Queries/set-op.md b/docs/content.zh/docs/dev/table/hiveCompatibility/hiveDialect/Queries/set-op.md new file mode 100644 index 00000000000..1178844d2c3 --- /dev/null +++ b/docs/content.zh/docs/dev/table/hiveCompatibility/hiveDialect/Queries/set-op.md @@ -0,0 +1,95 @@ +--- +title: "Set Operations" +weight: 5 +type: docs +--- +<!-- +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. +--> + +# Set Operations + +Set Operation is used to combing two select into single one. +Hive dialect supports the following operations: +- UNION +- INTERSECT +- EXCEPT/MINUS + +## UNION + +### Description + +`UNION`/`UNION DISTINCT`/`UNION ALL` returns the rows that are found in either side. + +`UNION` and `UNION DISTINCT` only returns the distinct rows, while `UNION ALL` does not duplicate. + +### Syntax + +```sql +select_statement { UNION [ ALL | DISTINCT ] } select_statement [ .. ] +``` + +### Examples +```sql +SELECT x, y FROM t1 UNION DISTINCT SELECT x, y FROM t2; +SELECT x, y FROM t1 UNION SELECT x, y FROM t2; +SELECT x, y FROM t1 UNION ALL SELECT x, y FROM t2; +``` + +## INTERSECT + +### Description + +`INTERSECT`/`INTERSECT DISTINCT`/`INTERSECT ALL` returns the rows that are found in both side. + +`INTERSECT`/`INTERSECT DISTINCT` only returns the distinct rows, while `INTERSECT ALL` does not duplicate. + +### Syntax + +```sql +select_statement { INTERSECT [ ALL | DISTINCT ] } select_statement [ .. ] +``` + +### Examples +```sql +SELECT x, y FROM t1 INTERSECT DISTINCT SELECT x, y FROM t2; +SELECT x, y FROM t1 INTERSECT SELECT x, y FROM t2; +SELECT x, y FROM t1 INTERSECT ALL SELECT x, y FROM t2; +``` + +## EXCEPT/MINUS + +### Description + +`EXCEPT`/`EXCEPT DISTINCT`/`EXCEPT ALL` returns the rows that are found in left side but not in right side. + +`EXCEPT`/`EXCEPT DISTINCT` only returns the distinct rows, while `EXCEPT ALL` does not duplicate. + +`MINUS` is synonym for `EXCEPT`. + +### Syntax + +```sql +select_statement { EXCEPT [ ALL | DISTINCT ] } select_statement [ .. ] +``` + +### Examples + +```sql +SELECT x, y FROM t1 EXCEPT DISTINCT SELECT x, y FROM t2; +SELECT x, y FROM t1 EXCEPT SELECT x, y FROM t2; +SELECT x, y FROM t1 EXCEPT ALL SELECT x, y FROM t2; +``` diff --git a/docs/content/docs/dev/table/hiveCompatibility/hiveDialect/Queries/set-op.md b/docs/content/docs/dev/table/hiveCompatibility/hiveDialect/Queries/set-op.md new file mode 100644 index 00000000000..1178844d2c3 --- /dev/null +++ b/docs/content/docs/dev/table/hiveCompatibility/hiveDialect/Queries/set-op.md @@ -0,0 +1,95 @@ +--- +title: "Set Operations" +weight: 5 +type: docs +--- +<!-- +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. +--> + +# Set Operations + +Set Operation is used to combing two select into single one. +Hive dialect supports the following operations: +- UNION +- INTERSECT +- EXCEPT/MINUS + +## UNION + +### Description + +`UNION`/`UNION DISTINCT`/`UNION ALL` returns the rows that are found in either side. + +`UNION` and `UNION DISTINCT` only returns the distinct rows, while `UNION ALL` does not duplicate. + +### Syntax + +```sql +select_statement { UNION [ ALL | DISTINCT ] } select_statement [ .. ] +``` + +### Examples +```sql +SELECT x, y FROM t1 UNION DISTINCT SELECT x, y FROM t2; +SELECT x, y FROM t1 UNION SELECT x, y FROM t2; +SELECT x, y FROM t1 UNION ALL SELECT x, y FROM t2; +``` + +## INTERSECT + +### Description + +`INTERSECT`/`INTERSECT DISTINCT`/`INTERSECT ALL` returns the rows that are found in both side. + +`INTERSECT`/`INTERSECT DISTINCT` only returns the distinct rows, while `INTERSECT ALL` does not duplicate. + +### Syntax + +```sql +select_statement { INTERSECT [ ALL | DISTINCT ] } select_statement [ .. ] +``` + +### Examples +```sql +SELECT x, y FROM t1 INTERSECT DISTINCT SELECT x, y FROM t2; +SELECT x, y FROM t1 INTERSECT SELECT x, y FROM t2; +SELECT x, y FROM t1 INTERSECT ALL SELECT x, y FROM t2; +``` + +## EXCEPT/MINUS + +### Description + +`EXCEPT`/`EXCEPT DISTINCT`/`EXCEPT ALL` returns the rows that are found in left side but not in right side. + +`EXCEPT`/`EXCEPT DISTINCT` only returns the distinct rows, while `EXCEPT ALL` does not duplicate. + +`MINUS` is synonym for `EXCEPT`. + +### Syntax + +```sql +select_statement { EXCEPT [ ALL | DISTINCT ] } select_statement [ .. ] +``` + +### Examples + +```sql +SELECT x, y FROM t1 EXCEPT DISTINCT SELECT x, y FROM t2; +SELECT x, y FROM t1 EXCEPT SELECT x, y FROM t2; +SELECT x, y FROM t1 EXCEPT ALL SELECT x, y FROM t2; +```
