JOIN

[アーティクル]
03/01/2024

適用対象: 「はい」のチェックマーク Databricks SQL Databricks Runtime

2 つのテーブル参照の行を、結合条件に基づいて結合します。

構文

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

JOIN の種類。
- [ INNER ]
  
  両方のテーブル参照で一致する値を持つ行を返します。既定の結合種類です。
- LEFT [ OUTER ]
  
  左のテーブル参照のすべての値と、右のテーブル参照の一致した値を返します。一致するものがない場合は NULL を追加します。これは、"左外部結合" とも呼ばれます。
- RIGHT [ OUTER ]
  
  右のテーブル参照のすべての値と、左のテーブル参照の一致した値を返します。一致するものがない場合は NULL を追加します。これは、"右外部結合" とも呼ばれます。
- FULL [OUTER]
  
  両方のリレーションのすべての値を返します。一致するものがない側に NULL 値が付加されます。これは、"完全外部結合" とも呼ばれます。
- [ LEFT ] SEMI
  
  右辺との一致を持つテーブル参照の左辺からの値を返します。これは、"左半結合" とも呼ばれます。
- [ LEFT ] ANTI
  
  右のテーブル参照との一致がない左のテーブル参照から値を返します。これは、"左反結合" とも呼ばれます。
CROSS JOIN

2 つのリレーションのデカルト積を返します。
NATURAL

一致する名前を持つすべての列の等式で、2 つのリレーションの行が暗黙的に一致することを指定します。
join_criteria

あるテーブル参照の行を別のテーブル参照の行とどのように結合するかを指定します。
- ON boolean_expression
  
  2 つのリレーションの行がどのように一致されるかを指定する戻り型がブール値の式。結果が true の場合、行は一致と見なされます。
- USING ( column_name [, …] )
  
  両方のリレーションに存在しなければならない列 column_name のリストの等価性を比較することによって、行を照合します。
  
  USING (c1, c2) は ON rel1.c1 = rel2.c1 AND rel1.c2 = rel2.c2 のシノニムです。
table_alias

省略可能な列識別子リストを持つ一時的な名前です。

メモ

USING または NATURAL を指定すると、SELECT * が、照合に使用されるそれぞれの列に対して 1 回だけ表示されます。

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

JOIN

構文

パラメーター

メモ

例

関連記事

その他のリソース