はじめに

副問い合わせ(サブクエリ)とは、あるクエリ、つまりSELECT文の中に入れ子でSELECT文を置くことです。といっても、活字での説明では難しいので、実際のSQLを書きに示します。

SELECT *
FROM T1
WHERE T1.r2 = (
SELECT T2.r2
FROM T2
WHERE T2.r1 = 'h-0110')

上記は非常にシンプルなサブクエリの例です。内容は抽象的ですが、緑色の部分こそがまさにサブクエリです。

これを言葉で説明すると、

「T2というテーブルからr1の値が”h-0110″であるレコードのr2の値を取得して、T1テーブルのr2の値と比較する。r2が一致したT1のレコードを全て表示する」

てな具合になります。

サブクエリはこの例のように、他のテーブルのデータを抽出条件に含めたいときなどに使用します。そして、通常であれば複数に分かれたクエリが必要な場合でも、サブクエリを活用することで一つのクエリとしてまとめることが出来ます。

サブクエリに慣れてくると色々な使い方が出来るようになり、クエリ方法の幅が広がることでしょう。

 

サブクエリの種類

記述場所での違い

SELECTやWHEREで使用するとサブクエリ

FROMで使用するとインラインビュー

呼称や使用場所こそ違いますが、文法が変わったり独特なワードが必要といったことはありません。両者ともに同じようなものです。

インラインビューは問合せ結果をあたかもテーブルのように扱うことが出来ます。つまり、SQL文中に埋め込める即席のビューなのでインラインビューと呼ばれています。

 

クエリ結果による違い

単一行副問合せ:サブクエリが1行のみ返すもの

複数行副問合せ:サブクエリが複数行返すもの

単一列副問合せ:サブクエリが1列のみ返すもの

複数列副問合せ:サブクエリが複数列返すもの

 

単一行副問合せは、たとえばサブクエリのWHERE条件に主キーを使っている場合に得られます。主キーを使い=(等価演算子)で比較すれば、自ずと得られるデータは最高でも1件になるでしょう?

逆に、複数行副問合せは、サブクエリのWHERE条件に主キー以外を使ったり、あいまい検索など行うことで得られます。たとえば、従業員マスタから、男性のみという条件でレコードを抽出すると、全体の約半数のレコードが抽出されますよね。これが複数行副問合せです。

単一列、複数列副問合せは、サブクエリのSELECTで指定した列数によって決まります。

SELECT R1 FROM T1 これは単一列ですし、

SELECT R1, …, Rn FROM T2 これは複数列です。

ちなみに、SQL Serverでつかう Transact-SQL では、この複数列副問合せに対する条件が記述できない(未対応?)みたいです。

この列と行、単一と複数ある問合せのパターンですが、実際に使用するのはこれらの組み合わせです。

???と思われるかもしれませんが、よくよく考えると、単一行の問合せには必ず列が付き物ですし、複数列の問合せでも必ず1行以上の行が付き物です。どちらか一方でも0列または0行では問合せになりません。

行列数の組み合わせは下表のようになり、全てのサブクエリが下表のどれか1パターンになります。

 

単一列副問合せ 複数列副問合せ
単一行副問合せ 1行1列副問合せ(値) 1行n列副問合せ(ベクトル)
複数行副問合せ m行1列副問合せ(ベクトル) m行n列副問合せ(行列)

1行1列副問合せ

よく使うパターン、あるテーブルのある値が平均以上のレコードを抽出せよ、みたいな時には、1行1列、つまり値の副問合せを行うことになります。この場合、結果が値なので、通常の比較演算子(=,<>, >, <, >=, <=)を使うことが出来ます。

 

SELECT * FROM テスト集計表

WHERE 合計点数 > (SELECT AVG(合計点数) FROM テスト集計表)

「合計点数より大きい」という意味で > という比較演算子を使っていますね。

 

m行1列副問合せ

一方の複数行副問合せですが、たとえば次の場合に使います。

2テーブル間の差分を取りたくてもMINUS集合演算子が使えない場合(MS Transact-SQLがまさに・・・)
SELECT * FROM T1
WHERE T1.id NOT IN (SELECT T2.id FROM T2)

上のSQLは、idに基づいてT1からT2を引いた結果の問合せです。
※正確にはMINUS演算の代用ではありませんが、同一の結果を期待できます。
さて、このサブクエリ部分は非常にシンプルで、何の条件もありません。つまり、T2のidを全て要求している問合せです。つまり、m行1列の複数行副問合せということになります。
複数行の場合、先ほどのように通常の比較演算子だけでは太刀打ちできません。
WHERE句に “NOT IN” という演算子がありますが、このような論理演算子を使用して条件を書く必要があります。
複数行副問合せの条件に使える論理演算子は、以下のものがあります。
IN, NOT IN, ANY(SOME), ALL, EXISTS, NOT EXISTS
※NOTはただの否定なので根本的な意味に違いはありません
※ANYとSOMEは等価なのでANYのみ記します

IN 評価値 [ NOT ] IN ( サブクエリ | 値1,…,値n )
サブクエリが返す値または値リストのいずれかが評価値と一致すればTRUE
全ての値が評価値と一致しなければFALSE
ANY 評価値 比較演算子 ANY ( サブクエリ )
評価値がサブクエリの値リストに対して一つでも比較演算関係を満たせばTRUE
全て満たさなければFLASE
ALL 評価値 比較演算子 ALL ( サブクエリ )
評価値がサブクエリの値リストに対して全ての比較演算関係を満たせばTRUE
一つでも満たさなければFLASE
EXISTS [ NOT ] EXISTS ( サブクエリ )
サブクエリが1行でも返せばTRUE
何も返さなければFALSE

 

より詳しい使い方は https://msdn.microsoft.com/ja-jp/library/ms189773(v=sql.120).aspx をご覧ください。

 

~追記~ Transact-SQL にはMINUSこそありませんが、EXCEPTという差集合を出す同等の演算子がありました。おかしいと思ったんだ・・・ 無いわけが無いんですよ。

 

n列系の副問合せ

MS SQL Serverでは複数列に対する条件は記述できません。ただし、Oracleなど他のDBMSでは以下のような条件の記述が可能なようです。オレンジ部分は、T1とT2それぞれの列ですがデータ型と列数は同じでなければなりません。

SELECT * FROM T1
WHERE (T1.列1,...,T1.列2) = (
SELECT T2.列1,...,T2.列2
FROM T2
WHERE T2.列3 = 1111)

 

相関副問合せ

ちょっと分かりにくい副問合せを最後に紹介します。

前述のような行数や列数の違いとは独立した副問合せの考え方として
相関副問合せというものがあります。
これは、メインクエリのカラムがサブクエリの抽出条件に使われているもの
つまり、サブクエリの条件がメインクエリのキーに依存しているような状況です。

このような状況では、依存しているメインクエリのキーが変わるたびにサブクエリが実行されます。相関副問合せのSQL例を次に示します。


SELECT * FROM T1
WHERE NOT EXISTS
(SELECT '' FROM T2 WHERE T1.id = T2.id)

実は上のコードは、先ほどのm行1列副問合せの例で示した、2テーブル間の差分をとるSQLと機能的には全く同じです。なので、このSQLもT1とT2で重複しているidのレコードをT1から引いた結果が表示されます。

さてそれは置いといて、このSQLを相関副問合せであると決定付けている部分は、紛れも無くT1.idという部分です。サブクエリの中に、メインクエリのカラムが紛れ込んでいますね。これこそが、メインクエリのカラムがサブクエリの抽出条件に使われているという状況であり、相関副問合せなんです。

このSQLを説明すると次のようになります。※From句でテーブル読み込み・・・ とかは抜かしています

  1. T1テーブルのn番目のidについて、サブクエリ内のT2の全idと比較します
  2. 一致したidが一つでも存在すれば、NOT EXISTS はFALSEとなります
  3. 2番がFALSE、つまり一致するIDがあれば、そのレコードはメインクエリで表示されません。逆に、一つも存在しなければそのレコードは表示されます。
  4. n番のnを一つ増やして最初に戻ります。T1の全レコードを処理したならば終わります。

なんというか、プログラミングでいう2重ループみたいな構造に似ていますね。2重ループのようなカウンタ変数は出てきませんが、実質的にはそのような感じです。

事実、このような相関副問合せは処理するレコード数によってはかなりの時間がかかってしまうほど計算コストの高いクエリなのです。最近のDBMSではオプティマイザーがいい働きをしてくれるので、ある程度は最適化されますが、やはりエンジニア自身がケースバイケース、相関副問合せと付き合うのが良いでしょう。

 

使用例

御託はもうたくさんだ、こっちはどう使えばいいかを知りたいんだ! というせっかちな方もいらっしゃると思うので、使用例を幾つか示します。

また、使用例は不定期に拡充していきますので、現在の内容で終了ではありません。

例1 ある差分