SQL 外部結合サンプルコード 3テーブル結合とパフォーマンス比較
  • facebookページ
  • twitterページ
  • 2019.09.05

    SQL 外部結合サンプルコード 3テーブル結合とパフォーマンス比較

    外部結合は、2テーブルの指定カラムでデータを結合しデータを取得します。一致しないデータについては、NULLとしてデータを取得します。

    外部結合には、LEFT OUTER JOIN、RIGHT OUTER JOIN、FULL OUTER JOINの3種類があります。

    外部結合のサンプルコード

    実際にコピペして動作を確認できる、SQLの外部結合のサンプルコードを紹介します。

    以下、データベースとして、MySQLのサンプルデータベースEmployeesを使っています。SQL実行結果の表示にはphpMyAdminを使用しています。

    3テーブルの外部結合サンプル

    employees(社員)テーブルに、下記の1レコードを挿入します。

    emp_no=500000の場合、対応するdept_name(部署名)データが存在しない状態です。

    外部結合でデータを抽出してみましょう。想定では、dept_nameがNULLとして取得されるはずです。

    SQLの実行結果は以下の通りです。

    SQL実行結果

    予想通り、Taro Yamadaのdept_nameがNULLとして取得できました。

    right join を left joinに置き換えるサンプル

    right joinは、left joinに置き換え可能です。

    下記は、departments(部署名)テーブルとdept_emp(部署名・社員紐付け用テーブル)をright joinするSQLです。

    実行結果は、以下の通り。

    上記SQLをleft joinに置き換えるには、単純にテーブル名を入れ替えます。

    具体的には、以下のようにSQLを書き換えます。

    実行結果はこうなります。

    select *で抽出すると、カラムの並び順が異なる点に注意が必要です。

    開発案件では、混乱を避けるため、left joinのみに統一することもあります。

    完全外部結合

    2テーブルを互いに全件外部結合する方法です。FULL Outer Joinと呼ばれることもあります。

    データベースによっては実装されていません。(例えば、MySQLなどは未実装)

    完全外部結合の使いどころは、実際の開発案件ではほとんどないでしょう。

    なお、完全外部結合は、left outer joinとright outer joinにunionを組み合わせることで同じ結果を得ることが可能です。

    参考)【MySQL5.6】完全外部結合(FULL OUTER JOIN)のやりかた – Qiita

    外部結合が効かないケース 絞り込み条件は、where句に記述する

    上記SQLを実行すると、このような結果になります。

    SQL実行結果

    全てのdept_name(部署名)がNULLとなって取得されてしまいました。

    どういう理由で、この結果になったのでしょうか?

    一言で言うと「絞り込み条件は、where句に指定する」ということになります。

    処理の流れをまとめると以下のようになります。

    • left outer joinでは、left側のテーブル(employees)を全件抽出する。
    • onで指定した条件で抽出できなかったカラムはNULLを抽出する。
    • 「Taro Yamada」以外のデータは、全てdept_nameとしてNULLを取得する。
    • employees.emp_no = 500000 という条件で、「Taro Yamada」のデータのみdept_nameが抽出できるはずが、dept_empにデータが存在しないため、結局dept_nameとしてNULLを取得する。
    ポテパンダの一言メモ

    left outer joinは、onにどんな条件を指定しても、left側のテーブルを全件抽出する。

    外部結合のleft、rightのイメージ

    外部結合(outer join)には、left outer join、right outer joinの指定があります。

    上記のベン図をイメージしておくと良いでしょう。

    詳細は下記を参照してください。

    【関連記事】
    SQL Joinサンプル集 Joinで遅いSQLの原因を調べる方法 

    内部結合と外部結合のパフォーマンスの差は

    実務の場では、マスターテーブルなどデータが保証されている場合は内部結合で良い所を外部結合を使うケースがあります。

    万が一のデータの不整合があった場合に、データが欠落するよりはNULL値でも取得したほうが良いという場合です。

    しかし、パフォーマンスはどうなるのでしょうか?

    「outer joinは効率が悪い」という声もあります。

    employeesテーブル(30万件)と、salariesテーブル(280万件)を結合して試してみました。

    left joinの実行時間は以下の通り。

    Showing rows 0 – 24 (2844048 total, Query took 0.0004 seconds.)

    inner joinの実行時間は以下の通り。

    Showing rows 0 – 24 (2844048 total, Query took 0.0004 seconds.)

    一見、どちらでもパフォーマンスは変わらないように見えます。念のため、explainで実行計画を確認してみましょう。

    内部結合(inner join)ではemployeesテーブル検索に主キー(PRIMARY)を使用可能なのに対し、外部結合(left outer join)では、検索にキーを使用できません。

    単純なケースでは差が出なくても、SQLが複雑化すると外部結合の方が遅くなる可能性があります。

    外部結合のまとめ

    ポテパンダの一言メモ
    • 外部結合でデータの絞り込みをおこなう場合はwhere句に指定を記述する。
    • 外部結合のleft、right指定にはベン図をイメージする
    • 完全外部結合は、left outer join、right outer join、unionを組み合わせて実現できる
    • 内部結合より、外部結合がパフォーマンスが悪くなるケースがある。

    ポテパンが提供するサービスについて

    本メディア「ポテパンスタイル」を運営する株式会社ポテパンは、エンジニアキャリア領域で複数サービスを提供しています。

    ポテパンフリーランス

    ポテパンフリーランス

    フリーランスエンジニアの方に高単価案件をご紹介しております。弊社ではフリーランス案件を常時300件ほど保有しており、その中からあなたに適した案件をご案内いたします。また、これから独立してフリーランスになる方の無料個別相談も承っております。フリーランスになった後の案件獲得方法やお金面(税金や保険など)についてお答えいたします!フリーエンジニアになりたい方向けのコンテンツも盛りだくさんです。

    ポテパンキャリア

    ポテパンキャリア

    エンジニア職専門の転職エージェントです。ポテパンキャリアでは、技術のわかるエージェントがあなたの転職をサポートします。エージェント自身がエンジニアなので、あなたと同じ目線で仕事内容や今後のキャリアについて一緒に考えることができます。年収800万円以上のハイスペック転職をご希望の方は「ポテパンプロフェッショナル」もご用意しておりますのでご利用下さいませ。

    ポテパンキャンプ

    ポテパンキャンプ

    ポテパンキャンプでは、RubyにてゼロからオリジナルのECサイトを作り上げてる3ヶ月間の実践型カリキュラムを提供しております。すでに本スクールの卒業生は、エンジニア職として様々な企業様に就職しております。なお、本スクールは受講料25万円と他社スクールに比べ格安となっており、またポテパンからご紹介させていただいた企業へ就職が決まった場合は、全額キャッシュバックいたします。



    株式会社ポテパンは、企業とエンジニアの最適なマッチングを追求しています。気になるサービスがあれば、ぜひ覗いてみてください!

    ポテクラバナー ポテプロバナー

    この記事をシェア

    • Facebookシェア
    • Twitterシェア
    • Hatenaシェア
    • Lineシェア
    pickup









    ABOUT US

    ポテパンはエンジニアと企業の最適なマッチングを追求する企業です。

    READ MORE

    ポテパンおすすめ案件