This is an automated email from the ASF dual-hosted git repository.
wenchen pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/master by this push:
new ec894138344 [SPARK-35208][SQL][DOCS] Add docs for LATERAL subqueries
ec894138344 is described below
commit ec894138344d78f4e703aee0b989eff230d1537a
Author: huaxingao <[email protected]>
AuthorDate: Tue Jul 12 16:32:07 2022 +0800
[SPARK-35208][SQL][DOCS] Add docs for LATERAL subqueries
### What changes were proposed in this pull request?
Add docs for LATERAL subqueries
### Why are the changes needed?
Make SQL Reference complete.
### Does this PR introduce _any_ user-facing change?
Yes
<img width="701" alt="Screen Shot 2022-07-08 at 8 02 36 AM"
src="https://user-images.githubusercontent.com/13592258/178018972-52bc0e6b-a01b-4814-8820-ea1ea4f8432f.png";>
### How was this patch tested?
Manually build the doc and check.
Closes #37080 from huaxingao/lateral.
Authored-by: huaxingao <[email protected]>
Signed-off-by: Wenchen Fan <[email protected]>
---
docs/sql-ref-syntax-qry-select-join.md | 3 +-
docs/sql-ref-syntax-qry-select-lateral-subquery.md | 87 ++++++++++++++++++++++
docs/sql-ref-syntax-qry-select.md | 3 +-
docs/sql-ref-syntax.md | 1 +
4 files changed, 92 insertions(+), 2 deletions(-)
diff --git a/docs/sql-ref-syntax-qry-select-join.md
b/docs/sql-ref-syntax-qry-select-join.md
index 09b0efd7b57..698884dc28b 100644
--- a/docs/sql-ref-syntax-qry-select-join.md
+++ b/docs/sql-ref-syntax-qry-select-join.md
@@ -26,7 +26,7 @@ A SQL join is used to combine rows from two relations based
on join criteria. Th
### Syntax
```sql
-relation { [ join_type ] JOIN relation [ join_criteria ] | NATURAL join_type
JOIN relation }
+relation { [ join_type ] JOIN [ LATERAL ] relation [ join_criteria ] | NATURAL
join_type JOIN [ LATERAL ] relation }
```
### Parameters
@@ -236,3 +236,4 @@ SELECT * FROM employee ANTI JOIN department ON
employee.deptno = department.dept
* [SELECT](sql-ref-syntax-qry-select.html)
* [Hints](sql-ref-syntax-qry-select-hints.html)
+* [LATERAL Subquery](sql-ref-syntax-qry-select-lateral-subquery.html)
diff --git a/docs/sql-ref-syntax-qry-select-lateral-subquery.md
b/docs/sql-ref-syntax-qry-select-lateral-subquery.md
new file mode 100644
index 00000000000..54961b690d5
--- /dev/null
+++ b/docs/sql-ref-syntax-qry-select-lateral-subquery.md
@@ -0,0 +1,87 @@
+---
+layout: global
+title: LATERAL SUBQUERY
+displayTitle: LATERAL SUBQUERY
+license: |
+ 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.
+---
+
+### Description
+
+`LATERAL SUBQUERY` is a subquery that is preceded by the keyword `LATERAL`. It
provides a way to reference columns in the preceding `FROM` clause.
+Without the `LATERAL` keyword, subqueries can only refer to columns in the
outer query, but not in the `FROM` clause. `LATERAL SUBQUERY` makes the
complicated
+queries simpler and more efficient.
+
+### Syntax
+
+```sql
+[ LATERAL ] primary_relation [ join_relation ]
+```
+
+### Parameters
+
+* **primary_relation**
+
+ Specifies the primary relation. It can be one of the following:
+ * Table relation
+ * Aliased query
+
+ Syntax: `( query ) [ [ AS ] alias ]`
+ * Aliased relation
+
+ Syntax: `( relation ) [ [ AS ] alias ]`
+ * [Table-value function](sql-ref-syntax-qry-select-tvf.html)
+ * [Inline table](sql-ref-syntax-qry-select-inline-table.html)
+
+
+* **join_relation**
+
+ Specifies a [Join relation](sql-ref-syntax-qry-select-join.html).
+
+### Examples
+
+```sql
+CREATE TABLE t1 (c1 INT, c2 INT);
+INSERT INTO t1 VALUES (0, 1), (1, 2);
+
+CREATE TABLE t2 (c1 INT, c2 INT);
+INSERT INTO t2 VALUES (0, 2), (0, 3);
+
+SELECT * FROM t1,
+ LATERAL (SELECT * FROM t2 WHERE t1.c1 = t2.c1);
++--------+-------+--------+-------+
+| t1.c1 | t1.c2 | t2.c1 | t2.c2 |
++-------+--------+--------+-------+
+| 0 | 1 | 0 | 3 |
+| 0 | 1 | 0 | 2 |
++-------+--------+--------+-------+
+
+SELECT a, b, c FROM t1,
+ LATERAL (SELECT c1 + c2 AS a),
+ LATERAL (SELECT c1 - c2 AS b),
+ LATERAL (SELECT a * b AS c);
++--------+-------+--------+
+| a | b | c |
++-------+--------+--------+
+| 3 | -1 | -3 |
+| 1 | -1 | -1 |
++-------+--------+--------+
+```
+
+### Related Statements
+
+* [SELECT Main](sql-ref-syntax-qry-select.html)
+* [JOIN](sql-ref-syntax-qry-select-join.html)
diff --git a/docs/sql-ref-syntax-qry-select.md
b/docs/sql-ref-syntax-qry-select.md
index 500eda162bf..b4fa4b51fdc 100644
--- a/docs/sql-ref-syntax-qry-select.md
+++ b/docs/sql-ref-syntax-qry-select.md
@@ -84,7 +84,7 @@ SELECT [ hints , ... ] [ ALL | DISTINCT ] { [ [
named_expression | regex_column_
* [Join relation](sql-ref-syntax-qry-select-join.html)
* [Table-value function](sql-ref-syntax-qry-select-tvf.html)
* [Inline table](sql-ref-syntax-qry-select-inline-table.html)
- * Subquery
+ * [ [LATERAL](sql-ref-syntax-qry-select-lateral-subquery.html) ] (
Subquery )
* [File](sql-ref-syntax-qry-select-file.html)
* **PIVOT**
@@ -192,3 +192,4 @@ SELECT [ hints , ... ] [ ALL | DISTINCT ] { [ [
named_expression | regex_column_
* [PIVOT Clause](sql-ref-syntax-qry-select-pivot.html)
* [LATERAL VIEW Clause](sql-ref-syntax-qry-select-lateral-view.html)
* [TRANSFORM Clause](sql-ref-syntax-qry-select-transform.html)
+* [LATERAL Subquery](sql-ref-syntax-qry-select-lateral-subquery.html)
diff --git a/docs/sql-ref-syntax.md b/docs/sql-ref-syntax.md
index 3defd418d3a..b5f35f6433d 100644
--- a/docs/sql-ref-syntax.md
+++ b/docs/sql-ref-syntax.md
@@ -80,6 +80,7 @@ ability to generate logical and physical plan for a given
query using
* [CASE Clause](sql-ref-syntax-qry-select-case.html)
* [PIVOT Clause](sql-ref-syntax-qry-select-pivot.html)
* [LATERAL VIEW Clause](sql-ref-syntax-qry-select-lateral-view.html)
+ * [LATERAL SUBQUERY](sql-ref-syntax-qry-select-lateral-subquery.html)
* [TRANSFORM Clause](sql-ref-syntax-qry-select-transform.html)
* [EXPLAIN](sql-ref-syntax-qry-explain.html)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
![https://user-images.githubusercontent.com/13592258/178018972-52bc0e6b-a01b-4814-8820-ea1ea4f8432f.png"](https://user-images.githubusercontent.com/13592258/178018972-52bc0e6b-a01b-4814-8820-ea1ea4f8432f.png"){kind=link}