Let's start what a derived table is. According to the manual (link below) A derived table is an expression that generates a table within the scope of a query FROM clause. You are probably used to using a subquery or JSON_TABLE where that query feeds data into another query.
Derived Tables
Derived tables can return a scalar, column, row, or table. But there are limits to their use.
A derived table cannot be a correlated subquery and a derived table cannot contain references to other tables of the same SELECT. And previous to MySQL 8.0.14, a derived table cannot contain outer references.
Okay, so what is an 'outer reference'? The SQL-92 standard states that derived tables cannot depend on other tables in the same FROM clause.and cannot contain references to columns of other FROM clause tables. But SQL-99 changed that with the keyword LATERAL (think sudo) so you can reference previously mention table.
Overly Simplified Example
The following is a very contrived query to illustrate the use of the LATERAL keyword. The query behind the LATERAL is returning the country.Code column to the query in front of the LATERAL. I am using the good old World database from the MySQL example data sets.
SELECT Name, Population, District, x.cc
FROM city,
LATERAL (SELECT Code AS cc FROM country
WHERE city.CountryCode = Code) AS x
WHERE District = 'Texas' ORDER BY name;
Now if you remove that the LATERAL from the above query, you will see the following error message. (Please do try the above query with and without LATERAL as a learning exercise)
Error Code: 1054. Unknown column 'city.CountryCode' in 'where clause'
So with the LATERAL keyword in place, the left query (the one before the LATERAL) provides that city.CountryCode column to the right query (the one after the lateral). In other words the query behind the LATERAL keyword is depending on the previous query. Since we named the derived table as x we need to remember to reference it in the other table with x too.
Quick Conclusion
So with lateral derived tables and Common Table Expressions (CTEs), it has become much easier to write sunqueries with MySQL.
