MySQL/MariaDB - 上級者向けサブクエリテクニック：ORDER BY
MySQL/MariaDBにおけるサブクエリ内のORDER BY
サブクエリ内のORDER BYは、複雑なデータ抽出を可能にする強力なツールです。しかし、その動作は直感と異なる場合があり、意図した結果を得られないこともあります。
動作
MySQL/MariaDBでは、サブクエリ内のORDER BYは 無視 されます。代わりに、外側のクエリでORDER BYが適用されます。
例
以下の例では、サブクエリ内のORDER BYは無視され、外側のクエリに基づいて結果がソートされます。
SELECT *
FROM (
SELECT *
FROM users
ORDER BY name ASC
) AS t
ORDER BY age DESC;
このクエリは、まずname昇順でソートされたusersテーブルのすべての行を選択します。その後、外側のクエリでage降順でソートされます。
サブクエリ内のORDER BYは、以下のケースで役立ちます。
- 外側のクエリで集計関数を使用する場合
- サブクエリの結果を別のテーブルに挿入する場合
以下の例では、サブクエリ内のORDER BYを使用して、各ユーザーの最高得点を選択します。
SELECT user_id, MAX(score) AS max_score
FROM (
SELECT *
FROM scores
ORDER BY score DESC
) AS t
GROUP BY user_id;
注意事項
- サブクエリ内のORDER BYは、外側のクエリで使用する列を含んでいる必要があります。
補足
- 上記の情報は、MySQL 8.0.28に基づいています。
- バージョンによって動作が異なる場合があります。
サブクエリ内のORDER BYが無視される例
-- サブクエリ内のORDER BYは無視される
SELECT *
FROM (
SELECT *
FROM users
ORDER BY name ASC
) AS t
ORDER BY age DESC;
結果:
| id | name | age |
|---|---|---|
| 1 | Alice | 20 |
| 2 | Bob | 30 |
| 3 | Charlie | 40 |
サブクエリ内のORDER BYを使用する例
-- サブクエリ内のORDER BYを使用して、各ユーザーの最高得点を選択
SELECT user_id, MAX(score) AS max_score
FROM (
SELECT *
FROM scores
ORDER BY score DESC
) AS t
GROUP BY user_id;
このクエリは、scoresテーブルのすべての行をscore降順でソートし、その結果をuser_idごとにscoreの最大値を計算します。
| user_id | max_score |
|---|---|
| 1 | 100 |
| 2 | 90 |
| 3 | 80 |
-- サブクエリ内のORDER BYがパフォーマンスに影響を与える例
SELECT *
FROM (
SELECT *
FROM users
ORDER BY name ASC
LIMIT 10000
) AS t
ORDER BY age DESC;
このクエリは、usersテーブルの最初の10000行をname昇順でソートし、その結果をage降順でソートします。
このクエリは、次の理由でパフォーマンスが低下する可能性があります。
- サブクエリ内のORDER BYは、
usersテーブルのすべての行をソートする必要がある。 - 外側のクエリは、サブクエリからの結果をソートする必要がある。
パフォーマンスを向上させる方法:
- サブクエリ内のORDER BYを削除する。
- 外側のクエリで
ORDER BYを使用する前に、サブクエリでLIMITを使用する。
-- パフォーマンスを向上させる例
SELECT *
FROM (
SELECT *
FROM users
LIMIT 10000
) AS t
ORDER BY age DESC;
サブクエリ内のORDER BYの代わりに使用できる方法
方法1: 外側のクエリでORDER BYを使用する
SELECT *
FROM users
ORDER BY name ASC;
このクエリは、usersテーブルのすべての行をname昇順でソートします。
方法2: サブクエリでLIMITとOFFSETを使用する
SELECT *
FROM (
SELECT *
FROM users
ORDER BY name ASC
LIMIT 10
OFFSET 20
) AS t;
このクエリは、usersテーブルのname昇順でソートされた最初の10行を返します。
SELECT user_id, MAX(score) AS max_score
FROM scores
GROUP BY user_id;
方法4: CTE (Common Table Expressions)を使用する
WITH t AS (
SELECT *
FROM users
ORDER BY name ASC
)
SELECT *
FROM t;
このクエリは、usersテーブルのすべての行をname昇順でソートし、その結果をtという名前のCTEに保存します。その後、tからすべての行を選択します。
これらの方法は、サブクエリ内のORDER BYよりも効率的な場合があり、パフォーマンスを向上させることができます。
- サブクエリを別のクエリに分割する。
- ビューを作成する。
- ストアドプロシージャを使用する。
mysql sql subquery
