Subqueries in a FROM Clause
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.
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.
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);
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.