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

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

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

## 構文
### CREATE
```sql
CREATE VIEW <view_name>
    <col_name1>, <col_name2>, ...
AS
<SELECT STATEMENT>
```

### SELECT
```sql
SELECT
    <col_name1>, <col_name2>, ...
FROM
    <view_name>
```

### DELETE
```sql
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
```sql
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
```sql
SELECT
    pref_name, 
    pop
FROM
    big_pref
;
```

| pref_name | pop |
| ---- | ---- |
| 東京 | 500 |
| 神奈川 | 300 |

### DELETE
```sql
DROP VIEW big_pref
;
```

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


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

## 構文

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

## 例
```sql
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文が実行される
1. その結果に対して、サブクエリをFROM句に指定したSELECT文が実行される

## スカラサブクエリ
必ず１行１列だけの戻り値を返すサブクエリ
> や != などの比較演算子の入力とすることができる

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



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

#### ウィンドウ関数を優先して使えば、大抵の場合は相関サブクエリを使う必要が無い
デバッグもしづらく、パフォーマンスも悪いためできる限り使わないのがベター。

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

## 構文

```sql
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 |

```sql
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 |