サブクエリについてまとめてみた！

サブクエリとは

サブクエリ（副問い合わせ）とは、SQLの中に別のSQLを埋め込んで使う機能のことです。

サンプルデータ

・「students」テーブル・・・生徒の一覧を表します。
・「test_scores」テーブル・・・テストの点数を表します。科目は「国語」、「算数」、「理科」、「社会」の４教科。

・studentsテーブル

id	name	gender	class
201	さくら ももこ	F	3-4
202	はなわ かずひこ	M	3-4
203	ほなみ たまえ	F	3-4
204	まるお すえお	M	3-4

・test_scoresテーブル

student_id	subject	score
201	国語	60
201	算数	40
201	理科	40
201	社会	50
202	国語	60
202	算数	70
202	理科	50
202	社会	70
203	国語	80
203	算数	80
203	理科	70
203	社会	100
204	国語	80
204	算数	90
204	理科	100
204	社会	100

サブクエリが返す結果

サブクエリ（つまり埋め込むSQL）がどんな結果を返すのかによって、サブクエリの使い方が決まります。
具体的には、SQL の結果が単一列なのか複数列なのか、また単一行なのか複数行なのかで4種類に分類できます。

単一列単一行のサブクエリ

・実行結果が単一列単一行のSQL

国語の最高得点を検索するSQL

select max(score)
from test_scores
where subject = '国語'

実行結果（1列1行）

max
-----
80    ← 結果が単一列単一行（1列1行）
(1 row)

・単一列単一行のサブクエリ
サブクエリの返す結果を単一値として扱うことができます。

国語で最高点をとった生徒のIDと点数を検索するSQL

select t.student_id, t.score
from student t
where t.subject = '国語'
  and t.score = ( select max(score) from test_scores where subject = '国語')
order by t.student_id; 

実行結果

student_id | score
-----------+-------
       203 |    80
       204 |    80
(2 rows)

ちなみに1行も返さないSQLをサブクエリに使うとエラーにはならず、結果が空になります。

複数列単一行のサブクエリ

・実行結果が複数列単一行のSQL

国語の最高点を検索する

select subject, max(score)
from test_scores
where subject = '国語'
group by subject;

実行結果(2列1行)

subject | max
--------+-----
   国語 | 80     ← 結果が複数列単一行（2列1行）
(1 row)

・複数列単一行のサブクエリ
サブクエリの返す結果を複合値として扱うことができます。

国語の最高点をとった生徒のIDと点数を表示するSQL

select t.student_id, t.score
from test_scores
where (t.subject, t.score) = (select subject, max(score) 
                              from test_scores
                              where subject = '国語' 
                              group by subject)
order by student_id;

実行結果

student_id | score
-----------+-------
       203 | 80
       204 | 80
(2 rows)

単一列複数行のサブクエリ

・実行結果が単一列複数行のSQL

どれかの教科で100点満点をとった生徒のIDをすべて表示するSQL

select student_id
from test_scores
where score = 100;

1列3行

student_id
------------
       203    ← 結果が単一列複数行（1列3行）
       204
       204
(3 rows)

・単一列複数行のサブクエリ

どれかの教科で100点満点をとった生徒をすべて表示

select s.*
from students s
where s.id in (
  select student_id
  from test_scores
  where score = 100
)
order by s.id;

実行結果

id  | name           | gender | class
-----+---------------+--------+-------
203 |     ほなみ たまえ |      F | 3-4
204 |     まるお すえお |      M | 3-4
(2 rows)

複数列複数行のサブクエリ

・実行結果が複数列複数行のSQL

教科別の平均点を表示する

select subject, avg(score) as avg_score
from test_scores
group by subject;

実行結果

subject  | avg_score
---------+---------------------
    理科 | 65.0000000000000000
    国語 | 70.0000000000000000
    社会 | 80.0000000000000000
    算数 | 70.0000000000000000
(4 rows)

・複数列複数行のサブクエリ
テーブル代わりのサブクエリとして扱うことができます。

平均点が70点に満たない教科とその平均点を検索する

select x.subject, x.avg_score
from (
  select subject, avg(score) as avg_score
  from test_scores
  group by subject
) x
where 70 > x.avg_score;

実行結果

subject  | avg_score
---------+---------------------
     理科 | 65.0000000000000000
(1 row)

参考

わかりみSQL