バナー画像

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

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

not existsの構文

SELECT [要素名] FROM [テーブル名]
  WHERE not exists (
    [副問い合わせのSELECT文]
)

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

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

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

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

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

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

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

SELECT * FROM employees 
WHERE 
	not exists (
		select * from titles 
		where 
			employees.emp_no = titles.emp_no 
			and titles.title = 'Staff'
	)

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

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

SELECT 
	* 
FROM 
	employees 
join titles on employees.emp_no = titles.emp_no 
and titles.title != 'Staff'

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

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

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

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

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

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

SELECT * FROM employees 
WHERE 
	not EXISTS (
		select * from titles 
		where employees.emp_no = titles.emp_no
	)

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

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

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

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

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

select *  FROM salaries a 
WHERE not exists (
	select * from salaries b 
	where a.emp_no = b.emp_no
        and a.to_date < b.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版実行時にかかった時間は、以下の通り。

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

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

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

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

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

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

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

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

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

select *  FROM salaries a 
WHERE not exists (
	select 1 from salaries b 
	where a.emp_no = b.emp_no
        and a.to_date < b.to_date
)

select *の場合の実行時間

select 1の場合の実行時間

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

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

not existsの使い方まとめ

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

エンジニアになりたい人に選ばれるプログラミングスクール「ポテパンキャンプ 」

ポテパンキャンプは卒業生の多くがWebエンジニアとして活躍している実践型プログラミングスクールです。 1000名以上が受講しており、その多くが上場企業、ベンチャー企業のWebエンジニアとして活躍しています。

基礎的な学習だけで満足せず、実際にプログラミングを覚えて実践で使えるレベルまで学習したいという方に人気です。 プログラミングを学習し実践で使うには様々な要素が必要です。

それがマルっと詰まっているポテパンキャンプでプログラミングを学習してみませんか?

卒業生の多くがWebエンジニアとして活躍

卒業生の多くがWeb企業で活躍しております。
実践的なカリキュラムをこなしているからこそ現場でも戦力となっております。
活躍する卒業生のインタビューもございますので是非御覧ください。

経験豊富なエンジニア陣が直接指導

実践的なカリキュラムと経験豊富なエンジニアが直接指導にあたります。
有名企業のエンジニアも多数在籍し品質高いWebアプリケーションを作れるようサポートします。

満足度高くコスパの高いプログラミングスクール「ポテパンキャンプ」

運営する株式会社ポテパンは10,000人以上のエンジニアのキャリアサポートを行ってきております。
そのノウハウを活かして実践的なカリキュラムを随時アップデートしております。

代表の宮崎もプログラミングを覚えサイトを作りポテパンを創業しました。
本気でプログラミングを身につけたいという方にコスパ良く受講していただきたいと思っておりますので、気になる方はぜひスクール詳細をのぞいてくださいませ。