※学習中のため間違っている箇所があるかもしれません。
初めに
今回はサブクエリを解説していきます。
サブクエリは名前だけは聞いたことはあるけど使ったことはない、という人も多いと思います。
サブクエリに関して、演習形式で学習していきましょう。
環境
以下に今回使用している環境をまとめておきます。
- A5M2
- MySQL
SQL
usersテーブル
CREATE TABLE users (
id int AUTO_INCREMENT not null primary key,
name varchar(50),
email varchar(50),
role ENUM('USER', 'ADMIN'),
created_by varchar(50),
updated_by varchar(50),
created_at timestamp default current_timestamp,
updated_at timestamp default current_timestamp,
delete_flg boolean
);
ordersテーブル
CREATE TABLE orders (
id int AUTO_INCREMENT PRIMARY KEY NOT NULL,
name VARCHAR(50) NOT NULL,
price int NOT NULL,
purchase_date timestamp default current_timestamp,
user_id int NOT NULL
);
usersデータ挿入
INSERT INTO users (name) VALUES ('Taro');
INSERT INTO users (name) VALUES ('Hanako');
INSERT INTO users (name) VALUES ('Jiro');
INSERT INTO users (name) VALUES ('Miki');
ordersデータ挿入
INSERT INTO orders (user_id, name, price, purchase_date) VALUES (1, 'コーラ', 1000, '2025-04-01 10:00:00');
INSERT INTO orders (user_id, name, price, purchase_date) VALUES (1, 'サイダー', 2000, '2025-04-02 11:30:00');
INSERT INTO orders (user_id, name, price, purchase_date) VALUES (2, 'デカビタ', 3000, '2025-04-01 09:00:00');
INSERT INTO orders (user_id, name, price, purchase_date) VALUES (3, 'ドデカミン', 1500, '2025-04-02 14:00:00');
INSERT INTO orders (user_id, name, price, purchase_date) VALUES (5, 'ZONE', 1200, '2025-04-03 16:00:00');
サブクエリ
サブクエリ（副問い合わせ）とは、SELECT文の中で入れ子になった別のSELECT文です。
構文を以下に記載します。
SELECT * FROM テーブル名 WHERE カラム名 = (
SELECT 単一カラム FROM 他テーブル WHERE 条件
);
サブクエリの使用場所
サブクエリは以下の場所で使用することができます。
- WHERE 句：値の条件に使う
- FROM 句：サブクエリの結果を「仮想テーブル」として使う
- SELECT 句：単一値を取得して、表示用のカラムとして扱うことができます
このようにサブクエリは別のステートメントから取得した値を条件として使用することができます。
サブクエリの特徴
サブクエリの特徴をいかに記載します。
- サブクエリは「1つの値」だけを返すもの（スカラー）や、
「1列・複数行」、「複数列・複数行」など、さまざまな形の結果を返すことができます。 - サブクエリは
GROUP BYやORDER BY、LIMITなど多くの句を使用することができる
実際に簡単なSQLを構築してみましょう。
SELECT * FROM users WHERE id=(
SELECT user_id FROM orders WHERE id = 1
);
結果
usersテーブルのid=1のレコードが取得できました。
サブクエリ
これをわかりやすく分解していきましょう
まず、サブクエリである以下を実行します
SELECT user_id FROM orders WHERE id = 1
user_idが取得できました。
つまり、これをsqlに反映すると以下のようになります。
SELECT * FROM users WHERE id=1
結果を確認すると、先ほどの結果と同じになることがわかります。
このようにサブクエリはクエリの実行結果を条件式として使用することができます。
例文
以下にWHERE、FROM、SELECTでサブクエリを使用する構文をそれぞれ記載します。
WHERE
単一の値、または複数行を返却するようにします。
--単一の値
SELECT * FROM テーブル名 WHERE カラム名 = (
SELECT 単一カラム FROM 他テーブル WHERE 条件
);
--複数行(in句などで指定する場合)
--in句の場合
SELECT * FROM テーブル名 WHERE カラム名 in (
SELECT 単一カラム FROM 他テーブル WHERE 条件
);
--EXISTSの場合
SELECT * FROM テーブル名 WHERE EXISTS (
SELECT 単一カラム FROM 他テーブル WHERE 条件
);
FROM
テーブル形式で値を返却するようにします。
SELECT * FROM (
SELECT * FROM テーブル名
) AS エイリアス(別名);
※ASを使用しないとエラーになるため注意しましょう。
SELECT
単一の値(スカラー)のみを返却するようにします。
SELECT name, (
SELECT カラム名 FROM テーブル名 WHERE カラム名=値
) FROM テーブル名;
演習
演習１：最新の注文をしたユーザーの名前を取得せよ
- ordersテーブルの中で一番新しいpurchase_dateの注文をしたuserの名前を出力
演習2：Taroが購入した商品の名前一覧を取得せよ（サブクエリ + IN）
- Taroのuser_idをサブクエリで取得し、それを元にordersからname一覧を取得
演習3：価格が全体平均以上の注文だけを取得せよ（サブクエリ + 比較）
- orders.price >= (全体の平均価格) でフィルターする
演習4：全ユーザーの最新の注文日を一覧で取得せよ（サブクエリ + FROM）
-
サブクエリで各ユーザーごとの最新purchase_dateを抽出
-
外側のSELECTで user_id と日付を一覧で取得
演習5：存在しないユーザーが行った注文の一覧を取得せよ（サブクエリ + NOT IN）
- orders.user_id が users.id に存在しないものだけを取得
回答
演習１：最新の注文をしたユーザーの名前を取得せよ
解説
それでは今回のSQLを解説していきます。
SQLの条件を見ていきましょう
- ordersテーブルの中で一番新しいpurchase_dateの注文をしたuserの名前を出力
まずはこの条件のうち、「ordersテーブルの中で一番新しいpurchase_date」を取得していきましょう。
そのために、最初にORDER BYを使用し、購入日順に並べ替えます。
SELECT name, purchase_date FROM orders ORDER BY purchase_date DESC;
実行結果
ORDER BYを使用して、purchase_dateを降順に並べ替えました。
次に、ユーザー名を取得していきましょう
SELECT name, purchase_date,
(
SELECT name FROM users WHERE id=orders.user_id
)
FROM orders ORDER BY purchase_date DESC;
サブクエリで、order.user_idとusers.idが一致する値を取得しました。
SELECTのサブクエリは各行に対して実行され、一致する値を出力します。
次に一番新しい値を取得してみましょう
SELECT name, purchase_date,
(
SELECT name FROM users WHERE id=orders.user_id
)
FROM orders ORDER BY purchase_date DESC LIMIT 1;
LIMIT句を使用することで取得できる行数を制限できます。
今回は最新の1行だけ取得したのでLIMIT 1とします。
NULLが取得できました。
しかし、今回取得したいのは「一番新しいpurchase_dateの注文をしたuserの名前を出力」です。
そのため、NULLではなくユーザー名を取得する必要があります。
サブクエリを使用してusersテーブルにユーザーデータがある場合だけ表示してみましょう。
--IN句の場合
SELECT name, purchase_date,
(
SELECT name FROM users WHERE id=orders.user_id
) AS 'ユーザー名'
FROM orders WHERE user_id IN
(
SELECT id FROM users
)
ORDER BY purchase_date DESC LIMIT 1;
--EXISTS句の場合
SELECT name, purchase_date,
(
SELECT name FROM users WHERE id=orders.user_id
) AS 'ユーザー名'
FROM orders WHERE EXISTS
(
SELECT id FROM users WHERE id=orders.user_id
)
ORDER BY purchase_date DESC LIMIT 1;
※このEXISTSの使い方は「データが存在するかの判定」としては機能しますが、IN句と同様の結果になります。実務では、より複雑な絞り込み（相関サブクエリなど）でEXISTSが真価を発揮します。
この場合以下のように変換できます。
--IN句の場合
SELECT name, purchase_date,
(
SELECT name FROM users WHERE id=orders.user_id
) AS 'ユーザー名'
FROM orders WHERE user_id in
(
1,2,3,4
)
ORDER BY purchase_date DESC LIMIT 1;
--EXISTS句の場合
SELECT name, purchase_date,
(
SELECT name FROM users WHERE id=orders.user_id
) AS 'ユーザー名'
FROM orders WHERE EXISTS
(
orders.user_idと一致するuser.idを取得
)
ORDER BY purchase_date DESC LIMIT 1;
usersテーブルを確認すると、idは1,2,3,4が存在しています。
WHERE句で使用する場合、in句を使用することで複数の値を指定することができます。
実行すると、以下のようになります。
このように最新の注文をしたユーザーIDを取得することができました。
その他演習も同じように組み立てられます。
以下演習2以降も考え方自体は変わりません。
そのため、回答と補足のみ記載させていただきます
演習2
SELECT name FROM orders WHERE orders.user_id =
(
SELECT id FROM users WHERE name='Taro'
)
演習3
SELECT * FROM orders WHERE price >=
(
SELECT AVG(price) FROM orders
)
演習4
SELECT user_id, (
SELECT name FROM users WHERE id=orders.user_id
) AS username, MAX(purchase_date)
FROM orders GROUP BY user_id HAVING (
SELECT name FROM users WHERE id=orders.user_id
) IS NOT NULL
演習5
SELECT * FROM orders WHERE user_id NOT IN
(
SELECT id FROM users
)
まとめ
今回はサブクエリにサブクエリを使用した演習を通じて
使い方を解説しました。
サブクエリには相関サブクエリと非相関サブクエリがあります。
今回学んだのは非相関サブクエリです。
非相関サブクエリに関してはまた次回以降に解説できればと考えています。
次回は非相関サブクエリ/パフォーマンスチューニングなどを解説していければと考えています。
引き続き、SQLの基礎力を高める学習記事を投稿していきます！