LAG() and LEAD() functions in SQL

In SQL, LAG() and LEAD() are window functions used to access data from a preceding or succeeding row within a result set.

1. LAG() function in SQL

The LAG() function allows us to access data from a previous row in the result set.

SQL Syntax

• column_name: The column from which we want to access data.
• offset: The number of rows behind the current row. If not provided, it defaults to 1.
• default_value: Optional. Value returned when there is no previous row (e.g., in the first row).

Example: Create the Employees table.

SQL

CREATE TABLE Employees (
   EmployeeID INT,
    Name VARCHAR(20),
    Age int,
    Salary VARCHAR(10),
    Department VARCHAR(25),
    Gender VARCHAR(1)
);      

SQL

CREATE TABLE Employees (
   EmployeeID INT,
    Name VARCHAR(20),
    Age int,
    Salary VARCHAR(10),
    Department VARCHAR(25),
    Gender VARCHAR(1)
);      

Insert the data in the Employees table.

SQL

insert into Employees values
(1, "Ashish", 24, "25000", "Electrical", "M"), 
(2, "Kirshan", 32,"30000", "Electrical", "M"),
(3, "Anjali", 20,"25000", "Electronics", "F"),
(4, "Manjulika", 30, "15000", "Electrical", "F"),
(5, "Katrina", 37,"18000", "Computer", "F"),
(6, "Esha", 37,"20000", "Computer", "F"),
(7, "Ankita", 20,"9000", "Electronics", "M"), 
(8, "Meenakshi", 30, "15000", "Computer", "F"),
(10, "Alia", 37,"16000", "Electronics", "F")
;          

By using the Create table and insert statement we reach to the following table in our database:

Table: Employees

SQL

SELECT 
    EmployeeID, 
    salary, 
    LAG(salary, 1, 0) OVER (ORDER BY salary) AS previous_salary
FROM employees;  

This will return the salary from the previous row, ordered by salary.

• In the first row with EmployeeID 4, there is no previous salary, so it shows 0.
• For EmployeeID 8, the previous salary is EmployeeID 4's salary i.e. 15000.

2. LEAD() function in SQL

The LEAD() function allows you to access data from a subsequent row in the result set.

SQL Syntax

• column_name: The column from which you want to access data.
• offset: The number of rows ahead of the current row. If not provided, it defaults to 1.
• default_value: Optional. Value returned when there is no next row.

Example:

SQL

SELECT 
  EmployeeID, 
  salary, 
  LAG(salary, 1, 0) OVER (ORDER BY salary) AS previous_salary
FROM employees;  

This will return the salary from the next row, ordered by salary.