JOIN

适用于： Databricks SQL Databricks Runtime

根据联接条件合并两个表引用中的行。

语法

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 [, ...] ) }

参数

• left_table_reference

  联接左侧的表引用。

• right_table_reference

  联接右侧的表引用。

• join_type

  联接类型。

  • [ INNER ]

    返回在两个表引用中具有匹配值的行。 默认联接类型。

  • LEFT [ OUTER ]

    返回左侧表引用中的所有值和右侧表引用中的匹配值，如果没有匹配项，则追加 NULL。 它也称为左外部联接。

  • RIGHT [ OUTER ]

    返回右侧表引用中的所有值和左侧表引用中的匹配值，如果没有匹配项，则追加 NULL。 它也称为右外部联接。

  • FULL [OUTER]

    返回两个关系中的所有值，在没有匹配值的那一侧追加 NULL 值。 它也称为完全外部联接。

  • [ LEFT ] SEMI

    返回与右侧表引用有匹配值的左侧表引用的值。 它也称为左半联接。

  • [ LEFT ] ANTI

    返回与右侧表引用没有匹配值的左侧表引用的值。 它也称为左反联接。

• CROSS JOIN

  返回两个关系的笛卡尔乘积。

• NATURAL

  指定对两个关系中的行与具有匹配名称的所有列进行隐式相对性匹配。

• join_criteria

  指定一个表引用中的行如何与另一个表引用中的行合并。

  • ON boolean_expression

    返回类型为 BOOLEAN 的表达式，指定如何匹配两个关系中的行。 如果结果为 true，则将行视为匹配。

  • USING ( column_name [, …] )

    通过比较列列表 column_name 的相等性（必须存在于两个关系中）来匹配行。

• table_alias

  临时名称和可选的列标识符列表。

备注

指定 USING 或 NATURAL 时，SELECT * 将首先只显示用于匹配的每个列的一个匹配项，然后是左侧的列，再是右联接表（不包括所联接的列）。

SELECT * FROM left JOIN right USING (a, b)

等效于

SELECT left.a, left.b,
       left.* EXCEPT(a, b),
       right.* EXCEPT(a, b)
  FROM left JOIN right ON left.a = right.a AND left.b = right.b

如果省略 join_criteria，则任何 join_type 的语义将变成 CROSS JOIN 的语义。

示例

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

相关文章

• SELECT