MySQL :: MySQL 8.0 リファレンスマニュアル

9.2 English
9.1 English
9.0 English
8.4 English
8.0 English
5.7 English

MySQL 8.0 リファレンスマニュアル / ... / 導出テーブル

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

13.2.11.8 導出テーブル

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

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

Press CTRL+C to copy
 
SELECT ... FROM (subquery) [AS] tbl_name ...

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

Press CTRL+C to copy
 
SELECT * FROM JSON_TABLE(arg_list) [AS] tbl_name ...

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

Press CTRL+C to copy
 
SELECT ... FROM (subquery) [AS] tbl_name (col_list) ...

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

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

Press CTRL+C to copy
 
CREATE TABLE t1 (s1 INT, s2 CHAR(5), s3 FLOAT);

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

Press CTRL+C to copy
 
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;

結果:

Press CTRL+C to copy
 
+------+------+------+
| sb1  | sb2  | sb3  |
+------+------+------+
|    2 | 2    |    4 |
+------+------+------+

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

Press CTRL+C to copy
 
SELECT AVG(SUM(column1)) FROM t1 GROUP BY column1;

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

Press CTRL+C to copy
 
SELECT AVG(sum_column1)
  FROM (SELECT SUM(column1) AS sum_column1
        FROM t1 GROUP BY column1) AS t1;

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

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

Press CTRL+C to copy
 
mysql> SELECT * FROM (SELECT 1, 2, 3, 4) AS dt;
+---+---+---+---+
| 1 | 2 | 3 | 4 |
+---+---+---+---+
| 1 | 2 | 3 | 4 |
+---+---+---+---+

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

Press CTRL+C to copy
 
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 が含まれています:
```
Press CTRL+C to copy
 
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 が作成されているとします:

Press CTRL+C to copy
 
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 には影響しません:

Press CTRL+C to copy
 
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 にも当てはまります。

Press CTRL+C to copy
 
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 が変更されます:

Press CTRL+C to copy
 
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() 関数が実行されるため、実行に長い時間がかかる可能性があることも示しています。

Press CTRL+C to copy
 
EXPLAIN SELECT * FROM t1 AS a1, (SELECT BENCHMARK(1000000, MD5(NOW())));

PREV HOME UP NEXT