PostgreSQLで結合を使用して異なるテーブルからデータを結合する方法

内部結合

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

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

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

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

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

左結合

左結合は、内部結合で見つかったすべてのレコードに加えて、最初のテーブルからの一致しないすべての行を表示する結合です。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つの円の交差によって表される左側の円の部分には、右側のテーブルによって補足された追加データが含まれます。

右結合

右結合は、内部結合で見つかったすべてのレコードに加えて、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つの円の交差によって表される右側の円の部分には、左側のテーブルによって補足された追加データが含まれます。

完全外部結合

完全外部結合は、内部結合で見つかったすべてのレコードに加えて、両方の構成テーブルからの一致しないすべての行を表示する結合です。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 AS childrenとpeople AS mothersを使用して、peopleテーブルの2つのインスタンスを結合できます。そうすることで、結合条件を定義するときに、参照しているテーブルのインスタンスを指定できます。

別の例を次に示します。今回は、従業員とマネージャー間の関係を表しています

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とtable2からの行は、table1のid列がtable2のident列と一致するたびに結合されます。内部結合が使用されているため、結果には結合された行のみが表示されます。クエリはワイルドカード*文字を使用するため、両方のテーブルのすべての列が表示されます。

これは、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句の冗長性をさらに軽減できるもう1つの省略形です。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句はクエリの冗長性を軽減できますが、使用する際には注意が必要です。テーブルを結合するために使用される列は自動的に計算されるため、構成テーブルの列が変更されると、新しい結合条件により結果が大きく異なる可能性があります。