はじめに
今回はSQL勉強会の第二回目の発表会となります。
「スッキリわかるSQL入門 第2版」の第7章(p203~)で「副問い合わせ」について学習した内容をまとめてみます。
副問い合わせ
副問い合わせとは、「あるSQL文の内部に入っているSELECT文」のことで、サブクエリとも言います。
記述するときには丸カッコでくくります。また、クエリ(query)とは問い合わせという意味です。
例として、あるテーブルAから「最も大きな出費の費目と金額を求める」処理を行います。
まず、副問い合わせを利用しない場合で記述してみます。
テーブルA
| 日付 | 費目 | 出金額 |
|---|---|---|
| 2021-07-02 | 食費 | 600 |
| 2021-07-10 | 教養娯楽費 | 4000 |
| 2021-07-18 | 水道光熱費 | 8700 |
テーブルAから出金額の最大値を求めます。
SELECT MAX(出金額) FROM テーブルA --(1)
次に(1)で取得した金額を条件式に記述して費目と金額を求めます
SELECT 費目, 出金額 FROM テーブルA
WHERE 出金額 = 【 (1)で取得した金額 】 --(2)
これらを実行すると求めたい費目と出金額を取り出すことができます。
| 費目 | 出金額 |
|---|---|
| 水道光熱費 | 8700 |
次は副問い合わせを使って記述します。
SELECT 費目, 出金額 FROM テーブルA
WHERE 出金額 = ( SELECT MAX(出金額) FROM テーブルA )
この文中にはSELECTが2回出てきています、丸括弧で囲っているのが副問い合わせ(サブクエリ)です。
最初に副問い合わせの部分がDBMSによって処理され、この場合だと「8700」に置き換わります。
つまり、副問い合わせが実行されると具体的な値に化けます。そのあとは化けた値が外側のSQL文で利用されることになります。
副問い合わせの3パターン
副問い合わせの中身はSELECT文であり、実行して得られる結果には3つのパターンがあります。
以下にまとめます。
① 検索結果が1行1列になる副問い合わせ
これは先程の例のように、検索結果が1行1列の値になります。副問い合わせを実行すると一つの値に化けます。
SELECT文の選択列リストやUPDATE文のSET句、WHERE句の条件式などに記述できます。
例 (SELECT....) → 8700
- SET句で利用する場合
テーブルB
| 日付 | 費目 | 出金額 |
|---|---|---|
| 2021-07-02 | 食費 | 4000 |
| 2021-07-10 | 教養娯楽費 | 4500 |
| 2021-07-18 | 水道光熱費 | 8700 |
| 2021-07-20 | 居住費 | 70000 |
| 2021-07-22 | 食費 | 1000 |
UPDATE テーブルC
SET 平均 = (SELECT AVG(出金額)
FROM テーブルB
WHERE 出金額 > 0
AND 費目 = '食費')
WHERE 費目 = '食費'
この処理では、最初に副問い合わせが処理されます。
副問い合わせの結果が2500になります。
したがってテーブルCが仮にあるとすると、食費の平均が2500に更新されます。
- 選択列リストで利用する場合
「dokoQL」というサービスを使ってみます。
家計簿アーカイブ
家計簿集計
SELECT 日付,メモ,出金額
(SELECT 合計 FROM 家計簿集計
WHERE 費目 = '居住費') AS 過去の合計額
FROM 家計簿アーカイブ
WHERE 費目 = '居住費'
実行すると
まず、副問い合わせが処理され、家計簿集計テーブルから居住費の合計が取得されます。
この合計には別名として「過去の合計額」が付きます。最後に家計簿アーカイブテーブルから費目が居住費の行を取り出しています。
つまり、この場合は「家計簿集計テーブルの居住費に関する合計額を取得する」SQL文になっています。
② 検索結果が単一列(n行1列)の値になる副問い合わせ
この場合はSQL文中で「複数の値を列挙する」場所の代わりとして使われます。
IN,ANY,ALL演算子を用いた条件式が代表的です。また、複数の値との判定をするWHERE句の条件式や、SELECT文のFROM句にも書くことができます。
- IN演算子を使う場合
IN演算子は、値が括弧のなかの複数の値のどれかに合致するかを判定する演算子です。
SELECT * FROM 家計簿アーカイブ
WHERE 費目 IN (SELECT DISTINCT 費目 FROM 家計簿)
家計簿テーブル
これを実行すると以下のようになります。
家計簿の費目と合致する行を、家計簿アーカイブから取り出すという意味になります。
- ANY/ALL演算子を使う場合
SELECT * FROM 家計簿
WHERE 費目 = '教養娯楽費'
AND 出金額 > ANY (SELECT 出金額 FROM 家計簿アーカイブ
WHERE 費目 = '教養娯楽費')
これは家計簿アーカイブの費目が教養娯楽費の出金額をまず取り出し、それが家計簿テーブルの教養娯楽費の出金額よりも大きくなる行を取り出しています。
- 副問い合わせにNULLが含まれている場合
SELECT * FROM テーブル名
WHERE 費目 NOT IN ('食費', '教養娯楽費', '給料', NULL)
NOT IN : 括弧内に並べられた値のどれとも一致しない場合に真となる。 <>ALLと同じ
実行するとNULLになる。
右辺に一つでもNULLが含まれているとNOT IN 演算子による結果はNULLになります。
こういう場合はNULLを除外して考えます。
SELECT * FROM テーブル①
WHERE 費目 IN (SELECT 費目 FROM テーブル②
WHERE 費目 IS NOT NULL)
または
SELECT * FROM テーブル①
WHERE 費目 (SELECT COALESCE(費目,'NULLが入ります') FROM テーブル②
③ 検索結果が表形式(n行m列)になる場合
表を記述することのできる、SELECT文のFROM句やINSERT文に記述する
- FROM句で使う場合
SELECT SUM(SUB.出金額) AS 出金額合計
FROM (SELECT 日付, 費目, 出金額
FROM 家計簿
UNION
SELECT 日付, 費目, 出金額
FROM 家計簿アーカイブ
WHERE 日付 >= '2017-12-01'
AND 日付 <= '2017-12-31') AS SUB
括弧内のSELECT文から処理される。
家計簿テーブルから取り出し、それと家計簿アーカイブテーブルの12月分をUNION演算子で足し合わせる。
「SUB」という別名を付けてあげることで、外側のSELECT文の取得する対象として利用しています。
- INSERT文で使う場合
INSERT INTO テーブル①(費目, 合計, 平均)
SELECT 費目, SUM(出金額), AVG(出金額)
FROM テーブル②
WHERE 出金額 > 0
GROUP BY 費目
括弧でくくられていないので厳密にはINSERT文の特殊な構文をして扱われる。
SELECTの検索結果がそのまま登録する値となっている。
まとめ
- 副問い合わせ
SQL文中の別のSELECT文を記述することができて、これを副問い合わせ(サブクエリ)という
副問い合わせは何らかの値に置き換わり、括弧でくくられたSELECT文から処理される
- 副問い合わせのパターン
副問い合わせの結果が1行１列の単一行になる
副問い合わせの結果がn行１列の複数行になる
副問い合わせの結果がn行m列の表形式になる
参考文献
書籍『スッキリわかるSQL入門第2版ドリル222問付き！』