JOIN
Applies to: 
Databricks SQL Databricks Runtime
Combines the rows from two table references based on join criteria.
Syntax
left_table_reference { [ join_type ] JOIN right_table_reference [ join_criteria ] |
NATURAL join_type JOIN right_table_reference |
CROSS JOIN right_table_reference }
join_type
{ [ INNER ] |
LEFT [ OUTER ] |
[ LEFT ] SEMI |
RIGHT [ OUTER ] |
FULL [ OUTER ] |
[ LEFT ] ANTI |
CROSS }
join_criteria
{ ON boolean_expression |
USING ( column_name [, ...] ) }
Parameters
-
The table reference on the left side of the join.
-
The table reference on the right side of the join.
join_type
The join-type.
[ INNER ]
Returns the rows that have matching values in both table references. The default join-type.
LEFT [ OUTER ]
Returns all values from the left table reference and the matched values from the right table reference, or appends
NULLif there is no match. It is also referred to as a left outer join.RIGHT [ OUTER ]
Returns all values from the right table reference and the matched values from the left table reference, or appends
NULLif there is no match. It is also referred to as a right outer join.FULL [OUTER]
Returns all values from both relations, appending
NULLvalues on the side that does not have a match. It is also referred to as a full outer join.[ LEFT ] SEMI
Returns values from the left side of the table reference that has a match with the right. It is also referred to as a left semi join.
[ LEFT ] ANTI
Returns the values from the left table reference that have no match with the right table reference. It is also referred to as a left anti join.
CROSS JOIN
Returns the Cartesian product of two relations.
NATURAL
Specifies that the rows from the two relations will implicitly be matched on equality for all columns with matching names.
join_criteria
Optionally specifies how the rows from one table reference is combined with the rows of another table reference.
Warning
If you omit the
join_criteriathe semantic of anyjoin_typebecomes that of aCROSS JOIN.ON boolean_expression
An expression with a return type of BOOLEAN which specifies how rows from the two relations are matched. If the result is true the rows are considered a match.
USING ( column_name [, …] )
Matches the rows by comparing equality for list of columns
column_namewhich must exist in both relations.
-
A temporary name with an optional column identifier list.
Notes
When you specify USING or NATURAL, SELECT * will only show one occurrence for each of the columns used to match first, followed by the columns of the left, then right join tables excluding the columns joined upon.
SELECT * FROM first JOIN second USING (a, b)
is equivalent to
SELECT first.a, first.b,
first.* EXCEPT(a, b),
second.* EXCEPT(a, b)
FROM first JOIN second ON first.a = second.a AND first.b = second.b
Examples
-- Use employee and department tables to demonstrate different type of joins.
> CREATE TEMP VIEW employee(id, name, deptno) AS
VALUES(105, 'Chloe', 5),
(103, 'Paul' , 3),
(101, 'John' , 1),
(102, 'Lisa' , 2),
(104, 'Evan' , 4),
(106, 'Amy' , 6);
> CREATE TEMP VIEW department(deptno, deptname) AS
VALUES(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;
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;
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;
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;
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;
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;
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;
105 Chloe 5
104 Evan 4
106 Amy 6
-- Use employee and department tables to demonstrate lateral inner join.
> SELECT id, name, deptno, deptname
FROM employee
JOIN LATERAL (SELECT deptname
FROM department
WHERE employee.deptno = department.deptno);
103 Paul 3 Engineering
101 John 1 Marketing
102 Lisa 2 Sales
-- Use employee and department tables to demonstrate lateral left join.
> SELECT id, name, deptno, deptname
FROM employee
LEFT JOIN LATERAL (SELECT deptname
FROM department
WHERE employee.deptno = department.deptno);
105 Chloe 5 NULL
103 Paul 3 Engineering
101 John 1 Marketing
102 Lisa 2 Sales
104 Evan 4 NULL
106 Amy 6 NULL