JOIN

Combines rows from two relations based on join criteria.

Syntax

relation { [ join_type ] JOIN relation join_criteria |
           NATURAL join_type JOIN relation |
           CROSS JOIN relation }

relation
 { table_name [ table_alias ] |
   view_name [ table_alias ] |
   [ LATERAL ] ( query ) [ table_alias ] |
   ( JOIN clause ) [ table_alias ] |
   VALUES clause |
   table_valued_function [ table_alias ] }

join_type
  { [ INNER ] |
    LEFT [ OUTER ] |
    [ LEFT ] SEMI |
    RIGHT [ OUTER ] |
    FULL [ OUTER ] |
    [ LEFT ] ANTI |
    CROSS }

join_criteria
  { ON boolean_expression |
    USING ( column_name [, ...] ) }

Parameters

  • relation

    The relations to be joined.

    • table_name

      A reference to a table, view, or common table expression (CTE).

    • view_name

      A reference to a view, or common table expression (CTE).

    • [ LATERAL ] ( query )

      A nested query.

      LATERAL

      Preview

      This feature is in Public Preview.

      Note

      Available in Databricks Runtime 9.0 and above.

      Whether the nested query can reference columns in preceding from_items.

    • ( JOIN clause )

      A nested invocation of a JOIN.

    • VALUES clause

      A clause that produces an inline temporary table.

    • Table-valued function (TVF)

      An invocation of a table function.

  • join_type

    The join type.

    • [ INNER ]

      Returns rows that have matching values in both relations. The default join.

    • LEFT [ OUTER ]

      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.

    • RIGHT [ OUTER ]

      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.

    • FULL [OUTER]

      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.

    • [ LEFT ] SEMI

      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.

    • [ LEFT ] ANTI

      Returns values from the left relation that has no match with the right. It is also referred to as a left anti join.

  • CROSS JOIN

    Returns the Cartesian product of two relations.

  • NATURAL

    Rows from the two relations are implicitly matched on equality for all columns with matching names.

  • join_criteria

    How the rows from one relation are combined with the rows of another relation.

    • ON boolean_expression

      An expression with a return type of BOOLEAN that specifies how rows from the two relations are matched. If the result is true, the rows are considered a match.

    • USING ( column_name [, …] )

      Matches rows by comparing equality for list of columns column_name which must exist in both relations.

      USING (c1, c2) is a synonym for ON rel1.c1 = rel2.c1 AND rel1.c2 = rel2.c2.

  • table_alias

    A temporary name with an optional column identifier list.

Notes

When you specify USING or NATURAL, a SELECT * will show only one occurrence for each of the columns used to match.

If you omit join_criteria, the semantic of any join_type becomes that of a CROSS JOIN.

You can use LATERAL with INNER, LEFT [OUTER], and CROSS join.

Examples

-- 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|
+---+-----+------+

-- 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);
+---+----+------+-----------+
|id |name|deptno|deptname   |
+---+----+------+-----------+
|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);
+---+-----+------+-----------+
|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       |
+---+-----+------+-----------+