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.

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);

URL: https://mariadb.com/kb/en/subqueries-in-a-from-clause/