15.2.15.8 Derived Tables — MySQL 8.4

15.2.15.8 Производные таблицы

В этом разделе рассматриваются общие характеристики производных таблиц. Сведения о производных таблицах, предваряемых ключевым словом LATERAL, см. в разделе 15.2.15.9 «Производные таблицы с боковым слиянием».

Производная таблица — это выражение, генерирующее таблицу в рамках области действия запроса FROM. Например, подзапрос в операторе SELECT является производной таблицей:

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

Функция JSON_TABLE() генерирует таблицу и предоставляет другой способ создания производной таблицы:

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

Оператор [AS] tbl_name является обязательным, так как каждая таблица в операторе FROM должна иметь имя. Все столбцы в производной таблице должны иметь уникальные имена. В качестве альтернативы за именем производной таблицы может следовать список имён столбцов в скобках:

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; см. раздел 15.2.15.9 «Производные таблицы с боковым слиянием»).

Оптимизатор определяет информацию о производных таблицах таким образом, что EXPLAIN не нуждается в их материализации. См. раздел 10.2.2.4 «Оптимизация производных таблиц, ссылок на представления и общих табличных выражений с помощью объединения или материализации».

В некоторых случаях использование EXPLAIN SELECT может изменить данные таблицы. Это может произойти, если внешний запрос обращается к каким-либо таблицам, а внутренний запрос вызывает хранимую функцию, которая изменяет одну или несколько строк таблицы. Предположим, что в базе данных d1 существуют две таблицы t1 и t2, и хранимая функция f1, которая изменяет t2, создана следующим образом:

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)

Это потому, что оператор SELECT не ссылался ни на какие таблицы, как видно в столбцах table и Extra вывода. Это также верно для следующего вложенного оператора 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)

Оптимизация производных таблиц также может применяться к многим коррелированным (скалярным) подзапросам. Более подробная информация и примеры приведены в разделе 15.2.15.7 «Коррелированные подзапросы».