> ## Documentation Index
> Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# MySQL の相関サブクエリ

> MySQL の相関サブクエリが外側の行の列を読む仕組み、それによって行ごとに 1 回実行される理由、およびそのコストを払う価値がある場合。

> [card] [VillageSQL is a drop-in replacement for MySQL with extensions.](https://villagesql.com/docs/en/8.4/stable/install-server.md)
>
> All examples on this page work on VillageSQL. Install Now →

通常のサブクエリは 1 回実行され、答えを返します。相関サブクエリは外側のクエリの
列を参照するため、外側の行ごとに異なる答えを持ち、サーバーはそれぞれについて
再実行しなければなりません。

始める前に `mysql -u root -p sakila` でクライアントを開いてください。

## 相関サブクエリの仕組み

```sql theme={null}
SELECT COLUMN_LIST
FROM TABLE_NAME AS OUTER_ALIAS
WHERE COLUMN_NAME > (
  SELECT AGGREGATE_FUNCTION(COLUMN_NAME)
  FROM TABLE_NAME AS INNER_ALIAS
  WHERE INNER_ALIAS.COLUMN_NAME = OUTER_ALIAS.COLUMN_NAME
);
```

`COLUMN_LIST` を必要な列に、`TABLE_NAME` をテーブルに、`COLUMN_NAME` を比較する
列に、`AGGREGATE_FUNCTION` を内側のクエリが実行する関数に、`OUTER_ALIAS` と
`INNER_ALIAS` を関係するテーブルの 2 つの名前に置き換えます。
この 2 つのエイリアスがこれを機能させます。内側のクエリが外側のクエリを名前で
参照し、それが相関です。

外側の行ごとに 1 回、その行の値を埋めて問われる質問として読んでください。

## 答える質問

「平均より長い映画はどれか」には 1 つの平均が必要です。「**そのレーティングの
中で**平均より長い映画はどれか」には行ごとに異なる平均が必要で、相関サブクエリが
与えてくれるのはそれです。

```sql theme={null}
SELECT f.title, f.rating, f.length
FROM film AS f
WHERE f.length > (SELECT AVG(f2.length) FROM film AS f2 WHERE f2.rating = f.rating)
ORDER BY f.rating, f.title
LIMIT 5;
```

```text theme={null}
+------------------+--------+--------+
| title            | rating | length |
+------------------+--------+--------+
| AFFAIR PREJUDICE | G      |    117 |
| AFRICAN EGG      | G      |    130 |
| ALAMO VIDEOTAPE  | G      |    126 |
...
+------------------+--------+--------+
5 rows in set (0.12 sec)
```

`f2.rating = f.rating` が重要な部分です。内側のクエリをテスト中の行に結び付ける
ため、`G` の映画は `G` の平均と、`R` の映画は `R` の平均と比較されます。

その部分を取り除くと、サブクエリはカタログ全体の平均を取り、毎回同じ値になり、
クエリは別の質問に答えます。

## 行ごとに 1 回実行される

上の実行時間を、[サブクエリ](https://villagesql.com/docs/ja/tutorial/subqueries.md)のレッスンのクエリと比べて
ください。あちらは速すぎて計測できません。こちらは 1,000 行に対して 10 分の 1 秒
かかります。正確な数値は環境によって異なりますが、サーバーが内側のクエリを 1 回
ではなく外側の行ごとに評価するためです。

これがトレードオフのすべてです。相関サブクエリは「各行を自分のグループと比較
する」を書く最も明快な方法であり、そのコストはテーブルとともに増えます。

MySQL は一部の相関サブクエリを自分で結合に書き換えますし、同じ質問に 1 パスで
答える別の形もあります。
[MySQL におけるサブクエリと JOIN の比較](https://villagesql.com/docs/ja/guides/subqueries-vs-joins.md)では、その
両方と、どちらになったかを教えてくれるプランの読み方を扱います。

## まとめ

* 各行を、自分のグループから計算した何かと比較するには相関サブクエリを使います。
* 内側のクエリは外側のクエリを参照しなければならないため、両方のテーブルにエイリアスを付けます。
* 外側の行ごとに 1 回実行されると考えてください。
* テーブルが大きいときは、派生テーブルと結合に書き換えます。

## 関連項目

* [サブクエリ](https://villagesql.com/docs/ja/tutorial/subqueries.md) — 1 回だけ実行される、相関のない種類
* [EXISTS](https://villagesql.com/docs/ja/tutorial/exists.md) — 値ではなくテストとして使う相関サブクエリ
* [派生テーブル](https://villagesql.com/docs/ja/tutorial/derived-tables.md) — より安価な書き換え
* [MySQL におけるサブクエリと JOIN の比較](https://villagesql.com/docs/ja/guides/subqueries-vs-joins.md) — 判断のためにプランを読む


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.