LEAD() and LAG()
Learn how to use LEAD() and LAG() Window Functions in SQL Server to access values from following and previous rows.
What are LEAD() and LAG()?
LEAD() and LAG() are SQL Server Window Functions used to access data from another row without using a self join.
LAG() is used to access a value from a previous row.
LEAD() is used to access a value from a following row.
LAG()
The LAG() function allows us to access a value from a previous row in the result set.
It is useful when we want to compare the current row with the previous row.
SELECT
EmployeeName,
Salary,
LAG(Salary) OVER
(
ORDER BY EmployeeId
) AS PreviousSalary
FROM Employees;
LAG() Example
| EmployeeName | Salary | PreviousSalary |
|---|---|---|
| Amit | 60000 | NULL |
| Rahul | 70000 | 60000 |
| Priya | 80000 | 70000 |
| Neha | 90000 | 80000 |
For the first row there is no previous row, so LAG() returns NULL.
LEAD()
The LEAD() function allows us to access a value from a following row in the result set.
It is useful when we want to compare the current row with the next row.
SELECT
EmployeeName,
Salary,
LEAD(Salary) OVER
(
ORDER BY EmployeeId
) AS NextSalary
FROM Employees;
LEAD() Example
| EmployeeName | Salary | NextSalary |
|---|---|---|
| Amit | 60000 | 70000 |
| Rahul | 70000 | 80000 |
| Priya | 80000 | 90000 |
| Neha | 90000 | NULL |
For the last row there is no following row, so LEAD() returns NULL.
Syntax
LAG()
LAG(column_name)
OVER
(
ORDER BY column_name
)
LEAD()
LEAD(column_name)
OVER
(
ORDER BY column_name
)
Using Offset
By default, LEAD() and LAG() access one row before or after the current row. We can also specify an offset.
SELECT
EmployeeName,
Salary,
LAG(Salary, 2) OVER
(
ORDER BY EmployeeId
) AS SalaryTwoRowsBefore
FROM Employees;
Here LAG(Salary, 2) accesses the salary from two rows before the current row.
Using a Default Value
We can provide a default value when a previous or following row does not exist.
SELECT
EmployeeName,
Salary,
LAG(Salary, 1, 0) OVER
(
ORDER BY EmployeeId
) AS PreviousSalary
FROM Employees;
Here 0 is returned instead of NULL when there is no previous row.
LEAD() vs LAG()
| Feature | LAG() | LEAD() |
|---|---|---|
| Accesses | Previous Row | Following Row |
| Direction | Backward | Forward |
| Common Use | Compare with previous value | Compare with next value |
| Example | Previous Salary | Next Salary |
Practical Example
We can calculate the difference between an employee's current salary and the previous employee's salary.
SELECT
EmployeeName,
Salary,
LAG(Salary) OVER
(
ORDER BY EmployeeId
) AS PreviousSalary,
Salary -
LAG(Salary) OVER
(
ORDER BY EmployeeId
) AS SalaryDifference
FROM Employees;
LEAD() and LAG() with PARTITION BY
LEAD() and LAG() can be combined with PARTITION BY to perform the calculation separately for each group.
SELECT
EmployeeName,
Department,
Salary,
LAG(Salary) OVER
(
PARTITION BY Department
ORDER BY EmployeeId
) AS PreviousSalary
FROM Employees;
Here the previous salary is calculated separately for each department.
Practical Uses
- Comparing current and previous values.
- Comparing current and next values.
- Calculating changes between records.
- Finding salary or price changes.
- Analyzing sales trends.
- Comparing dates and time-based records.
Important Points
- LAG() accesses a previous row.
- LEAD() accesses a following row.
- Both functions use the OVER() clause.
- ORDER BY determines the row sequence.
- An offset can be specified.
- PARTITION BY can be used to perform calculations separately for each group.
Summary
LAG() is used to access values from a previous row, while LEAD() is used to access values from a following row.
Both functions are useful for comparing rows, calculating changes and analyzing trends. They can also be combined with PARTITION BY for group-wise analysis.