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

サブクエリとは

サブクエリ（副問い合わせ）とは、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