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

2021.4.42021.4.5Writer: f.nakamuraComment: 0

サブクエリとは

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

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

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


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

select max(score)

from test_scores

where subject = '国語'


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

max

-----

80 ← 結果が単一列単一行（1列1行）

(1 row)

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


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;

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)

student_id | score

-----------+-------

203 | 80

204 | 80

(2 rows)

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

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

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


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

select subject, max(score)

from test_scores

where subject = '国語'

group by subject;


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

subject | max

--------+-----

国語 | 80 ← 結果が複数列単一行（2列1行）

(1 row)

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


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;

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)

student_id | score

-----------+-------

203 | 80

204 | 80

(2 rows)

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


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

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)

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;

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)

subject | avg_score

---------+---------------------

理科 | 65.0000000000000000

国語 | 70.0000000000000000

社会 | 80.0000000000000000

算数 | 70.0000000000000000

(4 rows)

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


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;

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)

subject | avg_score

---------+---------------------

理科 | 65.0000000000000000

(1 row)

最後に

今回はサブクエリを勉強しました！
サブクエリを勉強することで、色々な条件を組み合わせたSQLが書けるようになり、幅が広がった気がします。
また更新します！