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

• left_table_reference

  The table reference on the left side of the join.

• right_table_reference

  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 NULL if 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 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 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_criteria the semantic of any join_type becomes that of a CROSS 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_name which must exist in both relations.

• table_alias

  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

Related articles

• SELECT