Wednesday, January 30, 2019

What the Heck is a Lateral Derived Table?

MySQL 8.0.14 came with Lateral Derived Tables as a new feature.  But what is that and how do you use them?

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, 
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 too.

Quick Conclusion

So with lateral derived tables and Common Table Expressions (CTEs), it has become much easier to write sunqueries with MySQL.