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;

内部結合は最も制限の厳しい結合タイプです。これは、各テーブルの行を結合して作成された行のみを表示するためです。構成テーブルで行が一致する相手を持たない行は、結果から削除されます。たとえば、最初のテーブルの比較列に「blue」の値があり、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 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句は、等号を使用して、結合が発生するタイミングを決定するために比較される各テーブルの正確な列を指定します。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句の冗長性をさらに削減できるもう1つのショートカットです。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句はクエリの冗長性を減らすことができますが、使用する際は注意が必要です。テーブルの結合に使用される列が自動的に計算されるため、構成テーブルの列が変更されると、新しい結合条件により結果が大きく異なる可能性があります。