こんにちは、開発エンジニアのRYOTAです。今回はSQLの副問合せについてアウトプットさせていただきます。

副問い合わせとは

副問い合わせとは簡単に言うとSQLの中に登場するSQLのことである。
SQLを使用していると、SELECT文で取得した結果の値でさらにSQLを実行したいことがあるかと思うがそういう時に使えるのが副問い合わせである。

以下のようなテーブルがあるとする。

説明用のため、家計簿テーブルと家計簿集計テーブルの整合性はないものとする。

SELECT
    * 
FROM
    household_account_book 
WHERE
    withdrawal_amount = ( 
        SELECT
            MAX(withdrawal_amount) 
        FROM
            household_account_book
    );

家計簿テーブルの項目を取得するSQL文だが、WHERE句に()で括られたSELECT文がある。
このSQLは()で括られたSELECT文の取得結果を用いて外側のSQLを実行する。

今回の例を具体的に説明すると、()で括られたSQLが10000に変化し、「SELECT * FROM household_account_book WHERE withdrawal_amount = 10000」を実行する形となる。
このような()内のSQLが副問い合わせと言い、サブクエリとも言われる。

副問い合わせの仕組み

先程の例のように副問い合わせを使用したSQLはネスト構造になっており、内側にあるSELECT文から実行されて最終的に1番外側のSQLが実行される(①→②の順番)

②SELECT
    * 
FROM
    household_account_book 
WHERE
    withdrawal_amount = ( 
        ①SELECT
            MAX(withdrawal_amount) 
        FROM
            household_account_book
    );

副問い合わせのパターン

副問い合わせはSELECT文なので、得られる結果としては以下の3パターンが挙げられる。

①単一の値に変化する
②複数の値に変化する
③テーブル形式の複数値に変化する

単一の値に変化する

これは先程の例の通り、SELECT結果が一つの値になる。
個人的にWHERE句で使用することが多いが、SELECTの選択列の部分やUPDATE文のSET句で使用することも可能。

■SELECT文の選択列の例

SELECT
    expense_item
    , memo
    , ( 
        SELECT
            sum_value 
        FROM
            aggregate 
        WHERE
            expense_item = '食費'
    ) AS 過去の合計額 
FROM
    household_account_book 
WHERE
    expense_item = '食費';

出力結果↓

■UPDATE文の例

UPDATE household_account_book 
SET
    withdrawal_amount = ( 
        SELECT
            minimum_value 
        FROM
            aggregate 
        WHERE
            expense_item = '娯楽費'
    ) 
WHERE
    expense_item = '食費';

複数の値に変化する

複数の値を持つので変化するイメージは以下のような感じ。

SELECT expense_item FROM household_account_book WHERE expense_item IN('食費','娯楽費','交通費');
↓↓↓
食費, 娯楽費, 交通費 

こちらはIN句やANY句で使える

■IN句で使用するの例

SELECT
    * 
FROM
    aggregate 
WHERE
    expense_item IN ( 
        SELECT
            DISTINCT expense_item 
        FROM
            household_account_book 
    );

出力結果↓

■ANY句で使用する例

SELECT
    * 
FROM
    aggregate 
WHERE
    expense_item = '食費' 
    AND max_value = ANY ( 
        SELECT
            withdrawal_amount 
        FROM
            household_account_book 
        WHERE
            expense_item = '食費'
    );

出力結果↓

テーブル形式の複数値に変化する

変化のイメージは以下のとおり。

SELECT * FROM household_account_book WHERE expense_item = '食費';

↓↓↓

テーブル形式なので、FROM句で使うことが多い。
SELECT結果をテーブルとすることでSELECT結果の中身を取得するイメージ。

SELECT
    SUBQUERY.memo
FROM
    ( 
        SELECT
            * 
        FROM
            household_account_book 
        WHERE
            expense_item = '食費'
    ) AS SUBQUERY 
WHERE
    withdrawal_amount = 1800;

出力結果↓

その他、insert文にも使用することができる。

どのような時に使うのかイメージが難しいが、VALUES以降の部分で使うことができる

INSERT INTO household_account_book (
    day,
    expense_item,
    memo,
    deposit_amount,
    withdrawal_amount
) SELECT * FROM household_account_book WHERE expense_item = '食費' AND withdrawal_amount = 100000;

今回はSELECT文の取得結果が高級焼肉代の100000円のレコードなので、そのレコードがさらに1個追加されている。
このように、SELECTの取得結果をそのまま全てテーブルにinsertできてしまう。
ただし、副問い合わせの取得結果の列とinsertするテーブルの列が完全に一致している必要がある。
このやり方は厳密には副問い合わせではなくinsertの特殊構文である。

このようにinsertで副問い合わせを使用すれば複数行を1回の実行でレコードの登録が可能となる。(今回の例は1行だけだが、、、)

※10/20更新
insert文で副問い合わせを実務で使うパターンを教えてもらったのでここに残しておく。
そのパターンとはデータを以降したいときである。

家計簿テーブルに新しいカラム「user_name」を追加し、今までのデータも引き継ぎたいとする。
user_nameは末尾ではなくmemoの後ろに追加したいとした場合、普通にカラムを追加する場合だとできない。

そんなときに副問い合わせを使用する。

手順は以下の通り

新規テーブル作成→新規テーブルに向けて旧テーブルのデータを副問い合わせでinsert

詳しく見ていく

まずは新規テーブル作成(家計簿テーブルにuser_nameを追加したもの)

create table copy_household_account_book( 
    day timestamp
    , expense_item varchar (20)
    , memo varchar (50)
    , user_name varchar (20)
    , deposit_amount integer
    , withdrawal_amount integer
); 

作成後、新規テーブルに向けて旧テーブルのデータを副問い合わせでinsert。
user_nameの部分に対応する仮のカラムを適当に作っておく必要はあるので作る。
値はとりあえず適当に入れておけばよい。

insert 
into copy_household_account_book( 
    day
    , expense_item
    , memo 
    , user_name
    , deposit_amount
    , withdrawal_amount
) SELECT
    day
    , expense_item
    , memo
    , 'kitting' AS user_name
    , deposit_amount
    , withdrawal_amount 
FROM
    household_account_book;

insert実行後のcopy_household_account_bookテーブルの中身↓

ちゃんと家計簿テーブルのデータが以降できているのがわかる。
こうすれば何度もinsertを実行せず一発で以降が完了するので便利でよい。

相関副問い合わせ

相関副問い合わせとはネストの内側のSQLで外側のSQLの表や列を使用する手法である。

以下、サンプル

SELECT
    expense_item
    , memo 
FROM
    household_account_book AS a 
WHERE
    EXISTS ( 
        SELECT
            * 
        FROM
            aggregate AS b 
        WHERE
            b.expense_item = a.expense_item
    );

上記のように、副問い合わせ部分で外側の列を使用することができる。

相関副問い合わせはサンプルのように「他のテーブルに値が登場する行のみ取得したい」場合に、EXISTS演算子と一緒に使用されることが多い。

注意点として、相関副問い合わせは通常の副問い合わせとは仕組みが異なる。

通常の副問い合わせはネストの内側のSQLを一回処理→外側のSQLを1回処理という流れであるが、相関副問い合わせはネストの外側のSQLを実行し、取得した行の1行ずつ副問い合わせの条件にセットしてSQLを実行するようになっている。
そのため、DBへの負担はそれなりに増加してしまう。