前回の続きで第七章の主要点をまとめていきたいと思います。 (※具体的なテーブル表など割愛させてもらうことが多いです)

今回は「サブクエリ」についてです。

サブクエリとは、SQLの中で作成される一時的なテーブルということを抑えましょう。業務中では、困るとよくサブクエリを使用したりする癖がわたしはありますが、本当に効率的なのかこの章では考えてみます。

先に前提をはっきり意識するために以下を覚えておきます。

テーブル 永続的且つデータを保持する。
VIEW(ビュー) 永続的だがデータは保持しない。アクセスのたびにSELECT文が実行される。
サブクエリ 非永続的で生存期間(スコープ)がSQL文の実行中に限られる。

サブクエリの問題

サブクエリはデータを保持しないことを意識する

実態的なデータを保持しないということで、当たり前ですがSELECT実行にかかるコストが上乗せされます。このサブクエリが複雑なほどコストは落ちることになります。

面倒になってくるとサブクエリが多少複雑でも欲しい結果が得られれば「これでいいや！」ってよくなりますね笑

また、オプティマイザがクエリを解析するために必要な情報がサブクエリの結果からは得られません。

サブクエリが有効なときはどういうときなのか、ここからは考えてみます。

サブクエリが有効なとき

テーブル Receiptsを想定してみます。このテーブルは顧客の購入明細を記録するテーブルで、連番(seq)列は顧客の古い購入ほど小さな値が振られています。ここで最小の連番(seq)の金額(price)を求めることを考えてみると、ある顧客に対して一番古い購入履歴を見つけることと同義です。

このSQL文を考えるとき、テーブルへのアクセスを1回にします。I/Oを考慮します。

それと、ウィンドウ関数、ROW_NUMBERを使います。

ROW_NUMBERで行に連番を振り、常に最小値を1にすることで、seq列の最小値が不確定という問題を対処します。

SELECT cust_id, seq, price
FROM ( SELECT cust_id, seq, price,
                            ROW_NUMBER () OVER ( PARTITION BY cust_id
                                                                       ORDER BY seq) AS row_seq
              FROM Receipts ) WORK
WHERE WORK.row_seq = 1;

こうしたクエリでないとテーブルへのアクセスが2回になったり、クエリ自体が読みにくくなってしまいます。

また、テーブル Companiesテーブルとテーブル Shopsテーブルを想定します。 Companiesには、co_cd(会社コード)とdistrict(地域)があって、 Shopsには、co_cd(会社コード)、shop_id(事業所ID),emp_nbr(従業員数)、main_flg(主要事業所フラグ)があります。

1:Nの親子関係があるテーブルだと思って下さい。

ここから会社ごとの主要事業所の従業員数を含む結果(会社の地域を含めた)を得たいとします。

この結果を求めるには2通りのSQLが考えられます。