MySQL LEAD Function

Summary: in this tutorial, you will learn how to use the MySQL LEAD() function to access data of a subsequent row from the current row in the same result set.

Overview of MySQL LEAD() function

The LEAD() the function is a window function that allows you to look forward a number of rows and access data of that row from the current row.

Similar to the LAG() function, the LEAD() the function is very useful for calculating the difference between the current row and the subsequent row within the same result set.

The following shows the syntax of the LEAD() function:

LEAD(<expression>[,offset[, default_value]]) OVER (
    PARTITION BY (expr)
    ORDER BY (expr)
)

expression

The LEAD() the function returns the value of expression from the offset-th a row of the ordered partition.

offset

The offset is the number of rows forward from the current row from which to obtain the value.

The offset must be a non-negative integer. If offset is zero, then the LEAD() function evaluates the expression for the current row.

In case you omit offset, then the LEAD() the function uses one by default.

default_value

If there is no subsequent row, the LEAD() function returns the default_value. For example, if offset is one, then the return value of the last row is the default_value.

In case you do not specify the default_value, the function returns NULL .

PARTITION BY clause

The PARTITION BY clause divides the rows in the result set into partitions to which the LEAD() the function is applied.

If the PARTITION BY a clause is not specified, all rows in the result set are treated as a single partition.

ORDER BY clause

The ORDER BY clause determines the order of rows in partitions before the LEAD() the function is applied.

MySQL LEAD() function example

We will use the orders and customers tables from the sample database for the demonstration:

The following statement finds the order date and the next order date of each customer:

SELECT 
    customerName,
    orderDate,
    LEAD(orderDate,1) OVER (
        PARTITION BY customerNumber
        ORDER BY orderDate ) nextOrderDate
FROM 
    orders
INNER JOIN customers USING (customerNumber);

Here is the output:

In this example, we first divided the result set by the customer number into partitions. Then, we sorted each partition by the order date. Finally, the LEAD() the function is applied to each partition to get the next order date.

Once the subsequent row is crossing the partition boundary, the value of the nextOrderDate in the last row of each partition is NULL.

In this tutorial, you have learned how to use the MySQL LEAD() function to access the data of the subsequent row from the current row.