PostgreSQL-JOINS
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は非常に大きなテーブルを生成する可能性があるため、適切な場合にのみ使用するように注意する必要があります。
以下は、CROSSJOINの構文です。
SELECT ... FROM table1 CROSS JOIN table2 ...
上記の表に基づいて、次のようにCROSSJOINを記述できます。
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キーワードを使用できます。
以下は、INNERJOINの構文です。
SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_filed = table2.common_field;
上記の表に基づいて、次のようにINNERJOINを記述できます。
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は、INNERJOINの拡張です。SQL標準では、LEFT、RIGHT、およびFULLの3種類のOUTER JOINが定義されており、PostgreSQLはこれらすべてをサポートしています。
LEFT OUTER JOINの場合、最初に内部結合が実行されます。次に、テーブルT2のどの行とも結合条件を満たさないテーブルT1の各行について、結合された行がT2の列にNULL値で追加されます。したがって、結合されたテーブルには、T1の各行に対して常に少なくとも1つの行があります。
以下は、LEFT OUTERJOINの構文です。
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の各行に常に1つの行があります。
以下は、RIGHT OUTERJOINの構文です。
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 OUTERJOINの構文です。
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 |