JOIN

適用於：Databricks SQL Databricks Runtime

根據聯結準則，結合上述 left_table 參考 的數據列與 right_table 參考。

語法

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

參數

• right_table_reference

  聯結右側的數據表參考。

• join_type

  聯結類型。

  • [ INNER ]

    傳回在這兩個數據表參考中具有相符值的數據列。 默認聯結類型。

  • LEFT [ OUTER ]

    從左側資料表參照傳回所有值，並傳回來自右側資料表參照的相符值；若無相符值，則會附加 NULL。 也稱為 左外部聯接。

  • RIGHT [ OUTER ]

    從右數據表參考傳回所有值，以及來自左數據表參考的相符值，如果沒有任何相符值，則會填補 NULL。 也稱為右外部聯接 。

  • FULL [OUTER]

    傳回這兩個關聯的所有值，並在沒有匹配的那一邊附加 NULL 值。 也稱為 完整外部聯結。

  • [ LEFT ]半

    從表格左側參考中傳回與右側相符的值。 也稱為左方半聯結 。

  • [ LEFT ]反

    從左數據表參考傳回與右數據表參考沒有相符的值。 也稱為 左反聯結。

• CROSS JOIN

  傳回兩個關聯性之笛卡兒乘積。

• NATURAL

  指定兩個關聯中的數據行，將依據所有名稱相符的數據列的相等性隱含地進行比對。

• join_criteria

  選擇性地指定如何結合某個數據表參考的數據列與另一個數據表參考的數據列。

  警告

  如果您省略 join_criteria 任何 join_type 的語意會變成 的語 CROSS JOIN意。

  • ON boolean_expression

    具有 BOOLEAN 傳回型別的運算式，指定兩個關聯數據列的比對方式。 如果結果為 true，則會將數據列視為相符專案。

  • USING （ column_name [， ...] ）

    藉由比較存在於兩個關聯中的欄位清單 column_name 的相等性來比對這些列。

• table_alias

  具有可選的欄位識別符清單的暫時名稱。

備註

當您指定 USING 或 NATURAL時，SELECT * 只會顯示每一個用來比對的數據行出現一次，後面接著左邊的數據行，然後是右聯結數據表，但不包括聯結的數據行。

SELECT * FROM first JOIN second USING (a, b)

相當於

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

範例

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