【試して覚える】SQLite で Select 文入門（副問い合わせ）

前回の記事の最後に、select 文をカッコ で括りましたが、この方法を使うと、もっと複雑な検索が行えるようになります。

これを副問い合わせ（又はサブクエリー）と呼ぶのですが、今回はこの副問い合わせについて解説したいと思います。

副問い合わせとは

メインのSQL（正確にはQuery）に、付随する形で動作するSQLが副問い合わせになります。
メインのSQLが存在しているので、副という名前が付いています。

前回記事の最後に次のような記述を紹介しましたが、left join ～ の部分が、今回の副問い合わせとなる部分です。

select * from
(
select
t1.売上日
,t2.メーカー
,sum(t1.数量) as 数量
,sum(t1.数量) * t2.単価 as 売上金額
from 売上データ t1
left join 商品マスタ t2 on t1.商品コード=t2.商品コード
group by t1.売上日,t2.メーカー
) where 数量 >= 10

上記のSQLでは left join 商品マスタ t2 on t1.商品コード=t2.商品コード の結合により、商品マスタから 「メーカー」 と「単価」を参照しています。

Joinを副問い合わせで書き換える場合、「メーカー」と「単価」の参照部分を副問い合せ(select "メーカー" from 商品マスタ t2 where t2.商品コード=t1.商品コード)　で置き換える必要があるため、次の様になります。

select * from
(
select
売上日
,(select "メーカー" from 商品マスタ t2 where t2.商品コード=t1.商品コード) as "メーカー"
,sum(数量) as 数量
,sum(数量) * (select 単価 from 商品マスタ t2 where t2.商品コード=t1.商品コード) as 売上金額
from 売上データ t1
group by 売上日,(select "メーカー" from 商品マスタ t2 where t2.商品コード=t1.商品コード)
) where 数量 >= 10

この例では副問い合わせを使うよりJoin で済ませる方がシンプルになりますが、副問い合わせは select 句だけではなく、group by や where など、あらゆる箇所で利用できることが分かるかと思います。

Joinと副問い合わせはともに別テーブルの情報を引用する点では同じですが、それぞれメリットデメリットがあります。従って使い分けが大切です。

	テーブル結合（Join）	副問い合わせ
メリット	1つのテーブルから複数のカラムを参照したい場合に簡素に書ける。  1つのテーブルから複数のカラムを参照する場合、処理速度の低下が少ない。	誤った条件により1対多で結合された場合、エラーになるので気づきやすい。  カラムごとに個別の複雑な結合条件を記述することができる。  エイリアス名は副問い合わせにのみ付ければよい。
デメリット	誤った条件により1対多で結合された場合、結合元のテーブル件数を超える結果が返されるが、エラーにならないので気づきにくい。  一部のJoinを修正することで、他のJoinや最終の結合結果に影響を及ぼすことがある。  Joinを多用すると、最終的に返される結果がどうなるか分かり難くなる  全てのカラム名にテーブルエイリアス名（t1,t2など）を付ける必要がある。	１つのテーブルから複数のカラムを参照する場合、記述が煩雑になると共に、処理速度も低下する。  複雑な副問い合わせを多用すると、SQLが全体的に長く、かつ読みにくくなる場合がある。

副問い合わせを使う時のコツ

SQLにおいての副問い合わせは、１つのカラムとして扱われます。従って次の点に注意する必要があります。

1. 副問い合わせから返す値（select に記載するカラム又は値）は1個だけ
2. 副問い合わせのSQLは前後のカッコ ( ) で括る
3. 副問い合わせのSQLにはAS句を使って別名を付ける
4. 正となるSQLのカラムと副問い合わせのSQLのカラムを紐づける
5. 紐づけた条件に一致するレコードは、必ず一意になるようにする

もう少し具体的に説明すると、正となるSQLで抽出された1件が、必ず副となるSQLの1件と紐づくように、Joinと同様の条件を記述します。

（select 製品名 from 商品マスタ t2 where t1.商品コード = t2.商品コード） AS 製品名

Join の場合は次のように書きますが、 where か on かの違いだけです。

join 商品マスタ t2 on t1.商品コード = t2.商品コード

select で使う副問い合わせ

例えば、売上データと一緒に、商品名を表示することを考えてみましょう。

join の場合は次の様になります。

select
t1.売上日
,t2.商品コード
,t2.製品名
,t1.数量
from 売上データ t1
left join 商品マスタ t2 on t1.商品コード = t2.商品コード

これを副問い合わせで行うと、次のようになります。

select
売上日,
商品コード,
(select 製品名 from 商品マスタ t2 where t1.商品コード = t2.商品コード) as 製品名,
数量
from 売上データ t1

では、さっそくツールで試してみましょう。

結果は以下の通りです。

あるテーブルから、1カラムだけ抽出項目に含ませたい時は副問い合わせを使うメリットはありますが、2個以上だと (select ~)　を数分だけ記述する必要が生じるため、検索速度の低下や記述が煩雑になるなどデメリットが多くなりますので、必ずしも便利かと言うとそうでもありません。

where で使う副問い合わせ

where 区で用いる副問い合わせも、select で使うものと同じ書き方になります。

異なる点は、絞り込みたい条件を副問い合わせに記述するという点です。

例えば、売上データに対して、特定の商品コードだけを抽出することを考えます。
商品コードが A000001 から A000016 までのデータを抽出したい場合

select * from 売上データ where 商品コード between 'A000001' and 'A000016'

というSQLになります。

この　商品コード between ~　の部分を副問い合わせで書くことになるわけですから、副問い合わせで返される値が、意図した抽出結果である必要があるのです。

では、どんな時に where 区で副問い合わせを用いるのでしょうか。

よく使われるのが、副問い合わせで別テーブルから抽出した結果を使って絞り込むようなパターンです。

例えば、”「メーカー」が SONY である売上データのみ抽出する”　ような場合です。

売上データに「メーカー」のカラムが存在しない場合、商品マスタの製品名を使わざるを得ません。

このような場合は売上データと商品マスタをJoin してから検索することになります。

select
t1.売上日,t1.商品コード,t1.数量 from 売上データ t1
left join 商品マスタ t2 on t2.商品コード = t1.商品コード
where メーカー = 'SONY'

副問い合わせを使うと、次の様に記述することが出来ます。

select
t1.売上日,t1.商品コード,t1.数量
from 売上データ t1
where 商品コード in (select 商品コード from 商品マスタ where メーカー='SONY')

今度は in が登場しました。

in は、列記した値と一致するレコードを抽出するという機能です。

商品コード in ('A000001','A000002','A000003','A000016’) という具合に、複数の値を列記することで、商品コードがいづれかと一致したレコードを抽出してくれます。

(select 商品コード from 商品マスタ where メーカー='SONY')

は複数のレコード（商品コード）が返されますので、それを in を使って検索します。

in 以外にも、 発売日が 2020/09/01 以降のものを抽出するような場合は

select
t1.売上日,t1.商品コード,t1.数量
from 売上データ t1
where 商品コード in (select 商品コード from 商品マスタ where 発売日 >= '2020/09/01')

と言う風に記述できます。

join で結合することでも同じことが出来ますが、このような検索においては、副問い合わせの中に正となるテーブルとの結合条件を記述する必要が無いので、記述が楽になります。

ツールで実行してみると、次の結果が返ってくるはずです。

from で使う副問い合わせ

最後に from で使う副問い合わせを紹介します。

これは冒頭で解説したやつです。

select * from
(
select t1.売上日
,t2.メーカー
,sum(t1.数量) as 数量
,sum(t1.数量) * t2.単価 as 売上金額 from 売上データ t1
left join 商品マスタ t2 on t1.商品コード=t2.商品コード
group by t1.売上日,t2.メーカー
)
where 数量 >= 10

from の後にテーブル名を記述しますが、ここに副問い合わせを記述すると、「その条件で絞り込まれた仮想的なテーブル」を指定したことになります。

扱う上ではテーブルとまったく同じなので、任意の名前（t1とか商品マスタとか）を付けることも可能です。

名前を付けると、別のテーブルとJoinすることができるようになります。

例えば、発売日が '2020/09/01' 以降の売上データに対して、商品仕様をJoin する場合は次の様になります。

select * from
(
select * from 売上データ
where 商品コード in (select 商品コード from 商品マスタ where 発売日 >= '2020/09/01')
) t1
left join 商品仕様 t2 on t1.商品コード=t2.商品コード

結果は次の様になります。

from に副問い合わせを使うメリットは、副問い合わせを重ねていく（入れ子にする）ことで、複雑な抽出条件を比較的簡単に記述できるようになるという点です。

小さい条件でSQLを作って、さらにその上に条件を追加したSQLを重ねて、更に複数のテーブルをJOINして・・・という具合に、段階的に積み上げていくことが出来るので、考えやすくなります。

まとめ

今回は副問い合わせについて説明しました。

一番よく使うのが from での副問い合わせ、次に where 区での副問い合わせ、たまに select での副問い合わせという使用頻度になると思います。

特に from での副問い合わせは、複雑な抽出条件を考えるとき、段階的に積み上げていけるという大きなメリットがあります。

抽出条件は出来るだけシンプルな方が良いのですが、どうしても複雑になってしまう場合、副問い合わせを使うとスッキリと記述できますので、是非チャレンジしてみて下さい。