Becoming Familiar With the Little Known SQL Keyword LATERAL

SQL Keyword LateralWorking 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:

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:

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:

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

5

3/28/2020

3/30/2020

8

3/28/2020

3/31/2020

10

… 27 more rows for 3/28/2020

3/29/2020

3/30/2020

3

3/29/2020

3/31/2020

7

3/29/2020

4/1/2020

4

… 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:

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:

I am happy to see that MySQL 8 comes with a new rich set for SQL functions, which makes working with queries much easier.

Share this post

Comments (3)

  • Carter Reply

    I am more familiar in MSSQL but is it the same as Cross/outer apply?

    If so it can make queries so much easier.

    April 1, 2020 at 9:31 pm
  • JB Reply

    I understand that the prediction table example is here to illustrate lateral keyword usage. However, don’t you think the following query could be faster?

    select * from prediction_table
    where datediff(date_in_future, date_prediction) = N
    order by date_prediction, date_in_future;

    April 2, 2020 at 2:33 am
    • Vadim Tkachenko Reply

      JB,

      You are right, for this particular example your query should work.
      I had a more complex case, which I tried to simplify to show how to use LATER, and I oversimplified it too much.

      April 2, 2020 at 6:34 am

Leave a Reply