LEAD
Returns the value from the row that is offset rows after the current one in the window partition.
MySQL 8.1
LEAD(column[, offset[, default]])- [ ]optional part
- column
- Column or expression whose value is taken from the next row
- offset
- How many rows ahead to look, a non-negative integer, 1 by defaultINTOptional
- default
- Value used if there is no row at that offset, NULL by defaultOptional
Return value
A value of the same type as column; default or NULL if there is no row at that offset, for example for the last row of a partition
Examples
Shift one and two rows forward
MySQL 8.1
SELECT member_name,
birthday,
LEAD(member_name) OVER (
ORDER BY birthday
) AS next_member,
LEAD(member_name, 2, 'none') OVER (
ORDER BY birthday
) AS two_rows_ahead
FROM FamilyMembers;Free days before the next reservation
MySQL 8.1
SELECT room_id,
start_date,
end_date,
DATEDIFF(
LEAD(start_date) OVER (
PARTITION BY room_id
ORDER BY start_date
),
end_date
) AS free_days_after
FROM Reservations;Details
LEAD is handy for calculating the gap before the next event: for example, how many days a room stays free until the next reservation. default is used only when there is no row at that offset; if the row exists but holds NULL, the result is NULL.