MySQL | LEAD() and LAG() Function
Last Updated :
14 May, 2025
The LEAD() and LAG() functions in MySQL are powerful window functions introduced in version 8.0. They allow users to access data from preceding or following rows without the need for self-joins or subqueries, making them invaluable for analyzing sequential or time-series data.
In this article, we will learn the LEAD() and LAG() functions in MySQL, covering their syntax, parameters, and key differences. You’ll learn how to use these functions with real-world examples, explore their role in window function operations
What are LEAD() and LAG() Functions in MySQL?
The LAG() function is used to get value from the row that precedes the current row.
The LEAD() function is used to get value from a row that succeeds the current row.
Syntax:
For LEAD() Function-
LEAD(expr, N, default)
OVER (Window_specification | Window_name)
For LAG() Function-
LAG(expr, N, default)
OVER (Window_specification | Window_name)
The N and default argument in the function is optional.
Parameters used:
- expr: It can be a column or any built-in function.
- N: It is a positive value which determine number of rows preceding/succeeding the current row. If it is omitted in query then its default value is 1.
- default: It is the default value return by function in-case no row precedes/succeedes the current row by N rows. If it is missing then it is by default NULL.
- OVER(): It defines how rows are partitioned into groups. If OVER() is empty then function compute result using all rows.
- Window_specification: It consist of query partition clause which determines how the query rows are partitioned and ordered.
- Window_name: If window is specified elsewhere in the query then it is referenced using this Window_name.
Examples of LEAD() and LAG() Functions
Consider a "contest" table:-
c_id | start_date | end_date |
---|
1 | 2015-02-01 | 2015-02-04 |
2 | 2015-02-02 | 2015-02-05 |
3 | 2015-02-03 | 2015-02-07 |
4 | 2015-02-04 | 2015-02-06 |
5 | 2015-02-06 | 2015-02-09 |
6 | 2015-02-08 | 2015-02-10 |
7 | 2015-02-10 | 2015-02-11 |
In the above table, "c_id" represents contest id and "start_date" and "end_date" represents starting and ending date of contest respectively.
Example 1: Calculate Overlapping Days Between Contests
Problem description: We have to find number of days a contest will collapse with the next contest i.e no. of days on which both contests are held.
Query:
SELECT id,
start_date,
end_date,
DATEDIFF(LEAD(start_date) OVER (ORDER BY start_date), end_date) + 1 AS no_of_days
FROM events;
Explanations:
LEAD(start_date)
: This function fetches the start date of the next contest based on the ordering by start_date
.DATEDIFF(LEAD(start_date) ... , end_date)
: The difference in days between the current contest's end date and the next contest's start date.+ 1
: We add 1 to account for the fact that the last day of the previous contest also counts as an overlapping day.
Output:
c_id | start_date | end_date | no_of_days |
---|
1 | 2015-02-01 | 2015-02-04 | 3 |
2 | 2015-02-02 | 2015-02-05 | 3 |
3 | 2015-02-03 | 2015-02-07 | 4 |
4 | 2015-02-04 | 2015-02-06 | 1 |
5 | 2015-02-06 | 2015-02-09 | 2 |
6 | 2015-02-08 | 2015-02-10 | 1 |
7 | 2015-02-10 | 2015-02-11 | NULL |
Since, there is no contest after contest 7 i.e (c_id=7). So, lead(start_date) returns NULL value.
Note:The LEAD() and LAG() function are always used with OVER(). Missing of an over clause will raise an error.
Example 2: Retrieve Preceding Data for Analysis
Query:
SELECT
c_id,
start_date,
end_date,
LAG(end_date) OVER (ORDER BY start_date) AS prev_end_date,
DATEDIFF(start_date, LAG(end_date) OVER (ORDER BY start_date)) AS gap_days
FROM contest;
Output:
c_id | start_date | end_date | prev_end_date | gap_days |
---|
1 | 2015-02-01 | 2015-02-04 | NULL | NULL |
2 | 2015-02-02 | 2015-02-05 | 2015-02-04 | -2 |
3 | 2015-02-03 | 2015-02-07 | 2015-02-05 | -2 |
4 | 2015-02-04 | 2015-02-06 | 2015-02-07 | -3 |
5 | 2015-02-06 | 2015-02-09 | 2015-02-06 | 0 |
6 | 2015-02-08 | 2015-02-10 | 2015-02-09 | -1 |
7 | 2015-02-10 | 2015-02-11 | 2015-02-10 | 0 |
Conclusion
The LEAD() and LAG() functions in MySQL are essential for performing sequential data analysis, making it easier to access previous or next rows without complex joins. Whether you’re comparing trends, detecting gaps, or monitoring progressions, these functions simplify query writing and enhance performance. By understanding the syntax, use cases, and real-world applications, you can leverage LEAD() and LAG() effectively in your database operations.