Postgresql-using-joins

PostgreSQL-参加

PostgreSQLの Joins 句は、データベース内の2つ以上のテーブルのレコードを結合するために使用されます。 JOINは、それぞれに共通の値を使用して2つのテーブルのフィールドを結合するための手段です。

PostgreSQLの結合タイプは-

• クロスジョイン
• インナージョイン
• 左アウタージョイン
• 正しいアウタージョイン
• フルアウタージョイン

先に進む前に、COMPANYとDEPARTMENTの2つのテーブルについて考えてみましょう。 COMPANYテーブルにデータを挿入するINSERTステートメントを見てきました。 COMPANYテーブルで利用可能なレコードのリストを想定してみましょう-

 id | name  | age | address   | salary | join_date
----+-------+-----+-----------+--------+-----------
  1 | Paul  |  32 | California|  20000 | 2001-07-13
  3 | Teddy |  23 | Norway    |  20000 |
  4 | Mark  |  25 | Rich-Mond |  65000 | 2007-12-13
  5 | David |  27 | Texas     |  85000 | 2007-12-13
  2 | Allen |  25 | Texas     |        | 2007-12-13
  8 | Paul  |  24 | Houston   |  20000 | 2005-07-13
  9 | James |  44 | Norway    |   5000 | 2005-07-13
 10 | James |  45 | Texas     |   5000 | 2005-07-13

別のテーブルはDEPARTMENTであり、次の定義があります-

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

DEPARTMENTテーブルに入力するINSERTステートメントのリストは次のとおりです-

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番目のテーブルのすべての行を一致させます。 入力テーブルにそれぞれx列とy列がある場合、結果のテーブルにはx + y列があります。 CROSS JOINは非常に大きなテーブルを生成する可能性があるため、適切な場合にのみ使用するように注意する必要があります。

以下は、CROSS JOINの構文です-

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

上記の表に基づいて、次のようにCROSS JOINを記述できます-

testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;

上記のクエリは、次の結果を生成します-

emp_id| name  |  dept
------|-------|--------------
    1 | Paul  | IT Billing
    1 | Teddy | IT Billing
    1 | Mark  | IT Billing
    1 | David | IT Billing
    1 | Allen | IT Billing
    1 | Paul  | IT Billing
    1 | James | IT Billing
    1 | James | IT Billing
    2 | Paul  | Engineering
    2 | Teddy | Engineering
    2 | Mark  | Engineering
    2 | David | Engineering
    2 | Allen | Engineering
    2 | Paul  | Engineering
    2 | James | Engineering
    2 | James | Engineering
    7 | Paul  | Finance
    7 | Teddy | Finance
    7 | Mark  | Finance
    7 | David | Finance
    7 | Allen | Finance
    7 | Paul  | Finance
    7 | James | Finance
    7 | James | Finance

インナージョイン

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

INNER JOINは、最も一般的な結合のタイプであり、デフォルトの結合タイプです。 オプションでINNERキーワードを使用できます。

以下はINNER JOINの構文です-

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

上記の表に基づいて、次のようにINNER JOINを書くことができます-

testdb=# 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

左アウタージョイン

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

LEFT OUTER JOINの場合、最初に内部結合が実行されます。 次に、テーブルT2の行との結合条件を満たさないテーブルT1の各行について、T2の列にNULL値を持つ結合行が追加されます。 したがって、結合テーブルには、T1の各行に対して常に少なくとも1つの行があります。

以下はLEFT OUTER JOINの構文です-

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

上記の表に基づいて、次のように内部結合を記述できます-

testdb=# 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
        | James |
        | David |
        | Paul  |
        | Mark  |
        | Teddy |
        | James |

正しいアウタージョイン

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

以下は、右外部結合の構文です-

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

上記の表に基づいて、次のように内部結合を記述できます-

testdb=# 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 |       | Finance

フルアウタージョイン

最初に、内部結合が実行されます。 次に、テーブルT2の行との結合条件を満たさないテーブルT1の各行について、T2の列にNULL値を持つ結合行が追加されます。 さらに、T1のどの行とも結合条件を満たさないT2の各行に対して、T1の列にNULL値が含まれる結合行が追加されます。

以下は、FULL OUTER JOINの構文です-

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

上記の表に基づいて、次のように内部結合を記述できます-

testdb=# 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 |       | Finance
        | James |
        | David |
        | Paul  |
        | Mark  |
        | Teddy |
        | James |