相関副問合せとは？

相関副問合せの意味

副問合せの中で外側の問合せの列を参照するもの。外側の行1件ごとに副問合せが評価される。

相関副問合せの具体例

各部門で最も給与が高い社員を求めるとき、副問合せ側で外側の部門コードを参照して同じ部門内の最大給与と比較する。

相関副問合せは試験でどう引っ掛けられる？

「グループ内の最新・最古・最大・最小の1件」を求める典型パターン。「自分より条件を満たす行が存在しない」という否定形（NOT EXISTS）で書くこともできる。

相関副問合せと関連する用語

• EXISTS述語
• 窓関数

相関副問合せが出た過去問

• 次のSQL文と同じ検索結果が得られるSQL文はどれか。 SELECT DISTINCT TBL1.COL1 FROM TBL1 WHERE COL1 IN (S…

  正解：SELECT DISTINCT TBL1.COL1 FROM TBL1 WHERE EXISTS (SELECT * FROM TBL2 WHERE TBL1.COL1 = TBL2.COL1)

  要点：IN述語の副問合せは相関EXISTSに等価変換できる

  IN述語による副問合せは、外側の行の値が副問合せの結果集合に含まれるかを判定するもので、相関副問合せを使ったEXISTS述語に書き換えられる。どちらもTBL2に同じCOL1の値が1件でもあればその行を残すという意味になり、結果は一致する。

  出典：平成28年度 春期 データベーススペシャリスト試験 am2 問9（IPA）

• “社員”表に対して、SQL文を実行して得られる結果はどれか。ここで、実線の下線は主キーを表し、表中の'NULL'は値が存在しないことを表す。 〔社員〕 社員コー…

  正解：社員コード: S002, S004, S007

  要点：NOT EXISTSの相関副問合せで「部下を持たない社員」を抽出

  この副問合せは「自分の社員コードを上司として持つ社員が存在するか」を調べており、NOT EXISTSで否定しているので、誰の上司にもなっていない社員だけが選ばれる。表では上司欄に現れるコードが部下を持つ社員なので、そこに一度も現れない社員が結果になる。

  出典：平成30年度 春期 データベーススペシャリスト試験 am2 問5（IPA）

• “社員”表から、男女それぞれの最年長社員を除く全ての社員を取り出すSQL文とするために、aに入れる字句はどれか。ここで、“社員”表の構造は次のとおりであり、実線…

  正解：SELECT MIN(生年月日) FROM 社員 AS S2 WHERE S1.性別 = S2.性別

  要点：相関副問合せで同性内の最年長を求め、それより後を選ぶ

  性別が同じ社員の中で最も早い生年月日を相関副問合せで求め、それより生年月日が後の社員だけを取り出せば、男女それぞれの最年長者が除かれる。外側の行の性別を副問合せの条件に渡す相関が要点である。

  出典：平成30年度 春期 データベーススペシャリスト試験 am2 問10（IPA）

• "社員"表に対して、SQL文を実行して得られる結果はどれか。ここで、実線の下線は主キーを表し、表中のNULLは値が存在しないことを表す。 社員(社員コード(主キ…

  正解：S002, S004, S007

  要点：NOT EXISTSの相関副問合せで部下を持たない行を抽出

  相関副問合せは、外側の行の社員コードを上司として持つ行が存在するかを調べている。NOT EXISTSなのでそれが1件もない社員、すなわち部下を1人も持たない社員が選ばれる。上司列に現れるのはS001・S003・S005・S006なので、残るS002・S004・S007が答えになる。

  出典：令和2年度 10月 データベーススペシャリスト試験 am2 問8（IPA）

• "社員"表から、男女それぞれの最年長社員を除く全ての社員を取り出すSQL文とするために、aに入れる字句はどれか。ここで、"社員"表の構造は次のとおりであり、実線…

  正解：SELECT MIN(生年月日) FROM 社員 AS S2 WHERE S1.性別 = S2.性別

  要点：相関副問合せで同一グループ内の最小値と比較する

  外側の行と同じ性別に限定した相関副問合せで最小の生年月日、すなわちその性別の最年長者の生年月日を求め、それより後に生まれた社員だけを残せば、男女それぞれの最年長者を除いた結果になる。外側の別名S1を副問合せの条件で参照している点が要である。

  出典：令和2年度 10月 データベーススペシャリスト試験 am2 問10（IPA）