サブクエリーは、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())));