メインコンテンツへスキップ
見出し画像

第14回:自己結合を段階的に理解する(CROSS JOIN・結合条件・DISTINCT)

    はじめに

    今回のテーマは、自己結合を段階的に理解することです。

    自己結合とは、同じテーブルを自分自身と結合することです。

    「同じテーブル同士を結合して、何の意味があるの?」

    最初はそう感じるかもしれません。

    しかし、同じテーブルを2つの別名で扱うことで、異なる行同士を比較したり、特定の組み合わせを探したりできます。

    今回は、CROSS JOINから始めて、条件を少しずつ追加しながら、SQLの実行結果がどのように変化するかを確認します。

    演習は次の4段階です。

    1. CROSS JOINで、すべての組み合わせを作る。

    2. 結合条件を加えて、監督が異なる映画同士を組み合わせる。

    3. WHERE句で、Toy Storyシリーズに対象を絞る。

    4. DISTINCTで、重複する映画情報を取り除く。

    第8回「SQLの実行順序」とも関連する内容です。

    それぞれのSQLが何をしているのかを意識しながら、進めていきましょう。

    今回使用するSQLBolt

    今回の演習には、SQLBoltのLesson 6に登場するMoviesテーブルを使用します。

    SQLBoltのLesson 6が今回の自己結合を直接教えているわけではありません。

    Moviesテーブルには、映画タイトルや監督などの情報があり、同じテーブル内の行同士を比較する演習に適しているため、利用しています。

    使用するテーブル

    Moviesテーブル(全14行)

    画像

    今回使用する列は、主に次の3つです。

    • Id:映画ID

    • Title:映画タイトル

    • Director:監督名

    同じMoviesテーブルを、aとbという2つの別名で扱います。

    ここで重要なのは、aとbは別々のテーブルではなく、同じMoviesテーブルを参照しているということです。

    ① CROSS JOINで全組み合わせを作る

    まずは、最も基本となるCROSS JOINです。

    SQL

    select a.id, a.title, a.director, b.id, b.title, b.director
    from movies a cross join movies b

    実行結果

    画像

    CROSS JOINは、左側のテーブルの各行に対して、右側のテーブルのすべての行を組み合わせます。

    今回は、Moviesテーブルが14行あります。

    同じテーブルを2つ使っているため、

    14 × 14 = 196行

    の組み合わせが作られます。

    例えば、左側のaが「Toy Story」の場合、右側のbには14作品すべてが順番に組み合わされます。

    • Toy Story × Toy Story

    • Toy Story × A Bug's Life

    • Toy Story × Toy Story 2

    • Toy Story × Monsters, Inc.

    • ……以下、残りの映画との組み合わせ

    左側が「A Bug's Life」に変われば、同じように右側の14作品すべてと組み合わされます。

    この処理が14作品分繰り返されるため、196行になるわけです。

    ここで覚えておきたいのは、CROSS JOINは、条件を指定せずにすべての組み合わせを作るということです。

    ② 結合条件を加えて、自己非等値結合にする

    次に、CROSS JOINに条件を加えます。

    今回の条件は、

    「監督が異なる映画同士を組み合わせる」

    です。

    SQL

    select a.id, a.title, a.director, b.id, b.title, b.director
    from movies a cross join movies b
    on a.director <> b.director /*自己非等値結合*/

    実行結果

    画像

    ここで注目するのは、次の条件です。

    a.director <> b.director

    <>は「等しくない」という意味です。

    つまり、左側の映画と右側の映画について、監督名が異なる組み合わせだけを残します。

    例えば、左側の映画が「Toy Story」の場合、監督はJohn Lasseterです。

    そのため、右側に同じJohn Lasseter監督の映画が来る組み合わせは対象外になります。

    一方、次のような組み合わせは条件を満たします。

    • Toy Story × Monsters, Inc.

    • Toy Story × Finding Nemo

    • Toy Story × The Incredibles

    これらは監督が異なるためです。

    このように、同じテーブル同士を結合し、等しくないという条件で行を比較する方法を、自己非等値結合と呼びます。

    SQLBoltとSQL Serverでの書き方の違い

    ここで、1つ注意点があります。

    今回のSQLは、SQLBoltのSQLite環境で実行しているものです。

    SQLiteでは、今回のようにCROSS JOINにON句を付ける書き方が使えます。

    一方、SQL Serverなどでは、通常、CROSS JOINにON句を付けることはできません。

    SQL Serverで同じ条件を表現するなら、例えば次のように書きます。

    select a.id, a.title, a.director, b.id, b.title, b.director
    from movies a
    inner join movies b
        on a.director <> b.director

    また、CROSS JOINを使ったまま条件を指定するなら、次の書き方もできます。

    select a.id, a.title, a.director, b.id, b.title, b.director
    from movies a
    cross join movies b
    where a.director <> b.director

    今回の記事では、普段の演習で使用しているSQLBoltのSQLをそのまま掲載しています。

    使用するデータベースによって、結合条件の書き方が異なる場合があることも覚えておきましょう。

    ③ WHERE句で抽出条件を追加する

    続いて、②のSQLにWHERE句を追加します。

    今回は、両側の映画タイトルがToy Storyシリーズに該当する組み合わせだけを抽出します。

    SQL

    select a.id, a.title, a.director, b.id, b.title, b.director
    from movies a cross join movies b
    on a.director <> b.director /*自己非等値結合*/
    where a.title like '%Toy Story%' and b.title like '%Toy Story%'

    実行結果

    画像

    ここで追加した条件は、

    where a.title like '%Toy Story%'
      and b.title like '%Toy Story%'

    です。

    LIKE '%Toy Story%'は、映画タイトルに「Toy Story」という文字列が含まれることを意味します。

    今回のMoviesテーブルには、次の3作品があります。

    • Toy Story:John Lasseter

    • Toy Story 2:John Lasseter

    • Toy Story 3:Lee Unkrich

    まず、両側ともToy Storyシリーズである組み合わせが対象になります。

    ただし、②で指定した「監督が異なる」という条件も引き続き有効です。

    そのため、Toy StoryとToy Story 2の組み合わせは、監督が同じなので対象外です。

    一方、Toy Story 3は監督が異なるため、次の4行が取得されます。

    • Toy Story × Toy Story 3

    • Toy Story 2 × Toy Story 3

    • Toy Story 3 × Toy Story

    • Toy Story 3 × Toy Story 2

    ここで重要なのは、結合条件とWHERE条件の両方を満たす行だけが、最終的な結果に残るということです。

    また、aとbの左右が入れ替わった組み合わせも、別々の行として取得されます。

    自己結合では、この点も意識しておきましょう。

    ④ DISTINCTで重複を排除する

    最後に、③のSQLを少し変更します。

    今回は、左側の映画情報だけを取得し、DISTINCTで重複を取り除きます。

    SQL

    select distinct a.id, a.title, a.director
    from movies a cross join movies b
    on a.director <> b.director /*自己非等値結合*/
    where a.title like '%Toy Story%' and b.title like '%Toy Story%'

    実行結果

    画像

    結果は次の3作品です。

    • Toy Story

    • Toy Story 2

    • Toy Story 3

    ③の段階では4行ありました。

    しかし、今回のSELECT句では、b側の映画情報を表示していません。

    左側のaの情報だけを取り出すと、Toy Story 3が2行登場します。

    そのため、DISTINCTによって重複が排除され、3行になります。

    ここで大切なのは、DISTINCTは、SELECT句に指定した列の組み合わせが同じ行を重複として扱うということです。

    今回は、a.id、a.title、a.directorの3列が比較対象になります。

    また、DISTINCTは元のMoviesテーブルのデータを削除するわけではありません。

    あくまで、最終的に返す検索結果から重複を取り除く処理です。

    第8回「SQLの実行順序」とのつながり

    今回の演習では、SQLを4段階に分けて確認しました。

    最初はCROSS JOINによって196行の組み合わせを作りました。

    そこから、

    • 結合条件で、監督が異なる組み合わせに絞る

    • WHERE句で、Toy Storyシリーズに絞る

    • SELECT DISTINCTで、必要な列だけを重複なく取得する

    という流れを確認しました。

    これは、第8回で学んだSQLの論理的な処理順序ともつながります。

    今回のSQLを理解するうえでは、おおむね次の順序を意識すると分かりやすいでしょう。

    1. FROM・JOIN:結合対象となる行を組み合わせる

    2. ON:結合条件を判定する

    3. WHERE:抽出条件を判定する

    4. SELECT:表示する列を決める

    5. DISTINCT:重複する結果行を取り除く

    ただし、これはSQLを理解するための論理的な処理順序です。

    データベース内部では、実行計画の最適化によって、必ずしもこの順番で物理的に処理されるわけではありません。

    今回のようにSQLを少しずつ変化させながら実行すると、それぞれの句が結果に与える影響を体感できます。

    実務ではどう使う?

    自己結合は、実務でもさまざまな場面で利用できます。

    例えば、製造業の部品マスタを考えてみましょう。

    同じ部品マスタの中から、異なる部品同士の組み合わせを調べたい場合があります。

    また、同じテーブル内で、特定の属性が一致するデータや、一致しないデータを比較したい場合にも自己結合が役立ちます。

    例えば、同じ顧客に紐づく複数の注文を比較したり、同じ品目について異なる時点のデータを比較したりする場面です。

    ただし、CROSS JOINは行数が急激に増える可能性があります。

    例えば、1,000行のテーブル同士をCROSS JOINすると、最大100万行の組み合わせになります。

    そのため、実務では、本当に必要な組み合わせだけを取得できるように、結合条件や抽出条件を適切に指定することが重要です。

    今回のまとめ

    今回は、自己結合を4段階に分けて練習しました。

    1. CROSS JOINで、同じテーブル同士の全組み合わせを作る。

    2. 非等値の結合条件で、異なる監督の映画同士を組み合わせる。

    3. WHERE句で、さらに対象を絞る。

    4. DISTINCTで、最終的な検索結果の重複を排除する。

    今回のポイントは、個々のSQL構文を覚えることだけではありません。

    条件を1つ追加すると、結果がどのように変わるのかを、自分で予測できるようになることです。

    SQLを見たときに、結合によってどのような行が作られ、どの条件で絞られ、最後に何が表示されるのか。

    その流れを自然にイメージできるようになれば、複雑なSQLも理解しやすくなります。


    SQLステップアップ講座の記事一覧はこちら

    前回の記事
    第13回:「全称文を集合的に解く」

    次回の記事
    第15回:集合演算を理解する(UNION・UNION ALL・EXCEPT・INTERSECT)


     
     
    SQL初心者から、実務で使える中級者へ。SQLBoltを活用した16テーマの実践トレーニングと、製造業の業務データを題材にしたオリジナルSQL演習問題集(全20問)を公開中。SQLを「息をするように書ける」力を身につけることを目指します。

    あなたへのおすすめ