Becoming Familiar With the Little Known SQL Keyword LATERAL

March 31, 2020

Author

Vadim Tkachenko

MySQL

Share this Post:

SQL Keyword Lateral Working on a weekend project, I became familiar with the SQL keyword “LATERAL”, which I had not used before because it was only introduced recently in MySQL 8.0.14, so I wanted to share how it can be used.

Some references on this topic:

Lateral Derived Tables

LATERAL Derived Tables in MySQL 8.0

The keyword “LATERAL” comes into play when you work with derived tables. The derived tables have been available in MySQL for a long time, and schematically they look like this:

SELECT t1.foo, t2.bar FROM t1, (SELECT bar FROM table2 WHERE <condition> ) t2 WHERE t1.id=t2.id;

1	SELECT t1.foo, t2.bar FROM t1, (SELECT bar FROM table2 WHERE <condition> ) t2 WHERE t1.id=t2.id;

The table “ (SELECT bar FROM table1 WHERE <condition> ) t2” is used in FROM condition is a derived table, and you can join regular and derived tables normally.

Derived tables are typically executed as materialized (ONCE per query and stored as “cached” in temporary tables) or via an outer table.

So what’s the problem with derived tables? You will find a limitation when you will try to use a condition inside a derived table that references an outside table. For example:

SELECT t1.foo, t2.bar FROM t1, (SELECT bar FROM table2 WHERE <condition> AND table2.date=t1.date ) t2 WHERE t1.id=t2.id;

1	SELECT t1.foo, t2.bar FROM t1, (SELECT bar FROM table2 WHERE <condition> AND table2.date=t1.date ) t2 WHERE t1.id=t2.id;

This is not allowed, and to be able to use this query you need to specify a keyword LATERAL, so the query will look like:

SELECT t1.foo, t2.bar FROM t1, LATERAL (SELECT bar FROM table2 WHERE <condition> AND table2.date=t1.date ) t2 WHERE t1.id=t2.id;

1	SELECT t1.foo, t2.bar FROM t1, LATERAL (SELECT bar FROM table2 WHERE <condition> AND table2.date=t1.date ) t2 WHERE t1.id=t2.id;

What is a drawback? Well, the derived query can’t be executed now ONCE per query and will be executed FOR EACH row from table t1, so obviously, it will come with a performance penalty and should be used carefully.

You may ask, then, when do we need to use LATERAL as we were just fine before MySQL 8.0.14?

Actually I came to this query working with timeseries and forecast (prediction data). For example, for each day, we have a metric prediction for each of 30 days ahead.

E.g.

Prediction date

Date in the future

Metric prediction

3/28/2020

3/29/2020

3/28/2020

3/30/2020

3/28/2020

3/31/2020

… 27 more rows for 3/28/2020

…

3/29/2020

3/30/2020

3/29/2020

3/31/2020

3/29/2020

4/1/2020

… 27 more rows for 3/29/2020

For each prediction date in the table, I need to find the metric prediction ONLY for the next day.

There are probably multiple ways to write this query, and also I was looking to handle this in the application, but with the LATERAL derived table you can write this as:

SELECT * FROM prediction_table q , 
LATERAL ( SELECT date_in_future,DATEDIFF(date_in_future, date_prediction) df  
   FROM prediction_table q1 
   WHERE q1.date_prediction=q.date_prediction ORDER BY df ASC LIMIT 1)  q2 
WHERE q2.date_in_future=q.date_in_future;

SELECT * FROM prediction_table q ,

LATERAL ( SELECT date_in_future,DATEDIFF(date_in_future, date_prediction) df

FROM prediction_table q1

WHERE q1.date_prediction=q.date_prediction ORDER BY df ASC LIMIT 1) q2

WHERE q2.date_in_future=q.date_in_future;

And if you need not exactly tomorrow, but rather for N days ahead, you can use WINDOW function

RANK() OVER ( ORDER BY DATEDIFF(expiration,tradedate)) r.

So, the query will look like:

SELECT * FROM prediction_table q , 
LATERAL ( SELECT date_in_future,RANK() OVER ( ORDER BY  DATEDIFF(expiration,tradedate)) r 
   FROM prediction_table q1 
   WHERE q1.date_prediction=q.date_prediction)  q2 
WHERE q2.date_in_future=q.date_in_future AND r=N;