Rechercher dans le manuel MySQL

13.2.11.8 Derived Tables

Inhaltsverzeichnis

This section discusses general characteristics of derived tables. For information about lateral derived tables preceded by the LATERAL keyword, see Section 13.2.11.9, “Lateral Derived Tables”.

A derived table is an expression that generates a table within the scope of a query FROM clause. For example, a subquery in a SELECT statement FROM clause is a derived table:

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

The JSON_TABLE() function generates a table and provides another way to create a derived table:

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

The [AS] tbl_name clause is mandatory because every table in a FROM clause must have a name. Any columns in the derived table must have unique names. Alternatively, tbl_name may be followed by a parenthesized list of names for the derived table columns:

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

The number of column names must be the same as the number of table columns.

For the sake of illustration, assume that you have this table:

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

Here is how to use a subquery in the FROM clause, using the example table:

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

Result: 

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

Here is another example: Suppose that you want to know the average of a set of sums for a grouped table. This does not work:

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

However, this query provides the desired information:

  1. SELECT AVG(sum_column1)
  2.   FROM (SELECT SUM(column1) AS sum_column1
  3.         FROM t1 GROUP BY column1) AS t1;

Notice that the column name used within the subquery (sum_column1) is recognized in the outer query.

The column names for a derived table come from its select list:

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

To provide column names explicitly, follow the derived table name with a parenthesized list of column names:

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

A derived table can return a scalar, column, row, or table.

Derived tables are subject to these restrictions:

  • A derived table cannot be a correlated subquery.

  • A derived table cannot contain references to other tables of the same SELECT.

  • Prior to MySQL 8.0.14, a derived table cannot contain outer references. This is a MySQL restriction that is lifted in MySQL 8.0.14, not a restriction of the SQL standard. For example, the derived table dt in the following query contains a reference t1.b to the table t1 in the outer query:

    1. SELECT * FROM t1
    2. WHERE t1.d > (SELECT AVG(dt.a)
    3.                 FROM (SELECT SUM(t2.a) AS a
    4.                       FROM t2
    5.                       WHERE t2.b = t1.b GROUP BY t2.c) dt
    6.               WHERE dt.a > 10);

    The query is valid in MySQL 8.0.14 and higher. Before 8.0.14, it produces an error: Unknown column 't1.b' in 'where clause'

The optimizer determines information about derived tables in such a way that EXPLAIN does not need to materialize them. See Section 8.2.2.4, “Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization”.

It is possible under certain circumstances that using EXPLAIN SELECT will modify table data. This can occur if the outer query accesses any tables and an inner query invokes a stored function that changes one or more rows of a table. Suppose that there are two tables t1 and t2 in database d1, and a stored function f1 that modifies t2, created as shown here:

  1. CREATE DATABASE d1;
  2. USE d1;
  3. CREATE TABLE t1 (c1 INT);
  4. CREATE TABLE t2 (c1 INT);
  5. CREATE FUNCTION f1(p1 INT) RETURNS INT
  6.   BEGIN
  7.     INSERT INTO t2 VALUES (p1);
  8.     RETURN p1;
  9.   END;

Referencing the function directly in an EXPLAIN SELECT has no effect on t2, as shown here:

  1. mysql> SELECT * FROM t2;
  2. Empty set (0.02 sec)
  3.  
  4. mysql> EXPLAIN SELECT f1(5)\G
  5. *************************** 1. row ***************************
  6.            id: 1
  7.   select_type: SIMPLE
  8.         table: NULL
  9.    partitions: NULL
  10.          type: NULL
  11. possible_keys: NULL
  12.           key: NULL
  13.       key_len: NULL
  14.           ref: NULL
  15.          rows: NULL
  16.      filtered: NULL
  17.         Extra: No tables used
  18. 1 row in set (0.01 sec)
  19.  
  20. mysql> SELECT * FROM t2;
  21. Empty set (0.01 sec)

This is because the SELECT statement did not reference any tables, as can be seen in the table and Extra columns of the output. This is also true of the following nested SELECT:

  1. mysql> EXPLAIN SELECT NOW() AS a1, (SELECT f1(5)) AS a2\G
  2. *************************** 1. row ***************************
  3.            id: 1
  4.   select_type: PRIMARY
  5.         table: NULL
  6.          type: NULL
  7. possible_keys: NULL
  8.           key: NULL
  9.       key_len: NULL
  10.           ref: NULL
  11.          rows: NULL
  12.      filtered: NULL
  13.         Extra: No tables used
  14. 1 row in set, 1 warning (0.00 sec)
  15.  
  16. mysql> SHOW WARNINGS;
  17. +-------+------+------------------------------------------+
  18. | Level | Code | Message                                  |
  19. +-------+------+------------------------------------------+
  20. | Note  | 1249 | Select 2 was reduced during optimization |
  21. +-------+------+------------------------------------------+
  22. 1 row in set (0.00 sec)
  23.  
  24. mysql> SELECT * FROM t2;
  25. Empty set (0.00 sec)

However, if the outer SELECT references any tables, the optimizer executes the statement in the subquery as well, with the result that t2 is modified:

  1. mysql> EXPLAIN SELECT * FROM t1 AS a1, (SELECT f1(5)) AS a2\G
  2. *************************** 1. row ***************************
  3.            id: 1
  4.   select_type: PRIMARY
  5.         table: <derived2>
  6.    partitions: NULL
  7.          type: system
  8. possible_keys: NULL
  9.           key: NULL
  10.       key_len: NULL
  11.           ref: NULL
  12.          rows: 1
  13.      filtered: 100.00
  14.         Extra: NULL
  15. *************************** 2. row ***************************
  16.            id: 1
  17.   select_type: PRIMARY
  18.         table: a1
  19.    partitions: NULL
  20.          type: ALL
  21. possible_keys: NULL
  22.           key: NULL
  23.       key_len: NULL
  24.           ref: NULL
  25.          rows: 1
  26.      filtered: 100.00
  27.         Extra: NULL
  28. *************************** 3. row ***************************
  29.            id: 2
  30.   select_type: DERIVED
  31.         table: NULL
  32.    partitions: NULL
  33.          type: NULL
  34. possible_keys: NULL
  35.           key: NULL
  36.       key_len: NULL
  37.           ref: NULL
  38.          rows: NULL
  39.      filtered: NULL
  40.         Extra: No tables used
  41. 3 rows in set (0.00 sec)
  42.  
  43. mysql> SELECT * FROM t2;
  44. +------+
  45. | c1   |
  46. +------+
  47. |    5 |
  48. +------+
  49. 1 row in set (0.00 sec)

This also means that an EXPLAIN SELECT statement such as the one shown here may take a long time to execute because the BENCHMARK() function is executed once for each row in t1:

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

Suchen Sie im MySQL-Handbuch

Deutsche Übersetzung

Sie haben gebeten, diese Seite auf Deutsch zu besuchen. Momentan ist nur die Oberfläche übersetzt, aber noch nicht der gesamte Inhalt.

Wenn Sie mir bei Übersetzungen helfen wollen, ist Ihr Beitrag willkommen. Alles, was Sie tun müssen, ist, sich auf der Website zu registrieren und mir eine Nachricht zu schicken, in der Sie gebeten werden, Sie der Gruppe der Übersetzer hinzuzufügen, die Ihnen die Möglichkeit gibt, die gewünschten Seiten zu übersetzen. Ein Link am Ende jeder übersetzten Seite zeigt an, dass Sie der Übersetzer sind und einen Link zu Ihrem Profil haben.

Vielen Dank im Voraus.

Dokument erstellt 26/06/2006, zuletzt geändert 26/10/2018
Quelle des gedruckten Dokuments:https://www.gaudry.be/de/mysql-rf-derived-tables.html

Die Infobro ist eine persönliche Seite, deren Inhalt in meiner alleinigen Verantwortung liegt. Der Text ist unter der CreativeCommons-Lizenz (BY-NC-SA) verfügbar. Weitere Informationen auf die Nutzungsbedingungen und dem Autor.

Referenzen

  1. Zeigen Sie - html-Dokument Sprache des Dokuments:en Manuel MySQL : https://dev.mysql.com/

Diese Verweise und Links verweisen auf Dokumente, die während des Schreibens dieser Seite konsultiert wurden, oder die zusätzliche Informationen liefern können, aber die Autoren dieser Quellen können nicht für den Inhalt dieser Seite verantwortlich gemacht werden.
Der Autor Diese Website ist allein dafür verantwortlich, wie die verschiedenen Konzepte und Freiheiten, die mit den Nachschlagewerken gemacht werden, hier dargestellt werden. Denken Sie daran, dass Sie mehrere Quellinformationen austauschen müssen, um das Risiko von Fehlern zu reduzieren.

Inhaltsverzeichnis Haut