IT技術

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

今まで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

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

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

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

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

 

SELECT title, comment
FROM post_table;

 

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

result1

さて、ここから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を少し変更します。

post_table2

また、テーブルを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つのテーブルを全て結合してみました。
するとこのようなテーブルになります。

join table

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

limit

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

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

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

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

 

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

 

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

先にLIMIT

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

サブクエリ後

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

先に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を使って記載します。

まとめ


今回は私が学んできたサブクエリについて解説してきました。

このような条件でSELECT文を書きたいけど、思ったようにデータを取得することができない

そんなときはサブクエリを使って書いてみるとうまくいくかもしれません。
SQLを学び始めたばかりという方の少しでも助けになりましたら幸いです。

どこからでも開発できる環境を作りませんか?

ConoHa VPSを使えば、クラウド上に自分だけの開発環境を構築することができます!

ConoHa VPSのメリット
  • 1回環境を作ればマシン毎に作り直す必要がなくなる
  • 開発途中のコードを外出先からでも簡単にいじることができる
  • 従量課金制で使うこともできる
  • 従量課金制で使った場合でも上限を超えることはない
定額プランでも月額620円から使うことができるので、コスパ最強!
\今なら初期費用無料/

ConoHa VPS公式ページ >>