0001 ---
0002 layout: global
0003 title: JOIN
0004 displayTitle: JOIN
0005 license: |
0006 Licensed to the Apache Software Foundation (ASF) under one or more
0007 contributor license agreements. See the NOTICE file distributed with
0008 this work for additional information regarding copyright ownership.
0009 The ASF licenses this file to You under the Apache License, Version 2.0
0010 (the "License"); you may not use this file except in compliance with
0011 the License. You may obtain a copy of the License at
0012
0013 http://www.apache.org/licenses/LICENSE-2.0
0014
0015 Unless required by applicable law or agreed to in writing, software
0016 distributed under the License is distributed on an "AS IS" BASIS,
0017 WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
0018 See the License for the specific language governing permissions and
0019 limitations under the License.
0020 ---
0021
0022 ### Description
0023
0024 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.
0025
0026 ### Syntax
0027
0028 ```sql
0029 relation { [ join_type ] JOIN relation [ join_criteria ] | NATURAL join_type JOIN relation }
0030 ```
0031
0032 ### Parameters
0033
0034 * **relation**
0035
0036 Specifies the relation to be joined.
0037
0038 * **join_type**
0039
0040 Specifies the join type.
0041
0042 **Syntax:**
0043
0044 `[ INNER ] | CROSS | LEFT [ OUTER ] | [ LEFT ] SEMI | RIGHT [ OUTER ] | FULL [ OUTER ] | [ LEFT ] ANTI`
0045
0046 * **join_criteria**
0047
0048 Specifies how the rows from one relation will be combined with the rows of another relation.
0049
0050 **Syntax:** `ON boolean_expression | USING ( column_name [ , ... ] )`
0051
0052 `boolean_expression`
0053
0054 Specifies an expression with a return type of boolean.
0055
0056 ### Join Types
0057
0058 #### **Inner Join**
0059
0060 The inner join is the default join in Spark SQL. It selects rows that have matching values in both relations.
0061
0062 **Syntax:**
0063
0064 `relation [ INNER ] JOIN relation [ join_criteria ]`
0065
0066 #### **Left Join**
0067
0068 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.
0069
0070 **Syntax:**
0071
0072 `relation LEFT [ OUTER ] JOIN relation [ join_criteria ]`
0073
0074 #### **Right Join**
0075
0076 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.
0077
0078 **Syntax:**
0079
0080 `relation RIGHT [ OUTER ] JOIN relation [ join_criteria ]`
0081
0082 #### **Full Join**
0083
0084 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.
0085
0086 **Syntax:**
0087
0088 `relation FULL [ OUTER ] JOIN relation [ join_criteria ]`
0089
0090 #### **Cross Join**
0091
0092 A cross join returns the Cartesian product of two relations.
0093
0094 **Syntax:**
0095
0096 `relation CROSS JOIN relation [ join_criteria ]`
0097
0098 #### **Semi Join**
0099
0100 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.
0101
0102 **Syntax:**
0103
0104 `relation [ LEFT ] SEMI JOIN relation [ join_criteria ]`
0105
0106 #### **Anti Join**
0107
0108 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.
0109
0110 **Syntax:**
0111
0112 `relation [ LEFT ] ANTI JOIN relation [ join_criteria ]`
0113
0114 ### Examples
0115
0116 ```sql
0117 -- Use employee and department tables to demonstrate different type of joins.
0118 SELECT * FROM employee;
0119 +---+-----+------+
0120 | id| name|deptno|
0121 +---+-----+------+
0122 |105|Chloe| 5|
0123 |103| Paul| 3|
0124 |101| John| 1|
0125 |102| Lisa| 2|
0126 |104| Evan| 4|
0127 |106| Amy| 6|
0128 +---+-----+------+
0129
0130 SELECT * FROM department;
0131 +------+-----------+
0132 |deptno| deptname|
0133 +------+-----------+
0134 | 3|Engineering|
0135 | 2| Sales|
0136 | 1| Marketing|
0137 +------+-----------+
0138
0139 -- Use employee and department tables to demonstrate inner join.
0140 SELECT id, name, employee.deptno, deptname
0141 FROM employee INNER JOIN department ON employee.deptno = department.deptno;
0142 +---+-----+------+-----------|
0143 | id| name|deptno| deptname|
0144 +---+-----+------+-----------|
0145 |103| Paul| 3|Engineering|
0146 |101| John| 1| Marketing|
0147 |102| Lisa| 2| Sales|
0148 +---+-----+------+-----------|
0149
0150 -- Use employee and department tables to demonstrate left join.
0151 SELECT id, name, employee.deptno, deptname
0152 FROM employee LEFT JOIN department ON employee.deptno = department.deptno;
0153 +---+-----+------+-----------|
0154 | id| name|deptno| deptname|
0155 +---+-----+------+-----------|
0156 |105|Chloe| 5| NULL|
0157 |103| Paul| 3|Engineering|
0158 |101| John| 1| Marketing|
0159 |102| Lisa| 2| Sales|
0160 |104| Evan| 4| NULL|
0161 |106| Amy| 6| NULL|
0162 +---+-----+------+-----------|
0163
0164 -- Use employee and department tables to demonstrate right join.
0165 SELECT id, name, employee.deptno, deptname
0166 FROM employee RIGHT JOIN department ON employee.deptno = department.deptno;
0167 +---+-----+------+-----------|
0168 | id| name|deptno| deptname|
0169 +---+-----+------+-----------|
0170 |103| Paul| 3|Engineering|
0171 |101| John| 1| Marketing|
0172 |102| Lisa| 2| Sales|
0173 +---+-----+------+-----------|
0174
0175 -- Use employee and department tables to demonstrate full join.
0176 SELECT id, name, employee.deptno, deptname
0177 FROM employee FULL JOIN department ON employee.deptno = department.deptno;
0178 +---+-----+------+-----------|
0179 | id| name|deptno| deptname|
0180 +---+-----+------+-----------|
0181 |101| John| 1| Marketing|
0182 |106| Amy| 6| NULL|
0183 |103| Paul| 3|Engineering|
0184 |105|Chloe| 5| NULL|
0185 |104| Evan| 4| NULL|
0186 |102| Lisa| 2| Sales|
0187 +---+-----+------+-----------|
0188
0189 -- Use employee and department tables to demonstrate cross join.
0190 SELECT id, name, employee.deptno, deptname FROM employee CROSS JOIN department;
0191 +---+-----+------+-----------|
0192 | id| name|deptno| deptname|
0193 +---+-----+------+-----------|
0194 |105|Chloe| 5|Engineering|
0195 |105|Chloe| 5| Marketing|
0196 |105|Chloe| 5| Sales|
0197 |103| Paul| 3|Engineering|
0198 |103| Paul| 3| Marketing|
0199 |103| Paul| 3| Sales|
0200 |101| John| 1|Engineering|
0201 |101| John| 1| Marketing|
0202 |101| John| 1| Sales|
0203 |102| Lisa| 2|Engineering|
0204 |102| Lisa| 2| Marketing|
0205 |102| Lisa| 2| Sales|
0206 |104| Evan| 4|Engineering|
0207 |104| Evan| 4| Marketing|
0208 |104| Evan| 4| Sales|
0209 |106| Amy| 4|Engineering|
0210 |106| Amy| 4| Marketing|
0211 |106| Amy| 4| Sales|
0212 +---+-----+------+-----------|
0213
0214 -- Use employee and department tables to demonstrate semi join.
0215 SELECT * FROM employee SEMI JOIN department ON employee.deptno = department.deptno;
0216 +---+-----+------+
0217 | id| name|deptno|
0218 +---+-----+------+
0219 |103| Paul| 3|
0220 |101| John| 1|
0221 |102| Lisa| 2|
0222 +---+-----+------+
0223
0224 -- Use employee and department tables to demonstrate anti join.
0225 SELECT * FROM employee ANTI JOIN department ON employee.deptno = department.deptno;
0226 +---+-----+------+
0227 | id| name|deptno|
0228 +---+-----+------+
0229 |105|Chloe| 5|
0230 |104| Evan| 4|
0231 |106| Amy| 6|
0232 +---+-----+------+
0233 ```
0234
0235 ### Related Statements
0236
0237 * [SELECT](sql-ref-syntax-qry-select.html)
0238 * [Hints](sql-ref-syntax-qry-select-hints.html)