Overview of the LEAD() Function in MySQL

In MySQL, the LEAD() function is a window function that returns the value of a given expression from the row that leads (follows) the current row by a given number of rows within its partition.

Basically, it returns the value from a later row.

Syntax

The syntax goes like this:

LEAD(expr [, N[, default]]) [null_treatment] over_clause

So it requires an OVER clause, which determines how the rowset is partitioned and ordered before the window function is applied.

The optional N argument specifies the lead value. For example, 3 means three rows forward.

The optional default argument specifies what to return if there’s no value (this will happen when the lead value is greater than the number of rows remaining in the partition).

The optional null_treatment clause specifies how NULL values are treated. This clause is part of the SQL standard, but MySQL only allows RESPECT NULLS, which is also the default. RESPECT NULLS means that NULL values are considered when calculating results. On the other hand, IGNORE NULLS is parsed, but produces an error (at least as of MySQL 8.0.33).

Example

Here’s a basic example to demonstrate how it works:

SELECT 
    DogName,
    Activity,
    Score,
    LEAD( Score ) 
        OVER ( ORDER BY Score ) 
    AS "Lead"
FROM Dogs;

Result:

+---------+-------------+-------+------+
| DogName | Activity    | Score | Lead |
+---------+-------------+-------+------+
| Bruno   | Keep Quiet  |     1 |    8 |
| Cooper  | Keep Quiet  |     8 |   12 |
| Max     | Keep Quiet  |    12 |   43 |
| Bruno   | Fetch Stick |    43 |   51 |
| Bruno   | Wag Tail    |    51 |   51 |
| Cooper  | Wag Tail    |    51 |   67 |
| Cooper  | Fetch Stick |    67 |   87 |
| Max     | Wag Tail    |    87 |   91 |
| Max     | Fetch Stick |    91 | NULL |
+---------+-------------+-------+------+

In this example I didn’t provide any arguments other than the expression/column to use. It therefore used the default lead value of 1 and a default value of NULL.

We can see that the Lead column presents the value of the Score column in the next row. We can also see that the last row is NULL. That’s because there’s no more values (we can’t get a value from a non-existent row). It returns NULL because that’s the default value to return in such cases. This can be changed (see later example).

Change the Lead

Let’s change the lead value:

SELECT 
    DogName,
    Activity,
    Score,
    LEAD( Score, 3 ) 
        OVER ( ORDER BY Score ) 
    AS "Lead"
FROM Dogs;

Result:

+---------+-------------+-------+------+
| DogName | Activity    | Score | Lead |
+---------+-------------+-------+------+
| Bruno   | Keep Quiet  |     1 |   43 |
| Cooper  | Keep Quiet  |     8 |   51 |
| Max     | Keep Quiet  |    12 |   51 |
| Bruno   | Fetch Stick |    43 |   67 |
| Bruno   | Wag Tail    |    51 |   87 |
| Cooper  | Wag Tail    |    51 |   91 |
| Cooper  | Fetch Stick |    67 | NULL |
| Max     | Wag Tail    |    87 | NULL |
| Max     | Fetch Stick |    91 | NULL |
+---------+-------------+-------+------+

This time I specified 3 as the lead value. This resulted in the value coming from three rows forward, and the last three rows being NULL.

Specify a Default Value

We can change the default value to be something other than NULL. Here’s an example:

SELECT 
    DogName,
    Activity,
    Score,
    LEAD( Score, 3, 'N/A' ) 
        OVER ( ORDER BY Score ) 
    AS "Lead"
FROM Dogs;

Result:

+---------+-------------+-------+------+
| DogName | Activity    | Score | Lead |
+---------+-------------+-------+------+
| Bruno   | Keep Quiet  |     1 | 43   |
| Cooper  | Keep Quiet  |     8 | 51   |
| Max     | Keep Quiet  |    12 | 51   |
| Bruno   | Fetch Stick |    43 | 67   |
| Bruno   | Wag Tail    |    51 | 87   |
| Cooper  | Wag Tail    |    51 | 91   |
| Cooper  | Fetch Stick |    67 | N/A  |
| Max     | Wag Tail    |    87 | N/A  |
| Max     | Fetch Stick |    91 | N/A  |
+---------+-------------+-------+------+

This time I specified that the string N/A be returned whenever there’s no value.

Partitioning

This example uses a PARTITION BY clause to partition the results by activity:

SELECT 
    DogName,
    Activity,
    Score,
    LEAD( Score, 1, 'N/A' ) 
        OVER ( 
            PARTITION BY Activity
            ORDER BY Score 
            )
    AS "Lead"
FROM Dogs;

Result:

+---------+-------------+-------+------+
| DogName | Activity    | Score | Lead |
+---------+-------------+-------+------+
| Bruno   | Fetch Stick |    43 | 67   |
| Cooper  | Fetch Stick |    67 | 91   |
| Max     | Fetch Stick |    91 | N/A  |
| Bruno   | Keep Quiet  |     1 | 8    |
| Cooper  | Keep Quiet  |     8 | 12   |
| Max     | Keep Quiet  |    12 | N/A  |
| Bruno   | Wag Tail    |    51 | 51   |
| Cooper  | Wag Tail    |    51 | 87   |
| Max     | Wag Tail    |    87 | N/A  |
+---------+-------------+-------+------+

This has caused the lead to reset with each partition.

The `null_treatment` Clause

As mentioned, the optional null_treatment clause specifies how to treat NULL values. The default value for this clause is RESPECT NULLS. Therefore, the above example implicitly uses RESPECT NULLS.

Let’s explicitly specify RESPECT NULLS:

SELECT 
    DogName,
    Activity,
    Score,
    LEAD( Score, 1, 'N/A' ) RESPECT NULLS
        OVER ( 
            PARTITION BY Activity
            ORDER BY Score 
            )
    AS "Lead"
FROM Dogs;

Result:

+---------+-------------+-------+------+
| DogName | Activity    | Score | Lead |
+---------+-------------+-------+------+
| Bruno   | Fetch Stick |    43 | 67   |
| Cooper  | Fetch Stick |    67 | 91   |
| Max     | Fetch Stick |    91 | N/A  |
| Bruno   | Keep Quiet  |     1 | 8    |
| Cooper  | Keep Quiet  |     8 | 12   |
| Max     | Keep Quiet  |    12 | N/A  |
| Bruno   | Wag Tail    |    51 | 51   |
| Cooper  | Wag Tail    |    51 | 87   |
| Max     | Wag Tail    |    87 | N/A  |
+---------+-------------+-------+------+

Specifying IGNORE NULLS produces an error (at least as of MySQL 8.0.33):

SELECT 
    DogName,
    Activity,
    Score,
    LEAD( Score, 1, 'N/A' ) IGNORE NULLS
        OVER ( 
            PARTITION BY Activity
            ORDER BY Score 
            )
    AS "Lead"
FROM Dogs;

Result:

ERROR 1235 (42000): This version of MySQL doesn't yet support 'IGNORE NULLS'

Changing the Order

Here’s what happens when we change the order to descending order:

SELECT 
    DogName,
    Activity,
    Score,
    LEAD( Score, 1, 'N/A' ) 
        OVER ( 
            PARTITION BY Activity
            ORDER BY Score DESC
            )
    AS "Lead"
FROM Dogs;

Result:

+---------+-------------+-------+------+
| DogName | Activity    | Score | Lead |
+---------+-------------+-------+------+
| Max     | Fetch Stick |    91 | 67   |
| Cooper  | Fetch Stick |    67 | 43   |
| Bruno   | Fetch Stick |    43 | N/A  |
| Max     | Keep Quiet  |    12 | 8    |
| Cooper  | Keep Quiet  |     8 | 1    |
| Bruno   | Keep Quiet  |     1 | N/A  |
| Max     | Wag Tail    |    87 | 51   |
| Bruno   | Wag Tail    |    51 | 51   |
| Cooper  | Wag Tail    |    51 | N/A  |
+---------+-------------+-------+------+

Passing a Different Column to `LEAD()`

In the above examples, the column that we pass to the LEAD() function is the same one we pass to the ORDER BY clause. But that’s not a requirement. We can pass different a different column if needed.

Here’s an example that uses two different columns for LEAD() and the ORDER BY clause:

SELECT 
    DogName,
    Activity,
    Score,
    LEAD( DogName, 1, 'N/A' ) 
        OVER ( 
            PARTITION BY Activity
            ORDER BY Score
            )
    AS "Lead"
FROM Dogs;

Result:

+---------+-------------+-------+--------+
| DogName | Activity    | Score | Lead   |
+---------+-------------+-------+--------+
| Bruno   | Fetch Stick |    43 | Cooper |
| Cooper  | Fetch Stick |    67 | Max    |
| Max     | Fetch Stick |    91 | N/A    |
| Bruno   | Keep Quiet  |     1 | Cooper |
| Cooper  | Keep Quiet  |     8 | Max    |
| Max     | Keep Quiet  |    12 | N/A    |
| Bruno   | Wag Tail    |    51 | Cooper |
| Cooper  | Wag Tail    |    51 | Max    |
| Max     | Wag Tail    |    87 | N/A    |
+---------+-------------+-------+--------+

Here, we passed the DogName column to the LEAD() function, but we sorted by the Score column. This resulted in the dog names being displayed in the Lead column.

There’s also a LAG() function that returns the value from the other direction (i.e. it returns the value from the row previous to the current row by the given amount).

Syntax

Example

Change the Lead

Specify a Default Value

Partitioning

The null_treatment Clause

Changing the Order

Passing a Different Column to LEAD()

The `null_treatment` Clause

Passing a Different Column to `LEAD()`