😂

SQLの相関サブクエリを理解する

2023/11/13に公開

SQL

tech

はじめに

Mysqlを学ぶ中でどうにも相関サブクエリのSQL文が頭に入ってこない問題が発生しています。
そのため、自分なりに紐解いて理解を深めようというのが今回の趣旨になります。

やってみる

まずは実際にデータを動かしてSQL文を書くために下記animalsテーブルを作成します。

id	name	power	type
1	ライオン	1000	パワー
2	シマウマ	250	テクニック
3	チーター	350	スピード
4	ゴリラ	1200	パワー
5	チンパンジー	500	テクニック
6	犬	200	スピード
7	カバ	800	パワー
8	カメレオン	50	テクニック
9	バッファロー	400	スピード
10	キリン	1500	パワー

create table animals
(id int, name varchar(255), power int, type varchar(10));

サンプルデータ

INSERT INTO animals (id, name, power, type)
VALUES
(1, 'ライオン', 1000, 'パワー'), 
(2, 'シマウマ', 250, 'テクニック'), 
(3, 'チーター', 350, 'スピード'), 
(4, 'ゴリラ', 1200, 'パワー'), 
(5, 'チンパンジー', 500, 'テクニック'), 
(6, '犬', 200, 'スピード'),
(7, 'カバ', 800, 'パワー'), 
(8, 'カメレオン', 50, 'テクニック'), 
(9, 'バッファロー', 400, 'スピード'),
(10, 'キリン', 1500, 'パワー');

今回はtypeごとに一番powerの高い動物を抽出するために、相関サブクエリを用いたクエリを書いていきたいと思います。

SELECT *
FROM animals AS outer_animal
WHERE outer_animal.power = (
    SELECT MAX(inner_animal.power)
    FROM animals AS inner_animal
    WHERE inner_animal.type = outer_animal.type
);

よくみる相関サブクエリの形かと思います。

このクエリで僕が理解しづらかったポイントは、
・サブクエリ内でtypeが　=　で繋がっている意味
・メインクエリのWHEREの =　
の処理がひたすらにイメージできませんでした。

前提として、ただのサブクエリ（非相関のサブクエリ）の場合と相関サブクエリでは実行順番が違います。
非相関のサブクエリでは、まずサブクエリが実行され、その結果をメインクエリに渡す　という順番になっています。

相関サブクエリの場合は、メインクエリの各行に対してサブクエリが実行されます。
今回の場合は、outer_animalの各行に対して、

(
    SELECT MAX(inner_animal.power)
    FROM animals AS inner_animal
    WHERE inner_animal.type = outer_animal.type
)

が実行されるわけですね。

では実際にどういう順番で上記のクエリが処理されているのかを確認しましょう

1.　まず、メインクエリの行が参照されます

id	name	power	type
1	ライオン	1000	パワー

メインクエリの現在の行はライオン、type はパワー、power は 1000 です。

2.　サブクエリの処理に移ります
WHERE inner_animal.type = outer_animal.type　の
outer_animal.typeの部分にさきほどのライオンのtypeである”パワー”が代入されます。

WHERE inner_animal.type = outer_animal.type(パワーが入っている）
# =　にする必要があるため、inner_animalをフィルターし、type'パワー'の動物だけに絞り込む

このメインクエリからの代入のイメージが僕は持てていませんでした。

下記のように絞られる

id	name	power	type
1	ライオン	1000	パワー
4	ゴリラ	1200	パワー
7	カバ	800	パワー
10	キリン	1500	パワー

3.　powerの値がもっとも大きいレコードを取得する
その後に、SELECT MAX(inner_animal.power)　によって、もっともpowerの値が大きい
レコードに絞られます。

id	name	power	type
10	キリン	1500	パワー

4.　メインクエリのpowerと一致するか

最後にWHERE outer_animal.power = の部分の処理です。
メインクエリの方ではライオンの　power'1000'を参照していて、サブクエリの方では
キリンのpower'1500'を参照しています。

この数値が一致しないため、このレコードは結果に含まれない...ということになります。

というのが一連のプロセスとなり、これを各行に繰り返し行います。
今回の場合だと10回ですね。

最終的な結果は以下です。

id	name	power	type
5	チンパンジー	500	テクニック
9	バッファロー	400	スピード
10	キリン	1500	パワー

まとめ

ぱっとみてすぐ理解できる人はできるんでしょうが、自分は特に苦戦しました......
こうやって処理を一つ一つ分解していくことで理解がスムーズになりますね。

もし同じように相関サブクエリのsql文に躓いている方の手助けになれば幸いです！

はじめに

やってみる

まとめ

Discussion