サブクエリを有効活用してSQLマスターになろう

ビュー

サブクエリの話をするにはビューの話は避けて通れない。

仕様

ビューとはSELECT文を保存したもの。
SELECT文に対して名前を付けることで、テーブルのように呼び出すことができる。
ビューを呼び出すと保存しておいたSELECT文が実行され、一時的に仮想のテーブルを作る。

構文

CREATE

CREATE VIEW <view_name>
    <col_name1>, <col_name2>, ...
AS
<SELECT STATEMENT>

SELECT

SELECT
    <col_name1>, <col_name2>, ...
FROM
    <view_name>

DELETE

DROP VIEW <view_name>

例

pop_sex

pref_name	sex	population
東京	1	250
東京	2	250
神奈川	1	200
神奈川	2	100
福岡	1	150
福岡	2	100
埼玉	1	100
埼玉	2	150
長崎	1	20
長崎	2	30
千葉	1	120
千葉	2	130
山形	1	50
山形	2	50

CREATE

CREATE VIEW big_pref
    pref_name, 
    pop
AS
SELECT
    pref_name, 
    SUM(population) as pop
FROM
    pop_sex
GROUP BY
    pref_name
HAVING
    SUM(population) >= 300
;

SELECT

SELECT
    pref_name, 
    pop
FROM
    big_pref
;

pref_name	pop
東京	500
神奈川	300

DELETE

DROP VIEW big_pref
;

ビューに対する検索の挙動

1. ビューに定義されたSELECT文が実行される
2. その結果に対して、ビューをFROM句に指定したSELECT文が実行される

サブクエリ

ビュー定義のSELECT文を、そのままFROM句の中に入れたもの。
ビューでもサブクエリでも得られる結果は等しい。

構文

SELECT
    <col_name1>, <col_name2>, ...
FROM
    (
    SELECT
        <col_name1>, <col_name2>, ...
    FROM
        <table_name>
    ) AS <sub_query_name>
;

例

SELECT
    pref_name, 
    pop
FROM
    (
    SELECT
        pref_name, 
        SUM(population) as pop
    FROM
        pop_sex
    GROUP BY
        pref_name
    HAVING
        SUM(population) >= 300
    ) AS big_pref
;

サブクエリに対する検索の挙動

1. サブクエリに定義されたSELECT文が実行される
2. その結果に対して、サブクエリをFROM句に指定したSELECT文が実行される

スカラサブクエリ

必ず１行１列だけの戻り値を返すサブクエリ

や != などの比較演算子の入力とすることができる

WHERE句では集約関数(AVGなど)を使えないので、スカラサブクエリで代用する。
(WHERE句の方が集約関数よりも評価が早いため)

相関サブクエリ

サブクエリにWHERE句を追加し、サブクエリのカラムとクエリのカラムを比較することで、相関サブクエリとなる。
集合全体ではなく部分集合に限定して比較したい場合に用いる。
つまりサブクエリで複数取得されたスカラ値(ベクトル)に対して、比較するクエリのカラムを指定できる。

ウィンドウ関数を優先して使えば、大抵の場合は相関サブクエリを使う必要が無い

デバッグもしづらく、パフォーマンスも悪いためできる限り使わないのがベター。

仕様

クエリとサブクエリで比較をする際、条件に合致するスカラ値とそれぞれ比較ができる。

構文

SELECT
    <col_name1>, <col_name2>, ...
FROM
    <table_name> AS T1
    (
    SELECT
        <col_name1>, <col_name2>, ...
    FROM
        <table_name> AS T2
    WHERE
        T1.col_nameX = T2.col_nameX
    )
;

例

下記のようなテーブルに対して、「性別ごとの平均よりも多い県のみ表示したい」ことを考える。
テーブル全体の平均ではなく、性別ごとの平均である。

pop_sex

pref_name	sex	population
東京	1	250
東京	2	250
神奈川	1	200
神奈川	2	100
福岡	1	150
福岡	2	100
埼玉	1	100
埼玉	2	150
長崎	1	20
長崎	2	30
千葉	1	120
千葉	2	130
山形	1	50
山形	2	50

SELECT
    pref_name, sex, population
FROM
    pop_sex AS S1
WHERE
    population > (
                    SELECT
                        AVG(population)
                    FROM
                        pop_sex AS S2
                    WHERE
                        S1.sex = S2.sex
                    GROUP BY
                        sex
                  )
;

まずサブクエリの結果を以下に示す。

AVG(population)
127.14
115.71

↑の１行目が男の平均人口、２行目が女の平均人口である。
それぞれ適切な平均人口とpop_sexテーブルを比較し、平均より上回るレコードのみが表示される。
最終的な結果は以下となる。

pref_name	sex	population
東京	1	250
東京	2	250
神奈川	1	200
福岡	1	150
埼玉	2	150
千葉	2	130