SQL not exists サンプルコード 2テーブルの片方にしかないデータを抽出
  • facebookページ
  • twitterページ
  • 2019.09.05

    SQL not exists サンプルコード 2テーブルの片方にしかないデータを抽出

    not existsを使うと、サブクエリに存在しないデータを抽出できます。

    not existsの使い方、サンプル、注意点をご紹介致します。

    not existsの構文

    not existsは、WHERE句で副問い合わせ(サブクエリ)と組み合わせて使用します。

    副問い合わせの結果が存在しないとき、not existsは真となります。

    クエリによっては、not existsをexistsを使って置き換えることも可能です。

    not exists の書き方サンプルコード

    not existsが実際、どういう場面で役に立つのか、サンプルコードを実行しながら見て行きましょう。

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

    従業員テーブル(employees)から、役職(title)がStaff以外のデータを抽出するサンプルです。

    結果はこうなります。役職が「Staff」の社員(社員番号10002,10005,10007,10011…)が除外されて抽出されています。

    not existsは、基本的にjoinで置き換えられます。

    joinで置き換えできないnot existsの使いかたを見て行きましょう。

    not existsでどちらか片方にしかないデータを抽出

    2テーブルのどちらか片方にしかないデータを抽出したい場合のサンプルです。

    例えば、employeesテーブルにはデータが存在するが、titlesテーブルにはデータが存在しないというケースを想定してみます。

    社員番号 500000のTaro Yamadaという社員のデータをemployeesテーブルにinsertしました。titlesテーブルには、何も挿入していません。

    以下のクエリを実行してみます。

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

    データが論理的に整合性を保っているかチェックするケース、例えば、リソーステーブルのデータ欠落チェックなどのために、not existsは役立ちそうですね。

    同一テーブルの重複データを除外する

    not existsのもうひとつの使い道は、重複データの除外です。

    salaries(給与)テーブルから、to_date(有効日付)が最大のもの以外を除外します。

    エイリアス(別名)を使うことで、ひとつのテーブルを別々の2つのテーブルのように扱うことができます。

    考え方は少々トリッキーで、「a.to_date < b.to_date」という条件により、to_dateに自身のデータ以下が存在するデータを抽出するサブクエリに対してnot existsを指定しています。

    結果として、「to_dateが最大のもの以外除外」となります。

    クエリの実行結果は以下の通り。

    条件を「a.to_date > b.to_date」とすれば、最初の給与(初任給)のみを抽出するSQLになります。

    SQL自体はシンプルですが、一から組み立てようとすると、なかなか思いつきません。

    重複除外には、not_existsが使えるということを覚えておくと良いでしょう。

    なお、少々複雑になりますが、inを使って記述することも可能です。

    【関連記事】
    SQLで重複を削除するサンプルコード 最新データを残してdeleteするには?

    not existsはフルスキャンするので遅い

    not existsのデメリットは、テーブルフルスキャン(全件走査)が実行されるため遅い点。

    開発の場面では、できるだけ使用を避け、joinで置き換えるべきでしょう。

    not existsを置き換えてパフォーマンスアップ

    実際、joinに置き換えるとどの程度速くなるのでしょう?

    MySQLにて、not exists版実行時にかかった時間は、以下の通り。

    • 335917 total, Query took 0.0006 seconds.

    join版に置き換えたときの実行時間は、こうなりました。

    • 335917 total, Query took 0.0004 seconds.

    実行時間は、約34%短縮。

    条件が複雑になるほど、実行時間の差は開くと考えられます。

    ただし、データベースによっては内部でSQLが最適化され、ほとんどパフォーマンスに影響しないケースもあります。

    【関連記事】
    NOT EXISTSとLEFT JOINの処理速度検証 Oralceでは実行結果はほぼ同じ

    not exists select 1 と記述してもパフォーマンスは変わらない

    古いデータベースでパフォーマンスをアップする裏技として、サブクエリのselect *をselect 1と記述するというものがありました。select *で全カラムを参照しなくても、存在するかどうか見ているだけだからselect 1を指定した方が早い、という理由です。

    しかし、現在のデータベースは、内部でSQLの最適化がおこなわれるため、パフォーマンスは変わりません。

    上記の重複を除外するSQLを、select 1に差し替えて、実行時間を比較してみましょう。

    select *の場合の実行時間

    • Showing rows 0 – 24 (300180 total, Query took 0.0011 seconds.)

    select 1の場合の実行時間

    • Showing rows 0 – 24 (300180 total, Query took 0.0012 seconds.)

    実行タイミングにより、select *またはselect 1がわずかに遅いケースがありましたが、誤差と言えるでしょう。

    メンテナンス性を考えると、他人が「このコード、どういう意味だろう?」と迷うようなSQLは避けた方がよいでしょう。

    not existsの使い方まとめ

    ポテパンダの一言メモ
    • not existsで、2テーブルの片方のみに存在するデータ抽出が可能
    • not existsで、重複データ抽出クエリをシンプルに記述できる
    • not existsは、実行速度が遅いので、可能な場合はjoinに置き換えて記述する

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

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

    ポテパンフリーランス

    ポテパンフリーランス

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

    ポテパンキャリア

    ポテパンキャリア

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

    ポテパンキャンプ

    ポテパンキャンプ

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



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

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

    この記事をシェア

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









    ABOUT US

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

    READ MORE

    ポテパンおすすめ案件