相関副問合せ(Oracle MASTER Silver SQL 2019の資格取得を目指して)

こんにちは、辻野です。

現在、Oracle MASTER Silver SQL 2019の資格取得を目指して勉強しております。
覚えたことの理解を深めるためのアウトプットとして記事を作成しました。
皆様のご参考になれば幸いです。

今回は相関副問合せについて説明します。

相関副問合せとは？

相関副問合せとは、副問合せの中で主問合せの表の列を参照している問合せのことです。

「社員の現在年収について、過去の最高年収より低い状態の社員を表示する」SQL文を例に相関副問合せを利用してみたいと思います。

相関副問合せを利用したSQLの例

• emp_annual_incomeテーブル（現在の年収が登録されています）

• emp_annual_income_historyテーブル（前年度までの年収が登録されています）

下記SQL文を実行することで、現在の年収が自分の過去の最高年収よりも低い社員を表示することができます。

SELECT id, name, annual_income FROM emp_annual_income eai
WHERE annual_income < 
    (SELECT MAX(annual_income) FROM emp_annual_income_history eaih
    WHERE eaih.emp_annual_income_id = eai.id);  

SQL文を詳しく確認します。
1行目のSQL文で、現在年収のテーブルからデータを取得しています。

SELECT id, name, annual_income FROM emp_annual_income eai

2行目～4行目のSQL文で、過去の年収から最大値(MAX(annual_income))を検索しています。
現在年収のデータが最大値より小さい(<)と指定されているため、最大値より小さかった場合にデータが表示されます。

WHERE annual_income < 
    (SELECT MAX(annual_income) FROM emp_annual_income_history eaih
    WHERE eaih.emp_annual_income_id = eai.id);  

実行結果となります。

idが「1001」の田中さんは2023年より、現在の年収が低いため表示されました。
idが「1003」の山田さんは2024年より、現在の年収が低いため表示されました。

SQL文の処理詳細

「相関副問合せを利用したSQLの例」で記載したSQL文の処理内容を見てきたいと思います。

最初にSQL文の副問合せを考えます。 3～4行目の括弧の中のSQL文です。
理解しやすいように田中さんに対する処理を追ってみます。

SELECT MAX(annual_income) FROM emp_annual_income_history eaih
WHERE eaih.emp_annual_income_id = eai.id;

主問合せのemp_annual_incomeのid (eai.id) が、田中さんのidで処理されます。（他のidについても並行で処理されます）

SELECT MAX(annual_income) FROM emp_annual_income_history eaih
WHERE eaih.emp_annual_income_id = 1001;

これで田中さんの最大の過去年収が取得されました。

次に、先ほどの副問合せの結果から田中さんの現在の値と比較します。
SQL文の1行目がそのままで、2行目を副問合せが実行された結果に置き換えて考えます。

SELECT id, name, annual_income FROM emp_annual_income eai
WHERE annual_income < 【idが1001の過去の最高年収】; -- idが1001の過去の最高年収は5000000

idが1001のデータの結果が戻ります。
idが1002、1003のデータについても、それぞれ同じ処理を行います。
副問合せの中の、WHERE eaih.emp_annual_income_id = eai.idの結合で、どこのidと紐付いているか判断されています。

最後に

最後まで読んでいただきありがとうございました。
今回の記事を作成するにあたり、参考にした資料は下記の通りです。

参考書籍

オラクルマスター教科書 Silver SQL Oracle Database SQL
[著者] 株式会社コーソル 企画&マーケティング部 渡部亮太、桝井智行、峯岸隆一
[監修] 日本オラクル株式会社
[発行人] 佐々木幹夫 [発行所] 株式会社翔泳社 [発行日] 2021年9月13日(初版)
[参考ページ] p.259-264