MySQL LEAD and LAG Function

The LEAD and LAG is a window function in MySQL used to access the preceding and succeeding value of specified rows from the current row within its partition. These functions are the kind of non-aggregate function.

The Window functions in MySQL are used to perform operations or calculations for each row within the partition or window. These functions produce the result similar to a calculation done by using the aggregate functions. But, unlike aggregate functions that perform operations on an entire table, window functions do not produce a result to be grouped into a single row. Therefore each row maintains the unique identities. In the window function, we must know about these things:

• The row on which function evaluation occurs is termed as the current row.
• A window is the set of rows related to the current row or using which function operates on that row.

Let us learn about these functions in detail.

MySQL LEAD Function

This function allows us to look forward rows or succeeding rows to get/access the value of that row from the current row. It is a very useful method to calculate the difference between the current and subsequent rows within the same output.

Below is the general syntax to use the LEAD function in MySQL:

Parameter Explanation

The LEAD function syntax contains the following parameters.

MySQL LEAD() Function Example

Here, we are going to understand how the LEAD function works with the MySQL table. First, we need to create a table named sales_table using the below statement.

Next, we will add records into this table using the INSERT statement as follows:

We can verify the records into a table using the SELECT statement. It will give the output as below:

The following statement finds the sale and next sales detail of each employee:

This example first divided the result set by the year into partitions and then sorted each partition using the country column. Finally, we applied the LEAD() function on each partition to get the next sales detail. The below output explains it more clearly:

In the output, we can see the null value in each partition. When the succeeding row crosses the partition boundary, the next value in each partition's last row always becomes NULL.

MySQL LAG Function

This function allows us to look information about backword rows or preceding rows to get/access the value of a previous row from the current row. It is a very useful method to calculate the difference between the current and the previous row within the same result set.

Below is the general syntax to use the LAG function in MySQL:

Parameter Explanation

The LAG function syntax contains the following parameters.

The other parameters such as OVER, PARTITION BY, ORDER BY clause meaning are the same as the LEAD function.

MySQL LAG() Function Example

Here, we are going to understand how the LAG function works with the MySQL table. We can use the above table named sales_table for the demonstration.

The following statement finds the sale and previous sales detail of each employee:

This example first divided the result set by the year into partitions and then sorted each partition using the country column. Finally, we applied the LAG() function on each partition to get the previous sales detail. After execution of the above statement, we can see the below output:

In the output, we can see the 0.00 value in each partition. It indicates the value of the row that will not exist in a table. If we have not provided the default value, it will become NULL.

NOTE: The LEAD() and LAG() function are introduced in MySQL version 8.0. So we cannot use it in the previous versions. They are always used with the OVER() clause. If we have not used this clause, it will raise an error.