This is an automated email from the ASF dual-hosted git repository.
srowen pushed a commit to branch branch-3.0
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/branch-3.0 by this push:
new 888b069 [SPARK-31348][SQL][DOCS] Document Join in SQL Reference
888b069 is described below
commit 888b06925f60eabc844f6463cc0d333d1b82b9da
Author: Huaxin Gao <huax...@us.ibm.com>
AuthorDate: Sun Apr 12 13:57:54 2020 -0500
[SPARK-31348][SQL][DOCS] Document Join in SQL Reference
### What changes were proposed in this pull request?
Document join in SQL Reference.
### Why are the changes needed?
To make SQL Reference complete.
### Does this PR introduce any user-facing change?
Yes
<img width="1050" alt="Screen Shot 2020-04-05 at 8 46 47 PM"
src="https://user-images.githubusercontent.com/13592258/78521722-ab7efe80-777f-11ea-90f5-1fac09282721.png";>
<img width="1049" alt="Screen Shot 2020-04-05 at 8 47 20 PM"
src="https://user-images.githubusercontent.com/13592258/78521724-ade15880-777f-11ea-9238-183d999ed918.png";>
<img width="1049" alt="Screen Shot 2020-04-05 at 8 47 41 PM"
src="https://user-images.githubusercontent.com/13592258/78521726-b043b280-777f-11ea-996f-a8e86d453c01.png";>
<img width="1049" alt="Screen Shot 2020-04-05 at 8 48 11 PM"
src="https://user-images.githubusercontent.com/13592258/78521731-b3d73980-777f-11ea-85c8-c24798ef41ac.png";>
<img width="1049" alt="Screen Shot 2020-04-05 at 8 48 33 PM"
src="https://user-images.githubusercontent.com/13592258/78521734-b5a0fd00-777f-11ea-8b2c-96af30f3bf49.png";>
### How was this patch tested?
Manually build and check.
Closes #28121 from huaxingao/join.
Authored-by: Huaxin Gao <huax...@us.ibm.com>
Signed-off-by: Sean Owen <sro...@gmail.com>
(cherry picked from commit fda910d4e2de832c36e97a404ad811022e5a5d52)
Signed-off-by: Sean Owen <sro...@gmail.com>
---
docs/_data/menu-sql.yaml | 2 +
docs/sql-ref-syntax-qry-select-hints.md | 4 +
docs/sql-ref-syntax-qry-select-join.md | 239 +++++++++++++++++++++++++++++++-
docs/sql-ref-syntax-qry-select.md | 1 +
4 files changed, 245 insertions(+), 1 deletion(-)
diff --git a/docs/_data/menu-sql.yaml b/docs/_data/menu-sql.yaml
index 225450b..75efb30 100644
--- a/docs/_data/menu-sql.yaml
+++ b/docs/_data/menu-sql.yaml
@@ -154,6 +154,8 @@
url: sql-ref-syntax-qry-select-distribute-by.html
- text: LIMIT Clause
url: sql-ref-syntax-qry-select-limit.html
+ - text: JOIN
+ url: sql-ref-syntax-qry-select-join.html
- text: Join Hints
url: sql-ref-syntax-qry-select-hints.html
- text: Set Operators
diff --git a/docs/sql-ref-syntax-qry-select-hints.md
b/docs/sql-ref-syntax-qry-select-hints.md
index 4713ea5..688ba10 100644
--- a/docs/sql-ref-syntax-qry-select-hints.md
+++ b/docs/sql-ref-syntax-qry-select-hints.md
@@ -81,3 +81,7 @@ SELECT /*+ SHUFFLE_REPLICATE_NL(t1) */ * FROM t1 INNER JOIN
t2 ON t1.key = t2.ke
SELECT /*+ BROADCAST(t1) */ /*+ MERGE(t1, t2) */ * FROM t1 INNER JOIN t2 ON
t1.key = t2.key;
{% endhighlight %}
+
+### Related Statements
+- [JOIN](sql-ref-syntax-qry-select-join.html)
+- [SELECT](sql-ref-syntax-qry-select.html)
diff --git a/docs/sql-ref-syntax-qry-select-join.md
b/docs/sql-ref-syntax-qry-select-join.md
index ee66dbd..4759b12 100644
--- a/docs/sql-ref-syntax-qry-select-join.md
+++ b/docs/sql-ref-syntax-qry-select-join.md
@@ -18,5 +18,242 @@ license: |
See the License for the specific language governing permissions and
limitations under the License.
---
+### Description
-**This page is under construction**
+A SQL join is used to combine rows from two relations based on join criteria.
The following section describes the overall join syntax and the sub-sections
cover different types of joins along with examples.
+
+### Syntax
+
+{% highlight sql %}
+relation { [ join_type ] JOIN relation [ join_criteria ] | NATURAL join_type
JOIN relation }
+{% endhighlight %}
+
+### Parameters
+
+<dl>
+ <dt><code><em>relation</em></code></dt>
+ <dd>
+ Specifies the relation to be joined.
+ </dd>
+ <dt><code><em>join_type</em></code></dt>
+ <dd>
+ Specifies the join type.<br><br>
+ <b>Syntax:</b><br>
+ <code>
+ [ INNER ]
+ | CROSS
+ | LEFT [ OUTER ]
+ | [ LEFT ] SEMI
+ | RIGHT [ OUTER ]
+ | FULL [ OUTER ]
+ | [ LEFT ] ANTI
+ </code>
+ </dd>
+ <dt><code><em>join_criteria</em></code></dt>
+ <dd>
+ Specifies how the rows from one relation will be combined with the rows of
another relation.<br><br>
+ <b>Syntax:</b>
+ <code>
+ ON boolean_expression | USING ( column_name [ , column_name ... ] )
+ </code> <br><br>
+ <code>boolean_expression</code><br>
+ Specifies an expression with a return type of boolean.
+ </dd>
+</dl>
+
+### Join Types
+
+#### <b>Inner Join</b>
+
+<dd>
+The inner join is the default join in Spark SQL. It selects rows that have
matching values in both relations.<br><br>
+ <b>Syntax:</b><br>
+ <code>
+ relation [ INNER ] JOIN relation [ join_criteria ]
+ </code>
+</dd>
+
+#### <b>Left Join </b>
+
+<dd>
+A left join returns all values from the left relation and the matched values
from the right relation, or appends NULL if there is no match. It is also
referred to as a left outer join.<br><br>
+ <b>Syntax:</b><br>
+ <code>
+ relation LEFT [ OUTER ] JOIN relation [ join_criteria ]
+ </code>
+</dd>
+
+#### <b>Right Join </b>
+<dd>
+A right join returns all values from the right relation and the matched values
from the left relation, or appends NULL if there is no match. It is also
referred to as a right outer join.<br><br>
+ <b>Syntax:</b><br>
+ <code>
+ relation RIGHT [ OUTER ] JOIN relation [ join_criteria ]
+ </code>
+</dd>
+
+#### <b>Full Join </b>
+
+<dd>
+A full join returns all values from both relations, appending NULL values on
the side that does not have a match. It is also referred to as a full outer
join.<br><br>
+ <b>Syntax:</b><br>
+ <code>
+ relation FULL [ OUTER ] JOIN relation [ join_criteria ]
+ </code>
+</dd>
+
+#### <b>Cross Join </b>
+
+<dd>
+A cross join returns the Cartesian product of two relations.<br><br>
+ <b>Syntax:</b><br>
+ <code>
+ relation CROSS JOIN relation [ join_criteria ]
+ </code>
+</dd>
+
+#### <b>Semi Join </b>
+
+<dd>
+A semi join returns values from the left side of the relation that has a match
with the right. It is also referred to as a left semi join.<br><br>
+ <b>Syntax:</b><br>
+ <code>
+ relation [ LEFT ] SEMI JOIN relation [ join_criteria ]
+ </code>
+</dd>
+
+#### <b>Anti Join </b>
+
+<dd>
+An anti join returns values from the left relation that has no match with the
right. It is also referred to as a left anti join.<br><br>
+ <b>Syntax:</b><br>
+ <code>
+ relation [ LEFT ] ANTI JOIN relation [ join_criteria ]
+ </code>
+</dd>
+
+### Examples
+
+{% highlight sql %}
+-- Use employee and department tables to demonstrate different type of joins.
+SELECT * FROM employee;
+
+ +---+-----+------+
+ | id| name|deptno|
+ +---+-----+------+
+ |105|Chloe| 5|
+ |103| Paul| 3|
+ |101| John| 1|
+ |102| Lisa| 2|
+ |104| Evan| 4|
+ |106| Amy| 6|
+ +---+-----+------+
+
+SELECT * FROM department;
+ +------+-----------+
+ |deptno| deptname|
+ +------+-----------+
+ | 3|Engineering|
+ | 2| Sales|
+ | 1| Marketing|
+ +------+-----------+
+
+-- Use employee and department tables to demonstrate inner join.
+SELECT id, name, employee.deptno, deptname
+ FROM employee INNER JOIN department ON employee.deptno = department.deptno;
+ +---+-----+------+-----------|
+ | id| name|deptno| deptname|
+ +---+-----+------+-----------|
+ |103| Paul| 3|Engineering|
+ |101| John| 1| Marketing|
+ |102| Lisa| 2| Sales|
+ +---+-----+------+-----------|
+
+-- Use employee and department tables to demonstrate left join.
+SELECT id, name, employee.deptno, deptname
+ FROM employee LEFT JOIN department ON employee.deptno = department.deptno;
+ +---+-----+------+-----------|
+ | id| name|deptno| deptname|
+ +---+-----+------+-----------|
+ |105|Chloe| 5| NULL|
+ |103| Paul| 3|Engineering|
+ |101| John| 1| Marketing|
+ |102| Lisa| 2| Sales|
+ |104| Evan| 4| NULL|
+ |106| Amy| 6| NULL|
+ +---+-----+------+-----------|
+
+-- Use employee and department tables to demonstrate right join.
+SELECT id, name, employee.deptno, deptname
+ FROM employee RIGHT JOIN department ON employee.deptno = department.deptno;
+ +---+-----+------+-----------|
+ | id| name|deptno| deptname|
+ +---+-----+------+-----------|
+ |103| Paul| 3|Engineering|
+ |101| John| 1| Marketing|
+ |102| Lisa| 2| Sales|
+ +---+-----+------+-----------|
+
+-- Use employee and department tables to demonstrate full join.
+SELECT id, name, employee.deptno, deptname
+ FROM employee FULL JOIN department ON employee.deptno = department.deptno;
+ +---+-----+------+-----------|
+ | id| name|deptno| deptname|
+ +---+-----+------+-----------|
+ |101| John| 1| Marketing|
+ |106| Amy| 6| NULL|
+ |103| Paul| 3|Engineering|
+ |105|Chloe| 5| NULL|
+ |104| Evan| 4| NULL|
+ |102| Lisa| 2| Sales|
+ +---+-----+------+-----------|
+
+-- Use employee and department tables to demonstrate cross join.
+SELECT id, name, employee.deptno, deptname FROM employee CROSS JOIN department;
+ +---+-----+------+-----------|
+ | id| name|deptno| deptname|
+ +---+-----+------+-----------|
+ |105|Chloe| 5|Engineering|
+ |105|Chloe| 5| Marketing|
+ |105|Chloe| 5| Sales|
+ |103| Paul| 3|Engineering|
+ |103| Paul| 3| Marketing|
+ |103| Paul| 3| Sales|
+ |101| John| 1|Engineering|
+ |101| John| 1| Marketing|
+ |101| John| 1| Sales|
+ |102| Lisa| 2|Engineering|
+ |102| Lisa| 2| Marketing|
+ |102| Lisa| 2| Sales|
+ |104| Evan| 4|Engineering|
+ |104| Evan| 4| Marketing|
+ |104| Evan| 4| Sales|
+ |106| Amy| 4|Engineering|
+ |106| Amy| 4| Marketing|
+ |106| Amy| 4| Sales|
+ +---+-----+------+-----------|
+
+-- Use employee and department tables to demonstrate semi join.
+SELECT * FROM employee SEMI JOIN department ON employee.deptno =
department.deptno;
+ +---+-----+------+
+ | id| name|deptno|
+ +---+-----+------+
+ |103| Paul| 3|
+ |101| John| 1|
+ |102| Lisa| 2|
+ +---+-----+------+
+
+-- Use employee and department tables to demonstrate anti join.
+SELECT * FROM employee ANTI JOIN department ON employee.deptno =
department.deptno;
+ +---+-----+------+
+ | id| name|deptno|
+ +---+-----+------+
+ |105|Chloe| 5|
+ |104| Evan| 4|
+ |106| Amy| 6|
+ +---+-----+------+
+{% endhighlight %}
+
+### Related Statement
+ * [SELECT](sql-ref-syntax-qry-select.html)
+ * [Join Hints](sql-ref-syntax-qry-select-hints.html)
diff --git a/docs/sql-ref-syntax-qry-select.md
b/docs/sql-ref-syntax-qry-select.md
index 17c1411..f1585df 100644
--- a/docs/sql-ref-syntax-qry-select.md
+++ b/docs/sql-ref-syntax-qry-select.md
@@ -151,4 +151,5 @@ SELECT [ hints , ... ] [ ALL | DISTINCT ] {
named_expression [ , ... ] }
- [DISTRIBUTE BY Clause](sql-ref-syntax-qry-select-distribute-by.html)
- [LIMIT Clause](sql-ref-syntax-qry-select-limit.html)
- [TABLESAMPLE](sql-ref-syntax-qry-sampling.html)
+- [JOIN](sql-ref-syntax-qry-select-join.html)
- [SET Operators](sql-ref-syntax-qry-select-setops.html)
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@spark.apache.org
For additional commands, e-mail: commits-h...@spark.apache.org
![https://user-images.githubusercontent.com/13592258/78521722-ab7efe80-777f-11ea-90f5-1fac09282721.png"](https://user-images.githubusercontent.com/13592258/78521722-ab7efe80-777f-11ea-90f5-1fac09282721.png"){kind=link}
![https://user-images.githubusercontent.com/13592258/78521724-ade15880-777f-11ea-9238-183d999ed918.png"](https://user-images.githubusercontent.com/13592258/78521724-ade15880-777f-11ea-9238-183d999ed918.png"){kind=link}
![https://user-images.githubusercontent.com/13592258/78521726-b043b280-777f-11ea-996f-a8e86d453c01.png"](https://user-images.githubusercontent.com/13592258/78521726-b043b280-777f-11ea-996f-a8e86d453c01.png"){kind=link}
![https://user-images.githubusercontent.com/13592258/78521731-b3d73980-777f-11ea-85c8-c24798ef41ac.png"](https://user-images.githubusercontent.com/13592258/78521731-b3d73980-777f-11ea-85c8-c24798ef41ac.png"){kind=link}
![https://user-images.githubusercontent.com/13592258/78521734-b5a0fd00-777f-11ea-8b2c-96af30f3bf49.png"](https://user-images.githubusercontent.com/13592258/78521734-b5a0fd00-777f-11ea-8b2c-96af30f3bf49.png"){kind=link}