LAG / LEAD

Functions
Buy Me A Coffee

Access the value from a row before (LAG) or after (LEAD) the current row within an ordered window partition — without a self-join.

Accesses data from a preceding (LAG) or following (LEAD) row within a partition without a self-join. Essential for period-over-period comparisons, change detection, and computing differences between consecutive rows.

Compatibility

Filter by Database
SQL LAG / LEAD Compatibility Across Databases
Database System Support Status Since Version Notes
MySQL Native 8.0 LAG(expr [, offset [, default]]) and LEAD(expr [, offset [, default]]). Offset defaults to 1. Default value returned when offset goes out of bounds (instead of NULL).
PostgreSQL Native 8.4 LAG(expr [, offset [, default]]) and LEAD(expr [, offset [, default]]). Supports any offset and a default value for when the offset goes beyond partition bounds.
SQL Server Native 2012 LAG(expr [, offset [, default]]) and LEAD(expr [, offset [, default]]). Added in SQL Server 2012. Offset must be a non-negative integer constant.
Oracle Native 8i LAG(expr [, offset [, default]]) and LEAD(expr [, offset [, default]]). One of the earliest implementations — Oracle has had these since 8i.
SQLite Native 3.25.0 LAG() and LEAD() fully supported since SQLite 3.25.0 (September 2018).

Details

LAG/LEAD replaced the classic self-join pattern (join a table to itself on id-1). The optional default argument (third parameter) controls what's returned at partition boundaries — without it you get NULL, which can break calculations. SQL Server didn't add these until 2012; Oracle had them since 8i.

Standard Syntax

SELECT order_date, amount, LAG(amount) OVER (ORDER BY order_date) AS prev_amount, LEAD(amount) OVER (ORDER BY order_date) AS next_amount FROM orders;

Version Support

MySQL: Native since 8.0 PostgreSQL: Native since 8.4 SQL Server: Native since 2012 Oracle: Native since 8i SQLite: Native since 3.25.0

Per-Database Syntax & Notes

MySQL Native syntax

LAG(expr [, offset [, default]]) and LEAD(expr [, offset [, default]]). Offset defaults to 1. Default value returned when offset goes out of bounds (instead of NULL).

SELECT order_date, amount, LAG(amount, 1, 0) OVER (ORDER BY order_date) AS prev_amount FROM orders;

PostgreSQL Native syntax

LAG(expr [, offset [, default]]) and LEAD(expr [, offset [, default]]). Supports any offset and a default value for when the offset goes beyond partition bounds.

SELECT order_date, amount, LAG(amount) OVER (ORDER BY order_date) AS prev_amount, LAG(amount, 2) OVER (ORDER BY order_date) AS two_ago, LEAD(amount, 1, 0) OVER (PARTITION BY cust_id ORDER BY order_date) AS next_amount FROM orders;

SQL Server Native syntax

LAG(expr [, offset [, default]]) and LEAD(expr [, offset [, default]]). Added in SQL Server 2012. Offset must be a non-negative integer constant.

SELECT order_date, amount, LAG(amount, 1, 0) OVER (ORDER BY order_date) AS prev_amount, LEAD(amount, 1, 0) OVER (ORDER BY order_date) AS next_amount FROM orders;

Oracle Native syntax

LAG(expr [, offset [, default]]) and LEAD(expr [, offset [, default]]). One of the earliest implementations — Oracle has had these since 8i.

SELECT order_date, amount, LAG(amount, 1, 0) OVER (ORDER BY order_date) AS prev_amount, LEAD(amount, 1, 0) OVER (ORDER BY order_date) AS next_amount FROM orders;

SQLite Native syntax

LAG() and LEAD() fully supported since SQLite 3.25.0 (September 2018).

SELECT order_date, amount, LAG(amount) OVER (ORDER BY order_date) AS prev_amount, LEAD(amount) OVER (ORDER BY order_date) AS next_amount FROM orders;