13.2.10.8 FROM 句内のサブクエリー

サブクエリーは、SELECT ステートメントの FROM 句内で正当です。その実際の構文は次のとおりです。

SELECT ... FROM (subquery) [AS] name ...

FROM 句内のどのテーブルも名前を持っている必要があるため、[AS] name 句は必須です。subquery 選択リスト内にカラムが存在する場合は、それが一意の名前を持っている必要があります。

説明のために、次のテーブルがあるとします。

CREATE TABLE t1 (s1 INT, s2 CHAR(5), s3 FLOAT);

このテーブルの例を使用して、FROM 句内のサブクエリーを使用する方法を次に示します。

INSERT INTO t1 VALUES (1,'1',1.0);
INSERT INTO t1 VALUES (2,'2',2.0);
SELECT sb1,sb2,sb3
  FROM (SELECT s1 AS sb1, s2 AS sb2, s3*2 AS sb3 FROM t1) AS sb
  WHERE sb1 > 1;

結果: 2, '2', 4.0。

次に別の例を示します。グループ化されたテーブルに関する一連の合計の平均を知りたいとします。次のクエリーは機能しません。

SELECT AVG(SUM(column1)) FROM t1 GROUP BY column1;

ただし、次のクエリーは目的の情報を提供します。

SELECT AVG(sum_column1)
  FROM (SELECT SUM(column1) AS sum_column1
        FROM t1 GROUP BY column1) AS t1;

サブクエリー内で使用されているカラム名 (sum_column1) は外部クエリーで認識されます。

FROM 句内のサブクエリーは、スカラー、カラム、行、またはテーブルを返すことができます。FROM 句内のサブクエリーは、JOIN 操作の ON 句内で使用されないかぎり、相関サブクエリーにすることはできません。

MySQL 5.6.3 より前は、FROM 句内のサブクエリーは EXPLAIN ステートメントに対しても実行されます (つまり、派生した一時テーブルが実体化されます)。これは、最適化フェーズ中に上位レベルのクエリーにすべてのテーブルに関する情報が必要であり、かつサブクエリーが実行されないかぎり FROM 句内のサブクエリーによって表されているテーブルを使用できないために発生します。MySQL 5.6.3 の時点では、オプティマイザは派生テーブルに関する情報を別の方法で特定するため、それらの実体化が EXPLAIN に対して発生しません。セクション8.2.1.18.3「FROM 句内のサブクエリー (派生テーブル) の最適化」を参照してください。

特定の状況では、EXPLAIN SELECT を使用してテーブルデータを変更できます。これは、外部クエリーがいずれかのテーブルにアクセスし、内部クエリーが、テーブルの 1 つ以上の行を変更するストアドファンクションを呼び出す場合に発生する可能性があります。データベース d1 内に、次に示すように作成された 2 つのテーブル t1 と t2 があるとします。

mysql> CREATE DATABASE d1;
Query OK, 1 row affected (0.00 sec)

mysql> USE d1;
Database changed

mysql> CREATE TABLE t1 (c1 INT);
Query OK, 0 rows affected (0.15 sec)

mysql> CREATE TABLE t2 (c1 INT);
Query OK, 0 rows affected (0.08 sec)

ここで、t2 を変更するストアドファンクション f1 を作成します。

mysql> DELIMITER //
mysql> CREATE FUNCTION f1(p1 INT) RETURNS INT
mysql>   BEGIN
mysql>     INSERT INTO t2 VALUES (p1);
mysql>     RETURN p1;
mysql>   END //
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;

次に示すように、EXPLAIN SELECT でこの関数を直接参照しても、t2 には何も影響を与えません。

mysql> SELECT * FROM t2;
Empty set (0.00 sec)

mysql> EXPLAIN SELECT f1(5);
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM t2;
Empty set (0.00 sec)

これは、出力の table および Extra カラムでわかるように、SELECT ステートメントがどのテーブルも参照しなかったためです。これはまた、次のネストされた SELECT にも当てはまります。

mysql> EXPLAIN SELECT NOW() AS a1, (SELECT f1(5)) AS a2;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | PRIMARY     | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+-------+------+------------------------------------------+
| Level | Code | Message                                  |
+-------+------+------------------------------------------+
| Note  | 1249 | Select 2 was reduced during optimization |
+-------+------+------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM t2;
Empty set (0.00 sec)

ただし、外部の SELECT がいずれかのテーブルを参照している場合、オプティマイザはそのサブクエリー内のステートメントも実行します。

mysql> EXPLAIN SELECT * FROM t1 AS a1, (SELECT f1(5)) AS a2;
+----+-------------+------------+--------+---------------+------+---------+------+------+---------------------+
| id | select_type | table      | type   | possible_keys | key  | key_len | ref  | rows | Extra               |
+----+-------------+------------+--------+---------------+------+---------+------+------+---------------------+
|  1 | PRIMARY     | a1         | system | NULL          | NULL | NULL    | NULL |    0 | const row not found |
|  1 | PRIMARY     | <derived2> | system | NULL          | NULL | NULL    | NULL |    1 |                     |
|  2 | DERIVED     | NULL       | NULL   | NULL          | NULL | NULL    | NULL | NULL | No tables used      |
+----+-------------+------------+--------+---------------+------+---------+------+------+---------------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM t2;
+------+
| c1   |
+------+
|    5 |
+------+
1 row in set (0.00 sec)

これはまた、次に示すような EXPLAIN SELECT ステートメントは、t1 内の行ごとに 1 回 BENCHMARK() 関数が実行されるため、実行に長い時間がかかる可能性があることも示しています。

EXPLAIN SELECT * FROM t1 AS a1, (SELECT BENCHMARK(1000000, MD5(NOW())));