Subqueries in a FROM Clause (Derived Tables)
Contents
Although subqueries are more commonly placed in a WHERE clause, they can also form part of the FROM clause. Such subqueries are commonly called derived tables.
If a subquery is used in this way, you must also use an AS clause to name the result of the subquery.
ORACLE mode
MariaDB starting with 10.6.0
From MariaDB 10.6.0, anonymous subqueries in a FROM clause (no AS clause) are permitted in ORACLE mode.
Correlation Column List
MariaDB starting with 11.7.0
From MariaDB 11.7, it is possible to assign column names in the derived table name syntax element.
Examples
CREATE TABLE student (name CHAR(10), test CHAR(10), score TINYINT); INSERT INTO student VALUES ('Chun', 'SQL', 75), ('Chun', 'Tuning', 73), ('Esben', 'SQL', 43), ('Esben', 'Tuning', 31), ('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88), ('Tatiana', 'SQL', 87), ('Tatiana', 'Tuning', 83);
Assume that, given the data above, you want to return the average total for all students. In other words, the average of Chun's 148 (75+73), Esben's 74 (43+31), etc.
You cannot do the following:
SELECT AVG(SUM(score)) FROM student GROUP BY name; ERROR 1111 (HY000): Invalid use of group function
A subquery in the FROM clause is however permitted:
SELECT AVG(sq_sum) FROM (SELECT SUM(score) AS sq_sum FROM student GROUP BY name) AS t; +-------------+ | AVG(sq_sum) | +-------------+ | 134.0000 | +-------------+
From MariaDB 10.6 in ORACLE mode, the following is permitted:
SELECT * FROM (SELECT 1 FROM DUAL), (SELECT 2 FROM DUAL);
From MariaDB 11.7. In this example, the second column of the derived table dt
is used both within (WHERE c2 > 0), and outside, (WHERE a2 > 10), the specification. Both conditions apply to t1.c2.
CREATE OR REPLACE TABLE t1(c1 INT, c2 INT, c3 INT); SELECT a1, a2 FROM (SELECT c1, c2, c3 FROM t1 WHERE c2 > 0) AS dt (a1, a2, a3);