MySQLでサブクエリを使って条件を絞る方法

2019年11月9日2022年12月22日

当ページのリンクには広告が含まれています。

今までSQLは最低限のSELECTとDELETEくらいしか使ったことがありませんでした。

しかし、プログラマとして仕事をしているとそういう訳にもいかず、最近は毎日SQLを書きながら勉強しています。

そんな中で、なかなか理解しづらかったサブクエリについて自分なりの解釈をアウトプットしてみます。

タップできる目次

サブクエリとは具体的にどんなもの？

サブクエリを使った書き方は以下のようなものです。

SELECT *
FROM test_table
WHERE user_name IN
(
  SELECT name
  FROM user_table
  WHERE age = 20
);

WHEREの条件を指定する箇所にSELECT文がありますよね。

このようにSELECT文の中で更にSELECT文を使ったものをサブクエリといいます。

サブクエリを使うとどんなことができる？

では、サブクエリを使うと具体的にどのようなことができるのでしょうか？

私が主に使っているのは以下の2パターンです。

WHEREの条件に使う
FROMで検索元のテーブルを絞る

これら2つについて詳しく解説していきます。

WHEREの条件に使う

こちらは先程の例で書いたものですね。

WHEREを使う場合は、

SELECT *
FROM test_table
WHERE id = 10;

のような書き方をするのが基本形です。

こちらの例ではtest_tableというテーブルからidが10のデータを取得します。

このように、検索する条件が1つの値、1つのテーブルだけならば何の問題もありませんが、実際はそういう訳にはいきません。

例えば、これらのようなテーブルがあったとします。

ユーザ情報が書かれたuser_tableと、投稿した情報が格納されているpost_tableとします。

このpost_tableからnameを使ってtitleとcommentを取り出したいとします。

では、SQLを書いてみましょう。

まず、取り出したいものと取り出し元を書いてみます。

SELECT title, comment
FROM post_table;

これで、post_tableからtitleとcommentが取り出せます。

しかし、これだけだと条件で絞っていないため、全てのtitleとcommentが出てしまいます。

さて、ここからnameで絞りたいですが、nameはuser_tableにしかありません。

そんな場合、サブクエリを使ってこのように表現できます。

SELECT title, comment
FROM post_table
WHERE user_id IN
(
  SELECT id
  FROM user_table
  WHERE name = "aaa"
);

これでaaaの人のtitleとcommentが取り出せます。

では、このSQLについて見ていきましょう。

まずサブクエリの中のSELECT文を見ていきます。

取り出したいのはid、取り出す元はuser_tableです。

そして条件にnameがaaaを指定しています。

まとめると「user_tableからnameがaaaの人のidを取り出す」となります。

つまり、このサブクエリの実行の結果nameがaaaのidです。

WHERE user_id IN (サブクエリ)と書いてありますが、結果を反映すると、

WHERE user_id IN idとなります。

これはWHERE user_id = idと同じ意味となるので、nameがaaaのidとイコールであるuser_idで絞ることができます。

FROMで検索元のテーブルを絞る

では、次にFROMを使ったサブクエリについてです。

先程のpost_tableを少し変更します。

また、テーブルを1つ追加します。

ユーザ情報が入ったuser_tableと、投稿日時を追加したpost_table、投稿に添付するための画像ファイルを指定するimage_tableとします。

これらのテーブルを使って、11月1日の古い発言から3件だけ名前、タイトル、コメント、画像をそれぞれ表示させるとします。

テーブルが3つあるので、テーブル同士を結合して、対象の日付から3件だけ取得したら何となく欲しい値が取り出せそうな気がしますよね？

試しにやってみましょう。

SELECT name, title, comment, file_name
FROM post_table
LEFT JOIN user_table
ON post_table.user_id = user_table.id
LEFT JOIN image_table
ON post_table.id = image_table.post_id

3つのテーブルを全て結合してみました。

するとこのようなテーブルになります。

ここから11月1日の上からLIMITを使って3件取得しようとすると、

こうなってしまいます。

bbbさんは1つの発言に対して2つの画像を添付しているため、それぞれを1つの発言として取得してしまい、欲しい結果とは異なる結果が出てしまいます。

これを解決するためにサブクエリを使います。

あらかじめpost_tableから欲しい要素を絞っておけばこのような変な結果にはなりません。

そのため、先にLIMIT 3で3行だけのテーブルを作ります。

SELECT *
FROM post_table
WHERE date = "11月1日"
LIMIT 3

結果、このようなテーブルになります。

このテーブルに対して結合を行えば、

このように欲しい情報を取得することができます。

先にSELECT文を使って結果を絞っておくためにサブクエリを使います。

その際、どこから取得してくるかという意味であるFROMをSELECTの結果にしたいので、FROMでサブクエリを使います。

SELECT name, title, comment, file_name
FROM (
  SELECT *
  FROM post_table
  WHERE date = "11月1日"
  LIMIT 3
) AS result_table
LEFT JOIN user_table
ON result_table.user_id = user_table.id
LEFT JOIN image_table
ON result_table.id = image_table.post_id

このように書くことで実現できます。

FROMの中でサブクエリを実行し、その結果のテーブルをAS result_tableで、result_tableと名付けています。

先にLIMITで絞った結果のテーブル名がresult_tableとして扱うので、結合するときのテーブル名もresult_tableを使って記載します。