このページは機械翻訳したものです。

13.2.11.8 導出テーブル

このセクションでは、導出テーブルの一般的な特性について説明します。 LATERAL キーワードで始まるラテラル導出テーブルの詳細は、セクション13.2.11.9「ラテラル導出テーブル」 を参照してください。

導出テーブルは、クエリーの FROM 句の有効範囲内にテーブルを生成する式です。 たとえば、SELECT ステートメントの FROM 句のサブクエリーは導出テーブルです:

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

JSON_TABLE() 関数は、テーブルを生成し、導出テーブルを作成する別の方法を提供します:

SELECT * FROM JSON_TABLE(arg_list) [AS] tbl_name ...

FROM 句のすべてのテーブルに名前が必要なため、[AS] tbl_name 句は必須です。 導出テーブルのカラムには一意の名前を付ける必要があります。 または、tbl_name の後に、導出テーブルのカラムの名前をカッコで囲んだリストを続けることもできます:

SELECT ... FROM (subquery) [AS] tbl_name (col_list) ...

カラム名の数は、テーブルのカラムの数と同じである必要があります。

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

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;

結果:

+------+------+------+
| sb1  | sb2  | sb3  |
+------+------+------+
|    2 | 2    |    4 |
+------+------+------+

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

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) は外部クエリーで認識されます。

導出テーブルのカラム名は、その選択リストから取得されます:

mysql> SELECT * FROM (SELECT 1, 2, 3, 4) AS dt;
+---+---+---+---+
| 1 | 2 | 3 | 4 |
+---+---+---+---+
| 1 | 2 | 3 | 4 |
+---+---+---+---+

カラム名を明示的に指定するには、導出テーブル名の後にカラム名のカッコ付きリストを付けます:

mysql> SELECT * FROM (SELECT 1, 2, 3, 4) AS dt (a, b, c, d);
+---+---+---+---+
| a | b | c | d |
+---+---+---+---+
| 1 | 2 | 3 | 4 |
+---+---+---+---+

導出テーブルは、スカラー、カラム、行またはテーブルを戻すことができます。

導出テーブルには、次の制限事項があります:

• 導出テーブルには、同じ SELECT の他のテーブルへの参照を含めることはできません (そのためには LATERAL 導出テーブルを使用します。セクション13.2.11.9「ラテラル導出テーブル」 を参照)。

• MySQL 8.0.14 より前は、導出テーブルに外部参照を含めることはできません。 これは、SQL 標準の制限ではなく、MySQL 8.0.14 で削除された MySQL 制限です。 たとえば、次のクエリーの導出テーブル dt には、外部クエリーのテーブル t1 への参照 t1.b が含まれています:

  SELECT * FROM t1
  WHERE t1.d > (SELECT AVG(dt.a)
                  FROM (SELECT SUM(t2.a) AS a
                        FROM t2
                        WHERE t2.b = t1.b GROUP BY t2.c) dt
                WHERE dt.a > 10);

  クエリーは、MySQL 8.0.14 以上で有効です。 8.0.14 より前は、エラーが生成されます: Unknown column 't1.b' in 'where clause'

オプティマイザは、導出テーブルに関する情報を、EXPLAIN が導出テーブルを実体化する必要がないように決定します。 セクション8.2.2.4「マージまたは実体化を使用した導出テーブル、ビュー参照および共通テーブル式の最適化」を参照してください。

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

CREATE DATABASE d1;
USE d1;
CREATE TABLE t1 (c1 INT);
CREATE TABLE t2 (c1 INT);
CREATE FUNCTION f1(p1 INT) RETURNS INT
  BEGIN
    INSERT INTO t2 VALUES (p1);
    RETURN p1;
  END;

次に示すように、EXPLAIN SELECT で関数を直接参照しても、t2 には影響しません:

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

mysql> EXPLAIN SELECT f1(5)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
   partitions: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: No tables used
1 row in set (0.01 sec)

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

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

mysql> EXPLAIN SELECT NOW() AS a1, (SELECT f1(5)) AS a2\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: 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 がいずれかのテーブルを参照する場合、オプティマイザはサブクエリーのステートメントも実行し、t2 が変更されます:

mysql> EXPLAIN SELECT * FROM t1 AS a1, (SELECT f1(5)) AS a2\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
   partitions: NULL
         type: system
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: a1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: NULL
   partitions: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: 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())));