サブクエリと仲良くなろう
今回の目標
- サブクエリよくわかんない
- 面接で「生SQL書けるか聞かれた」
- 実務でActiveRecord以上の複雑なクエリを書かないといけない
そんな方々へ少しでも負担が軽減されるように、サブクエリさんと仲良くなる回です🤝
SQL初心者の方にも伝わるよう、SQLとはなんぞや？ から紹介します。
サブクエリだけ読みたい方はサブクエリは何ができる？ から読み始めてください🥰
目次
- 説明しないこと
- SQL用語説明
- SQLを読み書きするコツ
- サブクエリは何ができる？
- 使用するテーブル
- サブクエリを書いてみよう：日本語で書いてみよう
- サブクエリを書いてみよう：SQLに翻訳してみよう
- まとめ
- もっとサブクエリを理解したい方に「CREATE VIEW」「相関サブクエリ」
説明しないこと
- JOIN
- VIEW
- スカラ・サブクエリ
- 相関サブクエリ
釈迦に説法！ 河童に水練！ SQL用語説明
| 用語 | 説明 |
|---|---|
| データベース | 大量の情報を保存し、コンピュータから効率よくアクセスできるように加工したデータの集まり |
| DBMS | データベースマネジメントシステムの略。データベースを管理する機構 |
| RDBMS | リレーショナルデータベースマネジメントシステムの略。いろんなデータベースを管理する方法がある中で、列(カラム)と行(レコード)からなる二次元表で表現できるので、管理が簡単。縦の糸があなたで横の糸が私。織りなす布がテーブル |
| SQL | Structured Query Language: 構造化問合せ言語の略。データベースに問い合わせるための言語。国際標準化機構（ISO）で規格化されている |
| PostgreSQL | SQLの中でも、オープンソースソフトウェアのRDBMS。Oracle社のMySQLやMicrosoft社のSQL Serverみたいに企業が開発したものではない。ある大学が開発して以来、つよつよ技術者たちがPostgreSQLコミュニティで開発し続けている。SQLでも言語に方言があるけど、基本的なものはだいたい一緒。ともあれ今回はこの言語をベースにする |
| クエリ | 日本語で問い合わせ。図書館司書に「カレーライスで本探して！ただし京都に由来するもの中から」と問い合わせすると、「京大カレー部」の本を渡される感じ |
| カラム | 列、柱。縦の糸はあなた |
| レコード | 行、1行分のデータ。横の糸はわたし |
| テーブル | 表、列と行からなる。織りなす布 |
SQLを読み書きするコツ
ここで、SQLを読み書きするコツをお伝えします
SQL文は大文字の命令分と小文字の名前部分で見分ける！
読みやすいSQL文では、だいたい命令文を大文字で、カラム名やテーブル名は小文字(先頭だけ大文字の場合もあり)で表現しています
このようにルールを決めて書くと、後々読み返すときに便利ですよ
(全部大文字のクエリは紙エプロン無しでカレーうどんを食うくらいダルい)
SELECT culum FROM Table;
SQLを書くコツは「クエリを日本語訳すること」
いきなりクエリ書け！って言われても、どうしたら良いかわからんですよね
そこで大事なのが、問い合わせたい内容を日本語にすること！
Q. あなたは図書館に来ています。図書館の中から京都に関連づいたカレーライスの本を探したいです。図書館司書にその旨を告げるとき、このように問い合わせます
カレーライスの本を探してください。
ただし、調べる地域は京都に限定してください。
A. 司書はカレーライスの本棚から取り出した本を差し出してこう言いました
本の中から、カレーライスの項目で京都の地域に合致する京大カレー部の本を見つけました。
これをSQLに翻訳します↓
Q.
SELECT curry_rice -- カレーライスカラム
FROM Book -- 本テーブル
WHERE region = '京都'; -- 地域カラムの中から、京都であるもの
A.
curry_rice -- SELECT句で指定したカラムのみ表示
----------------
'京大カレー部' -- 結果をレコードで表示
(1 rows) -- 表示したレコードの数
これはどういう風に探しているのでしょう？
Books本テーブルの中身を全部見てみましょう
SELECT * FROM Book; -- Bookテーブルの全カラム, 全レコードを見る
| id(一意な主キー) | curry_rice | region | quantity |
|---|---|---|---|
| 0001 | 京大カレー部 | 京都 | 1 |
| 0002 | 関西のスパイスカレーのつくりかた | 大阪 | 3 |
| 0003 | 東京最強カレー | 東京 | 0 |
| … |
今回のクエリでは、指定しなかった表示されなかったカラムもありますね
では、SQLではどのように動いているでしょう？
重要なのは、実行する順番です
| 処理順 | 図書館司書 | SQLの動き | クエリ |
|---|---|---|---|
| 1 | 本棚に向かう | FROM句のBookテーブルを探す | FROM Book |
| 2 | 背表紙に京都って書いてる本を探す | SELECT句ではなく、FROM句よりも後ろに書いたWHERE句を探す！ | WHERE region = ‘京都’ |
| 3 | 京都と名のつく本の居場所を把握したので、カレーライスが並ぶ本棚の中から京都のカレーライス本を取り出す | 他に命令文もないので、最後に取り出したレコードのうち、SELECT句で指定したカラム部分だけを表示させる | SELECT curry_rice |
FROM → WHERE → SELECTという順番で実行されています
このように動いたことで、無事京大カレー部を見つけることができました
実行する順番を覚えておくことは、のちのちエラーにならないためにも必須です！
実行優先順位
太文字が今回使うクエリです
| 実行優先順 | 命令文 | 役割 |
|---|---|---|
| 1 | FROM | 探して欲しいテーブルを指定する |
| 2 | JOIN | テーブルとテーブルを結合する。ONの後に書いたidが結合条件 |
| 3 | WHERE | レコードをフィルタリングする |
| 4 | GROUP BY | レコードをまとめる |
| 5 | HAVING | グループをフィルタリングする |
| 6 | SELECT | 指定のカラムを表示する |
| 7 | DISTINCT | 重複するレコードがあれば除外する |
| 8 | ORDER BY | レコードを並び替える |
| 9 | LIMIT, OFFSET | LIMIT: 上から指定のレコード分だけ表示する, OFFSET: 上から指定のレコード分だけ除外する |
SQLの仕組みを見たところで、さっそくサブクエリについて見ていきましょう
サブクエリ(副問い合わせ)は何ができる？
一言で言うと「クエリの中に書くクエリ」です
-- イメージ
SELECT カラムA, カラムB
FROM (
SELECT カラムA, カラムB
FROM テーブル
) AS サブクエリ名;
1回目で問い合わせた情報を基に、2回目の問い合わせをしたいとき、2回分の問い合わせを1回分にまとめられる便利な機能です
(3回分以上でも、まとめて問い合わせができるぞ！)
Milk inside a bag of milk inside a bag of milk...みたいなイメージ
もっというと、()の中身がサブクエリ。()の外がメインクエリ。1回目の問い合わせがサブクエリで、2回目の問い合わせがメインクエリです
主な使い場所は SELECT, FROM, WHERE です
(SELECT句・WHERE句(単体)では出力が1行1列のみになるスカラ・サブクエリしか使えませんが、今回は説明しません)
2つに分けて書くよりも、処理速度・処理量ともに良いでしょうから、学んでいて損はありません
(JOIN使うよりも速いらしい)
とはいえ、こんな説明ですぐに理解できる人は既知の人か超天才なので、実例で見てみましょう
使用するテーブルの説明
curry_id | curry_name | region | price | quantity | expiration_date
-- 主キー カレー名 発祥国 価格 在庫数 消費期限
----------+----------------------+--------------+-------+----------+-----------------
0001 | バターチキンカレー | インド | 900 | 8 | 2025-04-10
0002 | キーマカレー | インド | 800 | 5 | 2025-04-08
0003 | レッドカレー | タイ | 700 | 5 | 2025-04-07
0004 | グリーンカレー | タイ | 700 | 3 | 2025-04-06
0005 | イエローカレー | タイ | 800 | 3 | 2025-04-07
0006 | ダル | ネパール | 900 | 4 | 2025-04-06
0007 | ソトアヤム | インドネシア | 1000 | 0 |
0008 | ナシカリ | インドネシア | 900 | 2 | 2025-04-06
0009 | チキンティッカマサラ | イギリス | 1000 | 3 | 2025-04-07
0010 | ドライカレー | 日本 | 1000 | 3 | 2025-04-06
0011 | スープカレー | 日本 | 900 | 4 | 2025-04-08
(11 rows)
サブクエリを書いてみよう：日本語で書いてみよう
例題
あなたはカレー屋さんです。
世界各国のカレーを味わえることを売りにしています。
このところ、消費期限の迫っている作り置きカレーがいくつかあります。
これらをいち早く販売してしまえるように、カレーフェアを開くことにしました。
とはいえ、在庫還元セールなんてダサいので、消費期限が迫っていて、なおかつ在庫数の多い国のカレーを「◯◯国カレーフェア」と称して売ろうと思います。
そして、なるべく多くの種類（カレーメニュー）を捌きたいです。
そこで、どの国が・何種類くらい消費期限の危機に迫っていて・いくつ消費期限が切れそうな在庫があるかを調べることにしました。
開くべきは、どの国のカレーフェアでしょうか？
なんだか想像しづらいので、もう少し、条件を箇条書きにしてみましょう
条件
- 消費期限の迫っているものは2025年4月6日と7日とする
- 国別で、以下の条件を表示する
- 何種類くらいカレーが消費期限の危機に迫っているか
- いくつ消費期限が切れそうな在庫があるか
- 表示順は以下を条件とする
- 消費期限の迫っているカレーの種類が多い順に表示する(降順)
- 消費期限の迫っているカレーの在庫総数が多い順に表示する(降順)
- 1.の数が同数の場合は、2.の数で比較する
- 1レコード目の国を今回のカレーフェア対象にする
なんとなく、何をすれば良いか定まってきましたね
これをさらに、クエリ風に書き換えてみましょう
大事なのは「実行する順番」でしたね！
実行順
サブクエリは、メインクエリよりも先に実行するというルールがあります
改めて、命令文の実行順を見てみましょう(使わない命令文は今回除外しています)
| 実行優先順 | 命令文 | 役割 |
|---|---|---|
| 1 | (サブクエリ) | ()の中に書いたクエリから先に実行する |
| 2 | FROM | 探して欲しいテーブルを指定する |
| 3 | WHERE | レコードをフィルタリングする |
| 4 | GROUP BY | レコードをまとめる |
| 5 | SELECT | 指定のカラムを表示する |
| 6 | ORDER BY | レコードを並び替える |
| 7 | メインクエリ | サブクエリの外に書いたクエリ。メインクエリの中でも、2~6の実行順は同じ |
今回の条件だと、サブクエリなんかなくても書けるんですが・・・
今回は、サブクエリの中で消費期限の迫っているカレーを探した後、メインクエリでその他条件を実行してみましょう
実行順を元に、先ほどの条件を細分化し、並び替えてみます
- [サブクエリ_FROM] 探す対象テーブルはCurryテーブル
- [サブクエリ_WHERE] 消費期限が2025年4月6日と2025年4月7日のレコードを探す
- [サブクエリ_SELECT] 探したレコードのうち、カレー名・国名・在庫数の3カラムを抜き出す
- [メインクエリ_FROM] サブクエリで出したカレー名・国名・在庫数のレコードを新たなテーブルとみなす
- [メインクエリ_GROUP BY] これを国名別でまとめる
- [メインクエリ_SELECT] まとめた後、国名・消費期限の迫っているカレーの種類数・消費期限の迫っているカレーの在庫総数を計算して出す
- [メインクエリ_ORDER BY] 計算結果を消費期限の迫っているカレーの種類数の多い順、次いで消費期限の迫っているカレーの在庫総数の多い順に並び替える
- 1レコード目の国を今回のカレーフェア対象にする
これを、SQLに起こしてみましょう！
サブクエリを書いてみよう：SQLに翻訳してみよう
SELECT region, COUNT(curry_name) AS count_curry_type, SUM(quantity) AS total_quantity -- 6
FROM (
SELECT curry_name, region, quantity -- 3
FROM Curry -- 1
WHERE expiration_date IN ('2025-04-06', '2025-04-07') --2
) AS Subquery -- 4
GROUP BY region -- 5
ORDER BY cnt_curry_type DESC, total_quantity DESC; -- 7
| 実行優先順 | 命令文 | 役割 |
|---|---|---|
| 0 | FROM(サブクエリ) | ()内を実行済みのテーブルを呼び出してください。その名もSubqueryです |
| 1 | FROM | Curryテーブルを呼び出してください |
| 2 | WHERE | 消費期限が'2025-04-06'と'2025-04-07’であるレコードを探してください |
| 3 | SELECT | レコードのうち、カレー名、地域、個数カラムのみを選択してください |
| 4 | FROM()AS Subquery | サブクエリを呼び出してください |
| 5 | GROUP BY | 呼び出したテーブル内容を地域別にまとめてください |
| 6 | SELECT | 地域名、地域ごとにカレーライス名が何種類あるかカウントしたもの、地域ごとの個数合計を選択してください |
| 7 | ORDER BY DESC*2 | 選択したレコードをカレーの種類順に降順で並び替えてください。その後、個数合計を降順でさらに並び替えてください |
クエリに起こすと、このようになりました！
サブクエリの追加ルール
- サブクエリの()の後ろには、必ず AS サブクエリ名を命名してあげてください
今回なら、AS Subquery って部分ですね
これがないと、エラーになります(原理はVIEWで調べるとわかるかも)
サブクエリの名前はなんでも良いです。AS Unkoとかでも良いです💩🍛 ASすら無くしてUnko だけでも動きます🍛💩
(ちなみにCOUNT, SUM後のAS ◯◯はなくても大丈夫です)
- メインクエリで使うカラムは、サブクエリの段階でSELECTしておいてください
なぜなら、メインクエリはCurryテーブルを参考にしているのではなく、サブクエリで出力したSubqueryを参考にしているからです
Subqueryに書いてないカラム・レコードは参照することができません！
サブクエリを実行した後、その結果を基にメインクエリを実行するのでしたね
サブクエリだけの結果はこんな感じです
-- サブクエリ = 消費期限のやばいメニュー
SELECT curry_name, region, quantity
FROM Curry
WHERE expiration_date IN ('2025-04-06', '2025-04-07');
curry_name | region | quantity
----------------------+--------------+----------
レッドカレー | タイ | 5
グリーンカレー | タイ | 3
イエローカレー | タイ | 3
ダル | ネパール | 4
ナシカリ | インドネシア | 2
チキンティッカマサラ | イギリス | 3
ドライカレー | 日本 | 3
(7 rows)
サブクエリの結果から、さらにメインクエリを実行することで、最終的にこのような結果になります
最終結果
SELECT region, COUNT(curry_name) AS count_curry_type, SUM(quantity) AS total_quantity
FROM (
SELECT curry_name, region, quantity
FROM Curry
WHERE expiration_date IN ('2025-04-06', '2025-04-07')
) AS Subquery
GROUP BY region
ORDER BY cnt_curry_type DESC, total_quantity DESC;
region | count_curry_type | total_quantity
--------------+------------------+----------------
タイ | 3 | 11
ネパール | 1 | 4
イギリス | 1 | 3
日本 | 1 | 3
インドネシア | 1 | 2
(5 rows)
よって、消費期限の迫っているカレーメニューが3つもあり、尚且つそれらの在庫総数が11個もあるタイを今回のカレーフェアとした方が良いことがわかりました！
まとめ
- サブクエリは、先に実行しておきたいクエリをメインクエリの()内に書く
- ()を書ける場所は、主にSELECT, FROM, WHERE
- 今回はFROM句内で書きましたが、その他のやり方はぜひ調べてみてください
- ()の後にはAS句と適当なサブクエリ名を命名すべし
- メインクエリで使うカラムはサブクエリの時点でSELECTしておくこと
もっとサブクエリを理解したい方に「VIEW」「スカラ・サブクエリ」「相関サブクエリ」
を、調べてみてください！
ところで、キリン本はイイゾ＾〜
余談：今回のテーブルをお手元のDocker環境で触れるリポジトリを作りました
クローン後、そなたのコマンドラインで以下を打てばいつでも生SQLを楽しめます🍛
docker compose build
docker compose up
docker exec -it postgres /bin/bash
psql -U root -d test
Discussion