PostgreSQLで結合を使用して異なるテーブルのデータを結合する

内部結合

デフォルトの結合は内部結合（inner join）と呼ばれます。PostgreSQLでは、これはINNER JOINまたは単にJOINを使用して指定できます。

内部結合の構文を示す典型的な例を以下に示します

SELECT
    *
FROM
    table_1
[INNER] JOIN table_2
    ON table_1.id = table_2.table_1_id;

内部結合は、各テーブルからの行を結合することによって作成された行のみを表示するため、最も制約の厳しい結合タイプです。構成テーブル内で、他のテーブルに一致する対応する行がなかった行は、結果から削除されます。たとえば、最初のテーブルの比較列に「青」という値があり、2番目のテーブルにその値を持つレコードがない場合、その行は出力から抑制されます。

結果を構成テーブルのベン図として表すと、内部結合は2つの円の重なり合う領域を表すことができます。いずれかのテーブルにのみ存在した値は表示されません。

左外部結合

左外部結合（left join）は、内部結合で見つかったすべてのレコードに加えて、最初のテーブルからの一致しないすべての行を表示する結合です。PostgreSQLでは、これはLEFT OUTER JOINまたは単にLEFT JOINとして指定できます。

左外部結合の基本的な構文は次のパターンに従います

SELECT
    *
FROM
    table_1
LEFT JOIN table_2
    ON table_1.id = table_2.table_1_id;

左外部結合は、まず内部結合を実行して両方のテーブルの一致するすべてのレコードから行を構築することによって構築されます。その後、最初のテーブルからの一致しないレコードも含まれます。結合の各行には両方のテーブルの列が含まれるため、一致しない列には2番目のテーブルのすべての列の値としてNULLが使用されます。

結果を構成テーブルのベン図として表すと、左外部結合は左側の円全体を表すことができます。2つの円の交差部分で表される左側の円の部分には、右側のテーブルによって補足された追加データが含まれます。

右外部結合

右外部結合（right join）は、内部結合で見つかったすべてのレコードに加えて、2番目のテーブルからの一致しないすべての行を表示する結合です。PostgreSQLでは、これはRIGHT OUTER JOINまたは単にRIGHT JOINとして指定できます。

右外部結合の基本的な構文は次のパターンに従います

SELECT
    *
FROM
    table_1
RIGHT JOIN table_2
    ON table_1.id = table_2.table_1_id;

右外部結合は、まず内部結合を実行して両方のテーブルの一致するすべてのレコードから行を構築することによって構築されます。その後、2番目のテーブルからの一致しないレコードも含まれます。結合の各行には両方のテーブルの列が含まれるため、一致しない列には最初のテーブルのすべての列の値としてNULLが使用されます。

結果を構成テーブルのベン図として表すと、右外部結合は右側の円全体を表すことができます。2つの円の交差部分で表される右側の円の部分には、左側のテーブルによって補足された追加データが含まれます。

完全外部結合

完全結合（full join）は、内部結合で見つかったすべてのレコードに加えて、両方の構成テーブルからの一致しないすべての行を表示する結合です。PostgreSQLでは、これはFULL OUTER JOINまたは単にFULL JOINとして指定できます。

完全外部結合の基本的な構文は次のパターンに従います

SELECT
    *
FROM
    table_1
FULL JOIN table_2
    ON table_1.id = table_2.table_1_id;

完全外部結合は、まず内部結合を実行して両方のテーブルの一致するすべてのレコードから行を構築することによって構築されます。その後、両方のテーブルからの一致しないレコードも含まれます。結合の各行には両方のテーブルの列が含まれるため、一致しない列には、一致しない他のテーブルのすべての列の値としてNULLが使用されます。

結果を構成テーブルのベン図として表すと、完全結合は両方の構成円全体を表すことができます。2つの円の交差部分には、各構成テーブルによって提供される値が含まれます。重なり合う領域の外側の円の部分には、それらが属するテーブルからの値が含まれ、他のテーブルで見つかった列を埋めるためにNULLが使用されます。

クロス結合

CROSS JOINと呼ばれる特別な結合も利用できます。クロス結合は、各テーブルの行が互いに一致するかどうかを判断するためにいかなる比較も使用しません。代わりに、最初のテーブルの各行を2番目のテーブルの各行に単純に追加することによって結果が構築されます。

これは、2つ以上のテーブルの行の直積を生成します。実際には、この結合スタイルは各テーブルからの行を無条件に結合します。したがって、各テーブルに3つの行がある場合、結果のテーブルには両方のテーブルのすべての列を含む9つの行が作成されます。

たとえば、t1というテーブルがt2というテーブルと結合されており、それぞれがr1、r2、r3という行を持っている場合、結果は次のように結合された9つの行になります

t1.r1 + t2.r1
t1.r1 + t2.r2
t1.r1 + t2.r3
t1.r2 + t2.r1
t1.r2 + t2.r2
t1.r2 + t2.r3
t1.r3 + t2.r1
t1.r3 + t2.r2
t1.r3 + t2.r3

自己結合

自己結合とは、テーブルの行自体と結合するあらゆる結合のことです。これがどのように役立つかはすぐには明らかではないかもしれませんが、実際には多くの一般的なアプリケーションがあります。

多くの場合、テーブルは互いに関連して複数の役割を果たすことができるエンティティを記述します。たとえば、peopleテーブルがある場合、各行にはテーブル内の他のpeopleを参照するmother列が含まれる可能性があります。自己結合を使用すると、これらの値が一致する最初のテーブルに2番目のインスタンスのテーブルを結合することで、これらの異なる行を結合できます。

自己結合は同じテーブルを2回参照するため、参照を明確にするためにテーブルエイリアスが必要です。上記の例では、たとえば、peopleテーブルの2つのインスタンスをpeople AS childrenとpeople AS mothersというエイリアスを使用して結合できます。これにより、結合条件を定義するときに、参照しているテーブルのインスタンスを指定できます。

従業員と管理者の関係を示す別の例を以下に示します

SELECT
    *
FROM
    people AS employee
JOIN people AS manager
    ON employee.manager_id = manager.id;

ON句

テーブル結合の条件を定義する最も標準的な方法は、ON句を使用することです。ON句は、結合が発生するタイミングを決定するために比較される各テーブルの正確な列を指定するために、等号を使用します。PostgreSQLは提供された列を使用して、各テーブルからの行を結合します。

ON句は最も冗長ですが、利用可能な結合条件の中で最も柔軟でもあります。これは、結合される各テーブルの列名がどれほど標準化されているかに関係なく、特定の指定を可能にします。

ON句の基本的な構文は次のようになります

SELECT
    *
FROM
    table1
JOIN
    table2
ON
    table1.id = table2.ident;

ここでは、table1のid列がtable2のident列と一致する場合に、table1とtable2の行が結合されます。内部結合が使用されているため、結果には結合された行のみが表示されます。クエリはワイルドカード*文字を使用しているため、両方のテーブルのすべての列が表示されます。

これは、結合条件を満たすことで同じ厳密な値を持つにもかかわらず、table1のid列とtable2のident列の両方が表示されることを意味します。SELECT列リストで表示したい正確な列を指定することで、この重複を避けることができます。

USING句

USING句は、比較される列が両方のテーブルで同じ名前を持つ場合に使用できるON句の条件を指定するための省略形です。USING句は、比較されるべき共有列名のリストを括弧で囲んで取ります。

USING句の一般的な構文は次の形式を使用します

SELECT
    *
FROM
    table1
JOIN
    table2
USING
    (id, state);

この結合は、両方のテーブルが共有する2つの列（idとstate）がそれぞれ一致する値を持つ場合に、table1をtable2と結合します。

この同じ結合は、ONを使用して次のように冗長に表現することもできます

SELECT
    *
FROM
    table1
JOIN
    table2
ON
    table1.id = table2.id AND table1.state = table2.state;

上記のどちらの結合も同じデータを持つ同じ行を構築する結果になりますが、表示方法はわずかに異なります。ON句は両方のテーブルのすべての列を含みますが、USING句は重複する列を抑制します。したがって、2つの別々のid列と2つの別々のstate列（各テーブルに1つずつ）がある代わりに、結果は共有された列がそれぞれ1つだけになり、それに続いてtable1とtable2によって提供される他のすべての列が表示されます。

NATURAL句

NATURAL句は、USING句の冗長性をさらに減らすことができる別の省略形です。NATURAL結合は、一致させる列を一切指定しません。代わりに、PostgreSQLは各データベースで一致する列を持つすべての列に基づいて、テーブルを自動的に結合します。

NATURAL結合句の一般的な構文は次のようになります

SELECT
    *
FROM
    table1
NATURAL JOIN
    table2;

table1とtable2の両方にid、state、companyという名前の列があると仮定すると、上記のクエリはON句を使用した次のクエリと同等になります

SELECT
    *
FROM
    table1
JOIN
    table2
ON
    table1.id = table2.id AND table1.state = table2.state AND table1.company = table2.company;

そして、USING句を使用したこのクエリ

SELECT
    *
FROM
    table1
JOIN
    table2
USING
    (id, state, company);

USING句と同様に、NATURAL句は重複する列を抑制するため、結果には結合された各列の単一のインスタンスのみが存在します。

NATURAL句はクエリの冗長性を減らすことができますが、使用する際には注意が必要です。テーブルを結合するために使用される列は自動的に計算されるため、構成テーブルの列が変更されると、新しい結合条件により結果が大きく異なる可能性があります。