MySQLでSELECT FROMの中でSELECTしてみる〜サブクエリの話〜

MySQLでSELECT FROMの中でSELECTしてみるというサブクエリの話です。

具体的には以下のように、SELECTのFROM句の中でのSELECTについて書いていきます。

SELECT {取得したいカラム}
FROM
    (
        SELECT {取得したいカラム}
        FROM {テーブル名}

    ) AS {サブクエリ名}

SELECTのFROM句の中でのSELECTはサブクエリと言います！

SELECTのFROM句以外でもサブクエリは使えますが、今回はSELECTのFROM句に絞ってのサブクエリについて記載します。

試しにサブクエリしてみます！

SELECT FROMの中でSELECTしてみる

まずテーブルを作成します。

create table user(
id INT,
type INT,
name VARCHAR(255)
);

1件INSERTします。

INSERT INTO user VALUES(1, 2,'usr_name_1');

SELECTのFROM句の中でSELECTしてみます。

SELECT
    subquery.id
FROM
    (
        SELECT
            id
        FROM
            user
    ) AS subquery
;

実行結果は以下のようになります。

+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

SELECT FROMの中でSELECTする際のポイント

ポイント

FROM句の中で(SELECT文)を使用する
(SELECT文) AS でサブクエリ名を付ける
(SELECT文) で取得したカラムについて、外側のSELECT文で取得可能
→サブクエリ名.カラム名で取得できる(上記の例だと「subquery.id」)

上記の例では、「SELECT id FROM user」と実行結果が同じになり、「SELECT FROMの中でSELECTする」意味がない(SQLが複雑になるだけ)です。

次は、実際に「SELECT FROMの中でSELECTする」と効果がある例について記載します。

以降、「SELECT FROMの中でSELECTする」はサブクエリと呼びます。

サブクエリの効果的な使い方

サブクエリを使わないと取得が難しい場合や、サブクエリを使うことで高速化が測れる場合などで使用します。サブクエリ内でデータの絞り込みが行える場合、使用するとクエリの高速化ができます。

サブクエリを使用するケース

サブクエリを使わないと取得が難しい場合
サブクエリを使うことで高速化が測れる場合
→サブクエリ内でデータの絞り込みが行える場合、使用するとクエリの高速化ができるケースがあります。

具体的にクエリ例を記載していきます！

こんなときにサブクエリが使えるよ！という具体例を記載していきます。

まずは、準備です。

準備

◾️テーブル作成

以下のテーブルを作成します。

create table user(
id INT AUTO_INCREMENT,
type INT,
name VARCHAR(255),
PRIMARY KEY (id)
);

create table user_score(
id INT AUTO_INCREMENT,
user_id INT,
score INT,
PRIMARY KEY (id), index(user_id)
);

◾️プロシージャ作成

次は、テストデータはプロシージャーで作成しますのでプロシージャを作成します。

以下の通りデータ作成してくれるようになっています。

user.type：カウントを２で割った余りを格納（あまり意味がないカラムです。）
user.name：「user_name_{カウント}」の値を格納
user.score：ランダムな数値を格納

-- 区切り文字を「//」に変更する
DELIMITER //

-- make_sample_dataというテストデータ作成用プロシージャーを作成する

create procedure make_sample_data(in i int)
begin
  declare count int default 0;
  -- 繰り返し
  while count < i do
    set count = count + 1;
    INSERT INTO user (type, name) VALUES(MOD(count,2),CONCAT('usr_name_',count));
    INSERT INTO user_score (user_id, score) VALUES(count, CEIL(RAND() * 1000));
    INSERT INTO user_score (user_id, score) VALUES(count, CEIL(RAND() * 1000));
    INSERT INTO user_score (user_id, score) VALUES(count, CEIL(RAND() * 1000));
  end while;
end
//
-- 区切り文字を「;」に戻す
DELIMITER ;

◾️100万件インサート

mysql> call make_sample_data(1000000);
Query OK, 1 row affected (4 min 29.74 sec)

◾️バージョン確認

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.26    |
+-----------+
1 row in set (0.02 sec)

準備ができたので、サブクエリを使っていきます！

まずは、サブクエリを使わないと取得が難しい場合の例です。

サブクエリを使わないと取得が難しい場合

最も大きいデータや最も小さいデータの取得など、集計した結果の最大値、最小値を求める際には、サブクエリを使うと便利です。

例えば、上記で準備したテーブルのスコアが最も大きいユーザのスコアを取得するなどです。

スコアが最も大きいユーザのスコアを取得するサブクエリSQLは以下となります。

select max(score_sum.sum) as max from (
        SELECT
            `user_id`,
           SUM(`score`) AS `sum`
        FROM
            `user_score`
        GROUP BY
            `user_id`
    ) AS `score_sum`
;

// 実行結果
+------+
| max  |
+------+
| 2998 |
+------+
1 row in set (10.51 sec)

サブクエリを使うことで高速化が測れる場合

サブクエリ内でデータの絞り込みが行える場合、使用するとクエリの高速化ができます。

上記で準備したテーブルで、ユーザのスコアの平均が990のデータを絞り込む例で考えてみます。

◾️サブクエリを取得した場合

SELECT
    `user`.*,
    `score`.`average`
FROM
    (
        SELECT
            `user_id`,
            AVG(`score`) AS `average`
        FROM
            `user_score`
        GROUP BY
            `user_id`
        HAVING `average` > 990
    ) AS `score`
    JOIN
        `user`
    ON  `user`.`id` = `score`.`user_id`
;

// 実行結果
59 rows in set (8.75 sec)

◾️JOINを使用した場合

SELECT
    `user`.*,
    AVG(`user_score`.`score`) AS `average`
FROM
    `user`
    LEFT JOIN
        `user_score`
    ON  `user`.`id` = `user_score`.`user_id`
GROUP BY
    `user_id`
HAVING `average` > 990
;

// 実行結果
59 rows in set (17.68 sec)

サブクエリの方が高速でしたー

まとめ

サブクエリについて記載しましたー

まとめ

SELECTのFROM句の中でのSELECT
→サブクエリと言います！
＜サブクエリを使うケース＞
サブクエリを使わないと取得が難しい場合
サブクエリを使うことで高速化が測れる場合
→サブクエリ内でデータの絞り込みが行える場合、使用するとクエリの高速化ができるケースがあります。