LEAD function for next row access in SQL

Introduction

The LEAD function helps you look at the next row's data in a list without moving your place. It makes comparing current and next items easy.

When you want to compare a current sales amount with the next day's sales.

When you need to find the next event date after the current one in a schedule.

When you want to see the next price of a product in a sorted list.

When you want to calculate the difference between current and next row values.

Syntax

SQL

LEAD(column_name, offset, default_value) OVER (ORDER BY column_to_order)

The offset is how many rows ahead you want to look; if omitted, it defaults to 1 (the very next row).

The default_value is what you get if there is no next row; if omitted, it returns NULL.

Examples

Shows the salary of the next employee based on employee_id order.

SQL

SELECT LEAD(salary) OVER (ORDER BY employee_id) AS next_salary FROM employees;

Looks two rows ahead in sales_date order; if no row, returns 0.

SQL

SELECT LEAD(sales, 2, 0) OVER (ORDER BY sales_date) AS sales_after_two_days FROM sales_data;

Shows the next higher price in the list of products.

SQL

SELECT product, price, LEAD(price) OVER (ORDER BY price) AS next_price FROM products;

Sample Program

This query creates a table of sales by day, then shows each day's sales and the next day's sales side by side.

SQL

CREATE TABLE daily_sales (day INT, sales INT);
INSERT INTO daily_sales VALUES (1, 100), (2, 150), (3, 120), (4, 130);

SELECT day, sales, LEAD(sales) OVER (ORDER BY day) AS next_day_sales FROM daily_sales ORDER BY day;

Important Notes

LEAD is a window function and requires an OVER clause with ORDER BY to define the row order.

If you do not specify ORDER BY, the result may be unpredictable because SQL tables have no default order.

LEAD can be combined with other functions to calculate differences or trends between rows.

Summary

LEAD lets you peek at the next row's value without moving your current row position.

It needs an ORDER BY to know which row is next.

You can set how far ahead to look and what to return if no next row exists.