🌊

サブクエリの書き方を2万文字弱かけてすべて解説する

もりた

2024/05/02に公開2件

これはなに

ども、レバテック開発部のもりたです。

今回はSQLのサブクエリについてまとめます。仕事でクエリを書く際、サブクエリは頻出の構文だと思うんですが、同時にサブクエリの書き方を完全に理解しているよという人は案外少ないのではないでしょうか？^[1]

実際、MySQLの公式ドキュメントを見ると12ページくらいを割かれており、意外と奥深いのがサブクエリです。使いこなせると便利ですし、何よりちょっとSQLのコツみたいなのがわかって面白いよ、ということで記事にしてみました。

前提

この記事は以下の前提を含んでいます。

環境
- MySQL8.0系
読者の知識
- なんとなくサブクエリが書ける
- けど相関サブクエリとかになると「あーっ」つってGoogle meetを閉じてしまうくらいのレベル感
記事のボリューム
- 18,000文字
- おれの卒論が20,000文字だった
マサカリ
- 間違ってたら投げてくれ〜〜

それでは参りましょう。やるぞ！

サブクエリをやる、その前に

サブクエリについて知る前に、２点知っておいてもらいたいSQLの知識みたいなものがあります。どちらもサブクエリを知る上で重要な概念なので、まずはそちらを押さえましょう。

SQLのオブジェクト

JavaやPythonなどのプログラミング言語にはなくて^[2]、SQLにあるオブジェクトといえばなんでしょうか？　考えてみればそりゃそうなんですが、集合を操るSQLでは以下のオブジェクトが重要な意味を持ちます。

スカラー
行
カラム
テーブル

そしてこれらのオブジェクトは（考えてみればそりゃそうなんですが、）包含関係にあります。

今回は頑張ってcanvaで作った画像がちらほら入ります

スカラーは一行一列のテーブルといえます。行は一行複数列のテーブル。
SQLにおいて、これらのオブジェクトは書ける場所が決まっています。
例えばSELECT句の後ろにはスカラーしかかけません。FROM句であればテーブルです。ただ、包含関係にあるため、テーブルが書けるということは同時にスカラー、行、カラムも書けるということを意味します。

サブクエリを書く際は、これを意識することが大切になります。サブクエリの結果がどんなオブジェクトを返すのかを想像しながら書かないと、本来返すべきでないオブジェクトを返してしまい、実行時にエラーを返してしまいます。

スコープ

スコープも重要な概念です。端的に書くと、サブクエリは外部クエリを参照することが可能です。というよりはサブクエリ内にないテーブルを参照すると、外部クエリを見に行きます。
のちほど解説しますが、これを利用してるのが相関サブクエリです。

サブクエリを読んでみる

紹介するのは６つのサブクエリ

では前提知識の整理もできたので、ここからは実際にサブクエリを紹介します。

サンプルテーブル

実例を出すにあたって、先にサンプルのテーブルを用意しておきます。小説の投稿サイトをイメージしています。

ちなみにこのサンプルテーブルは以下の記事で紹介したOSSで公開しているので、サッと環境構築してお手元で試すことができます！！！　嬉しい！！！　気が利いている！！！

クエリチューニングを簡単に試せるハンズオン環境「SeekQueryLab」を作ったよ - Zenn

以下はテーブルのサンプルとスキーマ定義です。まずテーブルのサンプル

■作品テーブル

work_id	user_id	title	description	category_id	rating_type	text_length	total_star
1	34	ワイ将、転生してかわい雪見だいふくになる	会社を辞めた俺は最後の晩餐を楽しむべく雪見だいふく70個とともに帰路についていた。キンキンに冷えた身体のその背後から、時速120km/hで走る暴走トラックが襲いかかる[…]		1	104293	234
2	2	寒い日の過ごし方大全（こたつなし）	こたつに頼らず寒い日をやり過ごすにはこれ！　紅生姜[…]		2	5002	3

■作品-タグテーブル

work_id	tag_id
1	4
1	5

■章テーブル

chapter_id	work_id	chapter_title	status
1	1	ワイ将、会社を辞める	published
2	1	ワイ将、雪見だいふくと異世界に飛ぶ	published
3	1	ワイ将、雪見だいふく70個と精神がまざる	published

■エピソードテーブル

episode_id	chapter_id	status	body_text	text_length
1	1	1	大根のつまを会社の周りにばら撒きながら俺は泣いていた。なんで俺がこんなことをしないといけないんだ[…]	3082
2	1	1	天啓だった。俺の指からレジ袋が落ちる。道路に散らばる大根のつま。一本一本の大きさが揃えられた様は食品というよりさながら工業製品<マニュファクチュア・プロダクツ>のようだ。俺は[…]	4329

■コメントテーブル

comment_id	episode_id	reply_to_id	comment_from	is_head	comment_body
1	4	null	2	true	読んでたらウキウキしてきました。ソチオリンピック以来だ
2	6	null	2	true	食パンって食べたことないんですが、どんな味がするんですか？

■タグマスタ

tag_id	tag_name	tag_description
1	ラブストーリー	愛を扱った物語です
2	コメディ	大爆笑

■カテゴリマスタ

category_id	major_category	sub_category	category_description
1	コメディ	コメディ	大爆笑
1	コメディ	ラブコメ	恋もしちゃうし大爆笑

こちらがスキーマ定義。

-- house keeping fieldは省略

-- マスタ系
CREATE TABLE `ms_tags` (
    `tag_id` int unsigned NOT NULL AUTO_INCREMENT,
    `tag_name` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
    `tag_description` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL,
PRIMARY KEY (`tag_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

CREATE TABLE `ms_categories` (
    `category_id` int unsigned NOT NULL AUTO_INCREMENT,
    `major_category` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL, -- 正規化可能だが、要素の追加等が少ないのでこのまま
    `sub_category` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
    `category_description` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL,
PRIMARY KEY (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;**


-- ユーザー関連
CREATE TABLE `users` (
    `user_id` int unsigned NOT NULL AUTO_INCREMENT,
    `pen_name` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
    `hashed_password` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL,
    `follower_num` int unsigned DEFAULT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;


CREATE TABLE `follows` (
    `follow_to` int unsigned NOT NULL,
    `follow_from` int unsigned NOT NULL,
    PRIMARY KEY (`follow_to`, `follow_from`),
    FOREIGN KEY (`follow_to`)
        REFERENCES users(`user_id`)
        ON DELETE RESTRICT
        ON UPDATE RESTRICT,
    FOREIGN KEY (`follow_from`)
        REFERENCES users(`user_id`)
        ON DELETE RESTRICT
        ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;


-- 作品関連
CREATE TABLE `works` (
    `work_id` int unsigned NOT NULL AUTO_INCREMENT,
    `user_id` int unsigned NOT NULL,
    `title` varchar(100) COLLATE utf8mb4_bin NOT NULL,
    `description` varchar(300) COLLATE utf8mb4_bin DEFAULT NULL,
    `category_id` int unsigned NOT NULL,
    `rating_type` int NOT NULL ,
    `text_length` int NOT NULL DEFAULT '0',
    `total_star` int NOT NULL DEFAULT '0',
    PRIMARY KEY (`work_id`),
    FOREIGN KEY (`user_id`)
        REFERENCES users(`user_id`)
        ON DELETE RESTRICT
        ON UPDATE RESTRICT,
    FOREIGN KEY (`category_id`)
        REFERENCES ms_categories(`category_id`)
        ON DELETE RESTRICT
        ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

CREATE TABLE `work_tags` (
    `work_id` int unsigned NOT NULL,
    `tag_id` int unsigned NOT NULL,
    PRIMARY KEY (`work_id`, `tag_id`),
    FOREIGN KEY (`work_id`)
        REFERENCES works(`work_id`)
        ON DELETE RESTRICT
        ON UPDATE RESTRICT,
    FOREIGN KEY (`tag_id`)
        REFERENCES ms_tags(`tag_id`)
        ON DELETE RESTRICT
        ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

CREATE TABLE `chapters` (
    `chapter_id` int unsigned NOT NULL AUTO_INCREMENT,
    `work_id` int unsigned NOT NULL,
    `chapter_title` varchar(100) COLLATE utf8mb4_bin NOT NULL,
    `status` int NOT NULL,
PRIMARY KEY (`chapter_id`),
    FOREIGN KEY (`work_id`)
        REFERENCES works(`work_id`)
        ON DELETE RESTRICT
        ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

CREATE TABLE `episodes` (
    `episode_id` int unsigned NOT NULL AUTO_INCREMENT,
    `chapter_id` int unsigned NOT NULL,
    `status` int NOT NULL, -- 投稿済み, 下書き
    `body_text` varchar(15000) COLLATE utf8mb4_bin DEFAULT NULL,
    `text_length` int NOT NULL,
PRIMARY KEY (`episode_id`),
    FOREIGN KEY (`chapter_id`)
        REFERENCES chapters(`chapter_id`)
        ON DELETE RESTRICT
        ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

CREATE TABLE `comments` (
    `comment_id` int unsigned NOT NULL AUTO_INCREMENT,
    `episode_id` int unsigned NOT NULL,
    `reply_to_id` int unsigned DEFAULT NULL,
    `comment_from` int unsigned NOT NULL,
    `is_head` boolean NOT NULL,
    `comment_body` varchar(300) COLLATE utf8mb4_bin DEFAULT NULL,
PRIMARY KEY (`comment_id`),
    FOREIGN KEY (`episode_id`)
        REFERENCES episodes(`episode_id`)
        ON DELETE RESTRICT
        ON UPDATE RESTRICT,
    FOREIGN KEY (`reply_to_id`)
        REFERENCES comments(`comment_id`)
        ON DELETE RESTRICT
        ON UPDATE RESTRICT,
    FOREIGN KEY (`comment_from`)
        REFERENCES users(`user_id`)
        ON DELETE RESTRICT
        ON UPDATE RESTRICT

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

以下から始まります！

スカラーのサブクエリ

まず、スカラーを返すサブクエリです。スカラーを返すサブクエリはほとんどの場所に書くことができます。

-- tableの生成
CREATE TABLE t1 (s1 INT, s2 CHAR(5) NOT NULL);
INSERT INTO t1 VALUES(100, 'abcde');

-- SELECT句で使う例
-- 単一の値が結果として返ってくるクエリを、単にSELECTで表示している
SELECT (SELECT s2 FROM t1);

-- SELECT句でMAXの結果を返す例
-- これが一番馴染みあるかも
INSERT INTO t1 VALUES(200, 'abeshi');
SELECT (SELECT MAX(s2) FROM t1);

-- WHERE句で比較に使う例
SELECT s1
FROM t1
WHERE s1 = (
　　　　SELECT MAX(s1) FROM t1
　　　　)
;

-- FROM句で使う例
-- テーブルとみなされている
SELECT sub_1.max_s1
FROM (SELECT MAX(s1) FROM t1) AS sub_1(max_s1);

ちなみに、集約関数を使ったサブクエリというのはサブクエリのよくある使い方です。サブクエリはほとんどの場合、結合（JOIN）で置き換えられるのですが、集約関数を使うケースはその限りではありません。例えばWHEREで集約結果と比較したくても、集約結果は集約するまで存在しないので結合だけでは比較できないのです。先にサブクエリで集約する必要があります。

なお結合とサブクエリの比較に関して、速度面ではケースバイケースです。サブクエリ内でLIMITを使える場合などは、全て結合してからLIMITするよりは速くなることもあるので検討してみても良いでしょう。

カラムのサブクエリ

次に紹介するのはカラムのサブクエリです。複数のレコードが入ったテーブルの、特定の列だけを射影した結果です。

-- 大分類（major_category）で絞って、該当する作品を表示
SELECT works.title, works.category_id
FROM works
WHERE works.category_id IN (
　　　　SELECT category_id
　　　　FROM ms_categories
　　　　WHERE major_category = 'フィクション'
);

これはカラムを返すサブクエリをINで受けています。
このように、カラムのサブクエリは同じ属性が配列になった状態を返すため、IN, ANY, SOME, ALLなどの評価式を使った比較に使うことができます。

一方で、以下の例はエラーを返します。

-- 人気作品タグTOP3に該当する作品のリスト
SELECT works.title
FROM works
　　　　LEFT JOIN work_tags
　　　　ON work_tags.work_id = works.work_id
WHERE work_tags.tag_id IN (
　　　　SELECT tag_id
　　　　FROM work_tags
　　　　GROUP BY tag_id
　　　　ORDER BY COUNT(tag_id)
　　　　LIMIT 3
);

サブクエリで順位をつけてTOP3を抜き出すような使い方はユースケースの多い書き方だと思うのですが、IN, ANY, SOME, ALLとLIMITを併用するのはMySQL的にはNGです。評価順序とかと関係してるのかな？　と思うのですがあくまで推測です。

ANY, SOME, ALLについても使い方を紹介すると、

-- INを使ったやつ。WHEREで比較する対象にINを受ける
-- 大分類（major_category）で絞って、該当する作品を表示
SELECT works.title, works.category_id
FROM works
WHERE works.category_id IN (
-- WHERE works.category_id = ANY (
-- WHERE works.category_id = SOME (
　　　　SELECT category_id
　　　　FROM ms_categories
　　　　WHERE major_category = 'フィクション'
);

-- INを使ったやつ。WHEREで比較する対象にINを受ける
-- 大分類（major_category）で絞って、どれにも該当しない作品を表示
SELECT works.title, works.category_id
FROM works
WHERE works.category_id NOT IN (
-- WHERE works.category_id <> ALL (
　　　　SELECT category_id
　　　　FROM ms_categories
　　　　WHERE major_category = 'フィクション'
);

INと= ANY, = SOME はそのまま置き換え可能です（とある値Aは条件を満たす）。しかし、NOT IN は「すべての値が条件を満たさない」となり、 <> ALLと同値になります。

そして<> ANYは「とある値Aは条件を満たさない」を意味します。しかしここで困ったことがあります。公式を引用すると、

ほとんどの人びとにとって、「a is not equal to any b」(a はどの b にも等しくない) という英語のフレーズは「there is no b which is equal to a」(a に等しい b は存在しない) を示しますが、それはこの SQL 構文が示す内容とは異なります。この構文は、「there is some b to which a is not equal」(a に等しくない b がいくつか存在する) を示します。代わりに <> SOME を使用すると、このクエリーの本当の意味がすべての人に理解されるようにするのに役立ちます。

とのことで、<> ANYを直感的に利用するために<> SOMEがあるようです。

なお、ANYとかSOMEとかALLとかを論理学では量化子とよび∀（全称量化子）とか∃（存在量化子）とかを使って表します。ここらへんはややこしくて説明しがたいので割愛します。また同様の理由で三値理論についても割愛します。

行のサブクエリ

例えば、行を返すサブクエリは以下のように比較を行うことができます。

SELECT * FROM t1
　　　　WHERE (col1,col2) = (SELECT col3, col4 FROM t2 WHERE id = 10);
SELECT * FROM t1
　　　　WHERE ROW(col1,col2) = (SELECT col3, col4 FROM t2 WHERE id = 10);

こういう感じの比較・代入みたいな操作はPythonとかでもあるので直感的な使い方かと思います。このROW(col1, col2)というような書き方を行コンストラクタと呼ぶようです。

また、この行と行の比較に先ほど登場したINを使ってみましょう。そうすると、サブクエリで得られた複数行の中のどれかに合致する行を選択するというクエリも書けます。

-- 作者IDとカテゴリで検索するクエリ
SELECT *
FROM works
WHERE (works.user_id, works.category_id) IN (
　　　　SELECT user_id, 2 -- カテゴリがコメディ
　　　　FROM users
　　　　WHERE users.follower_num > 100
)
;

このとき、察しの良い方（もしくは若干サブクエリに飽きてきた方）なら「それ結合で良くね？」と思われたかと思います。実際、上記のクエリは結合でも書くことができます。

-- これでも全然良い。むしろシンプル
SELECT *
FROM works
　　　　RIGHT JOIN users
　　　　ON works.user_id = users.user_id
WHERE 
　　　　users.follower_num > 100 and
　　　　works.category_id = 2
;

こういう書き方も全く問題ありません。むしろこっちの方が馴染みあってわかりやすいですね。

テーブルのサブクエリ（導出テーブル）

先ほど行のサブクエリのところで、さらっとテーブルを返すサブクエリを扱ってしまったのですが、テーブルを返すサブクエリのうちFROM句に書かれたものを特に導出テーブルと呼びます。

これのメリットはなにより読みやすいことです。大きなテーブルを小さくしてから扱うのはサブクエリの王道的な使い方です。

SELECT *
FROM works
　　　　RIGHT JOIN (
　　　　　　　　SELECT *
　　　　　　　　FROM users
　　　　　　　　　　　　WHERE users.follower_num > 100
　　　　　　　　) AS favorite_users -- ここが導出テーブル
　　　　ON works.user_id = favorite_users.user_id
WHERE 
　　　　works.category_id = 2
;

このとき少し気にしておきたいのが、オプティマイザの動きです。上記クエリと、上記クエリを少し書き換えたクエリの実行計画を見比べてみましょう。

EXPLAIN
SELECT *
FROM works
　　　　RIGHT JOIN (
　　　　　　　　SELECT *
　　　　　　　　FROM users
　　　　　　　　WHERE users.follower_num > 100
　　　　) AS favorite_users
　　　　ON works.user_id = favorite_users.user_id
WHERE 
　　　　works.category_id = 2
;
/*
# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
1, SIMPLE, works, , ref, user_id,category_id, category_id, 4, const, 2951, 100.00, Using where
1, SIMPLE, users, , eq_ref, PRIMARY, PRIMARY, 4, querytuning.works.user_id, 1, 33.33, Using where
*/

EXPLAIN
SELECT *
FROM works
　　　　RIGHT JOIN (
　　　　　　　　SELECT *
　　　　　　　　FROM users
　　　　　　　　ORDER BY follower_num
　　　　　　　　LIMIT 100
　　　　) AS favorite_users
　　　　ON works.user_id = favorite_users.user_id
WHERE 
　　　　works.category_id = 2
;
/*
# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
1, PRIMARY, <derived2>, , ALL, , , , , 100, 100.00, 
1, PRIMARY, works, , ref, user_id,category_id, user_id, 4, favorite_users.user_id, 2, 9.89, Using where
2, DERIVED, users, , ALL, , , , , 28082, 100.00, Using filesort
*/

解説をすると、上のクエリは実行時にはサブクエリではなく結合で処理されています。それに対して下のクエリは結合に置き換わることなく導出サブクエリのまま処理されています。これはオプティマイザによる最適化の結果です（実行計画のselect_typeがDERIVEDになっている場合はサブクエリとして処理されています）。

サブクエリ内でLIMITしたり集計関数を使っている場合は結合に置き換えられないのでサブクエリとして処理されるというわけです。ここは実行計画を眺めていて「あれ？」となりがちなポイントなのでちょっと覚えておくと心穏やかにEXPLAINできるかと思います。

サブクエリでの最適化については以下を参照してください。

8.2.2.4 マージまたは実体化を使用した導出テーブル、ビュー参照および共通テーブル式の最適化

外部を参照するサブクエリ（相関サブクエリ）

さて、相関サブクエリです。相関サブクエリとは外部クエリと相関したサブクエリなんですが、相関がどうこうとかよりもとにかくSQL初学者の心を折るサブクエリです。
（もりたもこの記事を書き始めた時点では理解してませんでした（書いてみたらまだ理解し切れてないことがわかりました））

とりあえず例をだすと、

-- フォロワー数の更新
UPDATE users
SET follower_num = (
　　　　SELECT count(*)
　　　　FROM follows
　　　　WHERE follows.follow_to = users.user_id
)
;

ぱっと見て疑問に思うのは「サブクエリの中に急に変なテーブルが出てくる」点だと思います。usersテーブルはサブクエリのFROM句にないですよね。

ここで思い出して欲しいのが「スコープ」です。サブクエリ内にテーブルが存在しないときは外部クエリから取ってこれるんでしたよね。けど、「外側からとってくる」ってどういうことか分かりますか？（もりたは分かんなかったです）

これはループ構造になっていると考えます。外部クエリの一行ごとにサブクエリが走り、外部クエリの値を参照します。上の例だと、ユーザーごとに、followsテーブルのユーザーをフォローしているユーザーの数を集計して、follower_numを更新しています。

なお参照サブクエリはまだ序の口で、もっとややこしいものとして「自己結合する相関サブクエリ」「多段でEXISTSするサブクエリ」があります。

自己結合する相関サブクエリ

自己結合する相関サブクエリの例がこれです。

-- フォロワー数のランキングを表示する
SELECT u1.user_id, (
　　　　SELECT count(*) + 1
　　　　FROM users u2
　　　　WHERE u2.follower_num > u1.follower_num
) AS follower_rank
FROM users AS u1
ORDER BY follower_rank
;

これは特定のユーザーについて、そのユーザーよりフォロワー数が多いユーザーをサブクエリ内で抽出し、カウントしています。自己結合するだけでちょっと頭がこんがらがりますね。

多段でEXISTSするサブクエリ

EXISTを使った例はこちらです。MySQL公式サイトの例をそのまま持ってきます。

1 つ以上の市に存在するのはどのような種類のお店ですか?

SELECT DISTINCT store_type FROM stores
　　　　WHERE EXISTS (SELECT * FROM cities_stores
　　　　　　　　WHERE cities_stores.store_type = stores.store_type);

店舗の種類ごとに、どこかの市にその店舗種類があるかどうかをサブクエリ内で調べています。合致するものがあればEXISTSに引っかかり、外部クエリで抽出されます。

どの市にも存在しないのはどのような種類のお店ですか?

SELECT DISTINCT store_type FROM stores
　　　　WHERE NOT EXISTS (SELECT * FROM cities_stores
　　　　　　　　WHERE cities_stores.store_type = stores.store_type);

こちらでも店舗の種類ごとに、どこか市にその店舗種類があるかどうかをサブクエリ内で調べています。ただ、サブクエリ内でヒットしなかった（どの市にも店舗がなかった）店舗種類を抽出しています。

すべての市に存在するのはどのような種類のお店ですか?

SELECT DISTINCT store_type FROM stores
　　　　WHERE NOT EXISTS (
　　　　　　　　SELECT * FROM cities 
　　　　　　　　　　　　WHERE NOT EXISTS (
　　　　　　　　　　　　　　　　SELECT * FROM cities_stores
　　　　　　　　　　　　　　　　　　　　WHERE cities_stores.city = cities.city
　　　　　　　　　　　　　　　　　　　　AND cities_stores.store_type = stores.store_type));

最後は多段になっています。ループだと考えてみてみましょう。分解して考えてみます。

SELECT DISTINCT store_type FROM stores
　　　　WHERE NOT EXISTS (

まずこの部分。ここではstores一行ずつについてループをしています。

　　　　　　　　SELECT * FROM cities 
　　　　　　　　　　　　WHERE NOT EXISTS (

次にこの部分。storesのループの中で、さらにcitiesでループをします。

　　　　　　　　　　　　　　　　SELECT * FROM cities_stores
　　　　　　　　　　　　　　　　　　　　WHERE cities_stores.city = cities.city
　　　　　　　　　　　　　　　　　　　　AND cities_stores.store_type = stores.store_type));

でこの部分。cities.city, stores.store_typeに合致するようなcities_storesが存在するかチェックしています。ここで合致するcities_storesがあるなら、特定の店舗種類、特定の市で合致するものがあるということです。

ここからが若干わかりにくいんですが、

　　　　　　　　SELECT * FROM cities 
　　　　　　　　　　　　WHERE NOT EXISTS (

そんなcities_storesが存在しないcitiesを出します。

つまり、店舗は固定で、その店舗種類が存在していない市の一覧を出しています。図にするとこんなかんじ

えっ、こんな画像まで作ったんですか？　そうなんです。全ては見やすい記事のため...

SELECT DISTINCT store_type FROM stores
　　　　WHERE NOT EXISTS (

最後がこれ。ひとつひとつの店舗について、「その店舗種類が存在していない市の一覧」が返ってこない（０件だった）店舗の一覧を出して、さらに店舗種類で集約しています。つまり「その店舗種類が全ての市に存在している」ような店舗種類となります。

なぜこんなにいい感じの画像を入れるのか。その問いにもりたは答えた。
「たいした理由はないんです。ただ、技術者コミュニティに貢献したかった...へへ、なんだか照れますね」

これもし分かんないなと思っても一晩寝ると理解できたりするので、分からない人はいますぐ寝てください。

ラテラル導出テーブル（FROM句で外部を参照するテーブルのサブクエリ）

さて、長いサブクエリの旅もこれが最後です。名残惜しいですね。
最後に紹介するのはラテラル導出テーブルです。これはFROM句で相関サブクエリを書くというものになります。

-- ユーザーごとに一番人気の作品を出す
SELECT users.pen_name, favorite_works.title, favorite_works.total_star
FROM users,
　　　　LATERAL (
　　　　　　　　SELECT title, total_star
　　　　　　　　FROM works
　　　　　　　　WHERE works.user_id = users.user_id
　　　　　　　　ORDER BY total_star desc limit 1
　　　　) AS favorite_works
;

通常、FROM句に複数のテーブルを並べるとテーブル同士のCROSS JOINになってしまいますが、ここではちょっと違います。外部クエリのテーブル一行一行に対して、LATERAL導出テーブルがJOINされるイメージです。EXISTSの相関サブクエリをやったあとだと割と単純に思えますね。

おわりに

今回はMySQLのサブクエリを全て調べてみました。皆さんの周囲に眠る古文書のようなサブクエリ、ぜひこれで解き明かしてみてくださいね。^[3]

もりたはいつもこれくらいご機嫌に働いています。レバテック開発部っていい職場だなあ！！

参照文献

書籍

Webページ

13.2.11 サブクエリー - MySQL公式
相関サブクエリで行と行を比較する
- 解説ページ
- これらは『達人に学ぶSQL徹底指南書第１版』の元になった記事。相関サブクエリを扱った章は第２版でなくなっていたため、第１版の情報も参照しました。
  - なお第１版の内容はWebで読めます。以下にまとめてくれたページがあるので参照
    - 無料で学ぶ『達人に学ぶSQL徹底指南書第1版』

この記事の作成ログ

ログ：思ったより奥の深いサブクエリの世界

脚注

SQLって習うより慣れろで覚えちゃう傾向ありますよね ↩︎
「なくて」って言ってますが裏取りしてません。あったらごめん。ごめんて ↩︎
なおもりたはこの記事を公開する直前に仕事でサブクエリで書くべきところをJOINで書いてしまい、UPDATEがうまく走らなくてほんっっっっっとに悔しかったです、ドヤ顔でクエリ書いて人の前で実行しようとして失敗したのでマジで顔から火が出るかと思った、ウーッ！！！！！！！（恥） ↩︎

レバテック開発部

レバテック開発部の公式テックブログです！

Discussion

nakamoto 14日前

SQLラブの受講生に紹介させていただきます！
GW中に良い知見に出会えうれしいです。

もりた 14日前

ありがとうございます〜！！　読んでいただいて嬉しいです。

はてぶで色々と良いコメントいただいてるのでそちらも紹介していただけるとなお良いんじゃないかと思います！！