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

プログラミング
0

今まで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を学び始めたばかりという方の助けになりましたら幸いです。

0

コメント