MySQLで異なるテーブルのデータを結合するために結合を使用する

内部結合と交差結合

デフォルトの結合は内部結合と呼ばれます。MySQLでは、これはINNER JOIN、単にJOIN、またはCROSS JOINを使用して指定できます。他のデータベースシステムでは、INNER JOINとCROSS JOINはしばしば2つの異なる概念ですが、MySQLでは同じ構文で実装されています。

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

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

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

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

上記で述べたように、MySQLではこの形式を使ってクロス結合も生成されます。MySQLでは、一致条件なしで内部結合を使ってクロス結合を生成できます。クロス結合は、各テーブルの行が互いに一致するかどうかを判断するために比較を使用しません。代わりに、最初のテーブルの各行と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

左結合

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

ポータビリティの理由から、MySQLでは可能な場合は右結合ではなく左結合を使用することを推奨しています。

完全結合

完全結合は、内部結合で見つかったすべてのレコードに加えて、両方の構成テーブルからの不一致のすべての行を表示する結合です。MySQLは完全結合をネイティブには実装していませんが、いくつかのトリックを使用してその動作をエミュレートできます。

完全外部結合の結果を再現するには、両方のテーブルで共有されているすべての結果と左テーブルの不一致の行すべてに対して左結合を実行します。次に、UNION ALLセット演算子を使用して、これらの結果を右テーブルの「アンチ結合」と結合します。「アンチ結合」とは、テーブル間で共通しない結果のみを見つける結合操作です。

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

(   SELECT
        *
    FROM
        table_1
    LEFT JOIN table_2
        ON table_1.id = table_2.table_1_id
)
UNION ALL
(   SELECT
        *
    FROM
        table_1
    RIGHT JOIN table_2
        ON table_1.id = table_2.table_1_id
    WHERE table_1.id IS NULL
);

結合の各行には両方のテーブルの列が含まれるため、不一致の列は、一致しない他のテーブルのすべての列の値としてNULLを使用します。

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

自己結合

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

多くの場合、テーブルは互いに関連して複数の役割を果たすことができるエンティティを記述します。例えば、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句は、結合が発生するかどうかを決定するために比較される各テーブルの正確な列を指定するために、等号を使用します。MySQLは、提供された列を使用して各テーブルの行を結合します。

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結合は、一致させる列を一切指定しません。代わりに、MySQLは、各データベースに一致する列を持つすべての列に基づいてテーブルを自動的に結合します。

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