PostgreSQL 結合(JOIN)

PostgreSQL の JOIN 句は、2つ以上のテーブルの行を、それらのテーブル間の共通フィールドに基づいて結合するために使用されます。

PostgreSQL では、JOIN には5つの結合タイプがあります：

CROSS JOIN ：交差結合
INNER JOIN：内部結合
LEFT OUTER JOIN：左外部結合
RIGHT OUTER JOIN：右外部結合
FULL OUTER JOIN：完全外部結合

次に、2つのテーブルを作成しましょうCOMPANYとDEPARTMENT。

例

COMPANY テーブルを作成します（COMPANY SQL ファイルをダウンロード）、データ内容は以下のとおりです：

サンプルdb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

テーブルにいくつかのデータを追加します：

INSERT INTO COMPANY VALUES (8, 'Paul', 24, 'Houston', 20000.00);
INSERT INTO COMPANY VALUES (9, 'James', 44, 'Norway', 5000.00);
INSERT INTO COMPANY VALUES (10, 'James', 45, 'Texas', 5000.00);

このとき、COMPANY テーブルのレコードは以下のとおりです：

 id | name  | age | address      | salary
 ----+-------+-----+--------------+--------
   1 | Paul  |  32 | California   |  20000
   2 | Allen |  25 | Texas        |  15000
   3 | Teddy |  23 | Norway       |  20000
   4 | Mark  |  25 | Rich-Mond    |  65000
   5 | David |  27 | Texas        |  85000
   6 | Kim   |  22 | South-Hall   |  45000
   7 | James |  24 | Houston      |  10000
   8 | Paul  |  24 | Houston      |  20000
   9 | James |  44 | Norway       |   5000
  10 | James |  45 | Texas        |   5000
(10 rows)

DEPARTMENT テーブルを作成し、3つのフィールドを追加します：

CREATE TABLE DEPARTMENT(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      NOT NULL
);

DEPARTMENT テーブルに3つのレコードを挿入します：

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (1, 'IT Billing', 1 );

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (2, 'Engineering', 2 );

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (3, 'Finance', 7 );

このとき、DEPARTMENT テーブルのレコードは以下のとおりです：

 id | dept        | emp_id
----+-------------+--------
  1 | IT Billing  |  1
  2 | Engineering |  2
  3 | Finance     |  7

交差結合

交差結合（CROSS JOIN）は、最初のテーブルの各行を2番目のテーブルの各行とマッチングさせます。2つの入力テーブルがそれぞれ x 行と y 行ある場合、結果テーブルは x*y 行になります。

交差結合（CROSS JOIN）は非常に大きなテーブルを生成する可能性があるため、使用時には注意が必要で、適切な場合にのみ使用してください。

以下は CROSS JOIN の基本構文です：

SELECT ... FROM table1 CROSS JOIN table2 ...

上記のテーブルに基づいて、次のように交差結合（CROSS JOIN）を記述できます：

サンプルdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;

得られる結果は以下のとおりです：

サンプルdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;
 emp_id | name  |       dept
--------+-------+--------------------
      1 | Paul  | IT Billing
      1 | Allen | IT Billing
      1 | Teddy | IT Billing
      1 | Mark  | IT Billing
      1 | David | IT Billing
      1 | Kim   | IT Billing
      1 | James | IT Billing
      1 | Paul  | IT Billing
      1 | James | IT Billing
      1 | James | IT Billing
      2 | Paul  | Engineering
      2 | Allen | Engineering
      2 | Teddy | Engineering
      2 | Mark  | Engineering
      2 | David | Engineering
      2 | Kim   | Engineering
      2 | James | Engineering
      2 | Paul  | Engineering
      2 | James | Engineering
      2 | James | Engineering
      7 | Paul  | Finance

内部結合

内部結合（INNER JOIN）は、結合述語に基づいて2つのテーブル（table1 と table2）の列値を組み合わせて、新しい結果テーブルを作成します。クエリは table1 の各行を table2 の各行と比較し、結合述語を満たす行の一致ペアをすべて見つけます。

結合述語を満たす場合、A 行と B 行の各一致ペアの列値が1つの結果行に結合されます。

内部結合（INNER JOIN）は最も一般的な結合タイプであり、デフォルトの結合タイプです。

INNER キーワードは省略可能です。

以下は内部結合（INNER JOIN）の構文です：

SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_filed = table2.common_field;

上記のテーブルに基づいて、次のように内部結合を記述できます：

サンプルdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
 emp_id | name  |        dept
--------+-------+--------------
      1 | Paul  | IT Billing
      2 | Allen | Engineering
      7 | James | Finance
(3 rows)

左外部結合

外部結合は内部結合の拡張です。SQL 標準では、LEFT、RIGHT、FULL の3種類の外部結合が定義されており、PostgreSQL はこれらすべてをサポートしています。

左外部結合では、まず内部結合が実行されます。次に、テーブル T2 の結合条件を満たさないテーブル T1 の各行について、T2 の列に null 値がある場合も結合行が追加されます。したがって、結合されたテーブルには、T1 の各行に対して少なくとも1行があります。

以下は左外部結合（LEFT OUTER JOIN）の基本構文です：

SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...

上記の2つのテーブルに基づいて、次のように左外部結合を記述できます：

サンプルdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
 emp_id | name  |      dept
--------+-------+----------------
      1 | Paul  | IT Billing
      2 | Allen | Engineering
      7 | James | Finance
        | James | 
        | David | 
        | Paul  | 
        | Kim   | 
        | Mark  | 
        | Teddy | 
        | James | 
(10 rows)

右外部結合

まず、内部結合が実行されます。次に、テーブル T1 の結合条件を満たさないテーブル T2 の各行について、T1 の列の値が null の場合も結合行が追加されます。これは左結合の逆です。T2 の各行に対して、結果テーブルには常に1行があります。

以下は右外部結合（RIGHT OUT JOIN）の基本構文です：

SELECT ... FROM table1 RIGHT OUTER JOIN table2 ON conditional_expression ...

上記の2つのテーブルに基づいて、右外部結合を確立します：

サンプルdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY RIGHT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
 emp_id | name  |    dept
--------+-------+-----------------
      1 | Paul  | IT Billing
      2 | Allen | Engineering
      7 | James | Finance
(3 rows)

外部結合

まず、内部結合が実行されます。次に、テーブル T2 のいずれかの行の結合条件を満たさないテーブル T1 の各行について、T2 の列に null 値がある場合も結果に追加されます。さらに、T1 のいずれかの行との結合条件を満たさない T2 の各行について、T1 の列に null 値を含む行が結果に追加されます。

以下は外部結合の基本構文です：

SELECT ... FROM table1 FULL OUTER JOIN table2 ON conditional_expression ...

上記の2つのテーブルに基づいて、外部結合を確立できます：

サンプルdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY FULL OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
 emp_id | name  |      dept
--------+-------+-----------------
      1 | Paul  | IT Billing
      2 | Allen | Engineering
      7 | James | Finance
        | James | 
        | David | 
        | Paul  | 
        | Kim   | 
        | Mark  | 
        | Teddy | 
        | James | 
(10 rows)

その他の拡張

PostgreSQL 上級