SQL – サブクエリ（副問い合せ）｜Chopesu Blog - 文系・未経験でエンジニアに転身した元営業マンのエンジニアライフ

Contents

副問合せ

副問合せ

SQLには、SQL文の内部に別のSELECT文を記述する「副問い合せ」という機能がある。
これは小学校の算数でよく見る計算式をイメージすると分かりやすい。

x = 2 × 3
y = 4 × 5
z = 3 × 7
a = x + y + z
a = 47

この計算式、変数を使っているという意味では見やすいが、5行も使っており少々長い。
これを短くしようと思ったらこうするはず

a = (2 × 3) + (4 × 5) + (3 × 7)
a = 47

SQL文でもこれと同じようなことができる。それが副問い合せ。
つまり、1つのSQL文（計算式でいう a ）の中に、1つ以上のSQL文（計算式でいう(2 × 3)など）を入れることができるという機能。これを入れ子構造、ネストとも呼ばれる。副問い合せ（ネスト）している部分はかっこ（）でくくること。

副問合せの3パターン

パターン①検索結果が1行1列の値になる副問い合せ

■UPDATE文で使ってみる。

UPDATE 家計簿集計
SET 合計 = ( SELECT SUM(出金額)
　　　　　　FROM 家計簿
　　　　　　WHERE 出金額 > 0
　　　　　　AND 費目 = '交通費')  -- これはUPDATE文の中にSELECT文が含まれている（ネストされている）
WHERE 費目 = '交通費'

UPDATE 家計簿集計

SET 合計 = ( SELECT SUM(出金額)

　　　　　　FROM 家計簿

　　　　　　WHERE 出金額 > 0

　　　　　　AND 費目 = '交通費') -- これはUPDATE文の中にSELECT文が含まれている（ネストされている）

WHERE 費目 = '交通費'

上記のSQL文だと、家計簿の交通費の合計金額を集計して、家計簿集計の費目=’交通費’に入れてテーブルを更新できる。

■SELECT文で使ってみる

SELECT 日付, メモ, 出金額,
　　　　(SELECT 合計 FROM 家計簿集計
　　　　WHERE 費目 = '交通費') AS 過去の合計額
FROM 家計簿
WHERE 費目= '交通費'

SELECT 日付, メモ, 出金額,

　　　　(SELECT 合計 FROM 家計簿集計

　　　　WHERE 費目 = '交通費') AS 過去の合計額

FROM 家計簿

WHERE 費目= '交通費'

パターン②検索結果がn行1列の複数の値になる副問い合せ

■IN演算子で使ってみる

SELECT *
FROM 家計簿集計
WHERE 費目 IN(SELECT DISTINCT 費目 FROM 家計簿) -- 家計簿テーブルから重複を除外した費目

SELECT *

FROM 家計簿集計

WHERE 費目 IN(SELECT DISTINCT 費目 FROM 家計簿) -- 家計簿テーブルから重複を除外した費目

■ANY/ALL演算子で使ってみる

SELECT *
FROM 家計簿
WHERE 費目 = '食費'
AND 支出額 < ANY(SELECT 支出額 FROM 1月の家計簿
　　　　　　　　　WHERE 費目 = '食費')  -- 1月の家計簿の食費のいずれかよりも支出額が小さければという意味

SELECT *
FROM 家計簿
WHERE 費目 = '食費'
AND 支出額 < ALL(SELECT 支出額 FROM 1月の家計簿
　　　　　　　　　WHERE 費目 = '食費')  -- 1月の家計簿の食費のどれよりも支出額が小さければという意味

SELECT *

FROM 家計簿

WHERE 費目 = '食費'

AND 支出額 < ANY(SELECT 支出額 FROM 1月の家計簿

　　　　　　　　　WHERE 費目 = '食費') -- 1月の家計簿の食費のいずれかよりも支出額が小さければという意味

SELECT *

FROM 家計簿

WHERE 費目 = '食費'

AND 支出額 < ALL(SELECT 支出額 FROM 1月の家計簿

　　　　　　　　　WHERE 費目 = '食費') -- 1月の家計簿の食費のどれよりも支出額が小さければという意味

＜補足＞
NOT IN演算子または<>ALLで判定する副問い合わせの結果にNULLを含まれていた場合、全体の結果もNULLになってしまうので、下記のようにしてNULLを除外する。

SELECT *
FROM  家計簿集計
WHERE 費目 IN(SELECT 費目 FROM 家計簿
　　　　　　　　WHERE 費目 IS NOT NULL)  -- WHERE句でNULLでない費目を指定

SELECT *
FROM 家計簿集計
WHERE 費目 IN(SELECT COALESCE(費目,　’不明’) FROM 家計簿)  -- COALESCE関数を使って代替値を入れる

SELECT *

FROM 家計簿集計

WHERE 費目 IN(SELECT 費目 FROM 家計簿

　　　　　　　　WHERE 費目 IS NOT NULL) -- WHERE句でNULLでない費目を指定

SELECT *

FROM 家計簿集計

WHERE 費目 IN(SELECT COALESCE(費目,　’不明’) FROM 家計簿) -- COALESCE関数を使って代替値を入れる

パターン③検索結果が表の結果となる副問い合せ

FROM句で使ってみる

SELECT SUM(SUB.支出額) AS 支出額合計
FROM (SELECT  日付,費目,支出額
　　　　FROM 家計簿
　　　　UNION　　　　　　　　　　　　　　　　　　-- 家計簿と家計簿アーカイブの1月1日〜1月31日までのデータを結合
　　　　SELECT 日付,費目,支出額
　　　　FROM 家計簿アーカイブ
　　　　WHERE 日付 &gt;= '2018-01-01'
　　　　AND　　日付 &lt;= '2018-01-31' ) AS SUB  -- SUBという名前の表として副問い合せの結果を取得

#解説
1行目の(SUB.支出額)はFROM句で書かれている副問い合せの結果、得られた表のなかの支出額の合計という意味

SELECT SUM(SUB.支出額) AS 支出額合計

FROM (SELECT 日付,費目,支出額

　　　　FROM 家計簿

　　　　UNION　　　　　　　　　　　　　　　　　　-- 家計簿と家計簿アーカイブの1月1日〜1月31日までのデータを結合

　　　　SELECT 日付,費目,支出額

　　　　FROM 家計簿アーカイブ

　　　　WHERE 日付 >= '2018-01-01'

　　　　AND　　日付 <= '2018-01-31' ) AS SUB -- SUBという名前の表として副問い合せの結果を取得

#解説

1行目の(SUB.支出額)はFROM句で書かれている副問い合せの結果、得られた表のなかの支出額の合計という意味

INSERT文で使ってみる

INSERT INTO 家計簿集計(費目,合計,平均,回数)
SELECT 費目, SUM(出金額), AVG(出金額),0
FROM 家計簿
WHERE 出金額 > 0
GROUP BY 費目

/* 通常であれば、INSERT INTOのあとはVALUESを記述するが、この副問い合せを使うときだけは
特殊構文の扱いになり、VALUESがなくてもOKになる。*/