Master SQL Server From Scratch

Clear, interactive, and structured SQL Server lessons designed for beginners and developers.

COURSE SYLLABUS
1. Introduction to SQL Server
Introduction to SQL Server SQL Server Installation SQL Server Fundamentals
2. Data Types
Data Types Numeric Data Types Character & String Data Types Date & Time Data Types Other SQL Server Data Types
3. Database & Tables
Database & Tables Introduction Creating a Database Creating Tables Table Structure Altering Tables Dropping Tables
4. Constraints
Constraints Introduction PRIMARY KEY FOREIGN KEY UNIQUE Constraint NOT NULL & DEFAULT CHECK Constraint
5. SQL Basics
SQL Basics Introduction SQL Commands SQL Command Categories SELECT Statement INSERT, UPDATE & DELETE
6. CRUD Operations
CRUD Operations Introduction INSERT Operation SELECT Operation UPDATE Operation DELETE Operation
7. SQL Queries
Queries Introduction WHERE Clause ORDER BY Clause DISTINCT LIKE Operator IN, BETWEEN & IS NULL
8. Joins
Joins Introduction INNER JOIN LEFT JOIN RIGHT JOIN FULL OUTER JOIN CROSS JOIN
9. SQL Functions
SQL Functions Introduction String Functions Numeric Functions Date & Time Functions Conversion Functions NULL Functions
10. Grouping & Aggregate Functions
GROUP BY Aggregate Functions COUNT() SUM() AVG() MIN() & MAX()
11. Subqueries
Subqueries Introduction Single-Row Subquery Multiple-Row Subquery Correlated Subquery Subquery with DML
12. Views
SQL Views Introduction Creating a View Updating a View Altering a View Dropping a View
13. Stored Procedures
Stored Procedures Introduction Creating a Stored Procedure Input Parameters Output Parameters Executing & Modifying Stored Procedures
14. Triggers
Triggers Introduction AFTER INSERT Trigger AFTER UPDATE Trigger AFTER DELETE Trigger INSTEAD OF Trigger
15. Transactions
Transactions Introduction COMMIT ROLLBACK SAVEPOINT Transaction with TRY...CATCH
16. Indexes
Indexes Introduction Clustered Index Non-Clustered Index Creating & Dropping Indexes Index Performance
17. Common Table Expressions
CTE Introduction Simple CTE CTE with JOIN CTE with Aggregate Functions Recursive CTE
18. Window Functions
Window Functions Introduction ROW_NUMBER() RANK() and DENSE_RANK() LEAD() and LAG() PARTITION BY
19. Advanced SQL
Advanced SQL Introduction Temporary Tables Table Variables Dynamic SQL Error Handling Performance Optimization

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.