Module 4's self-join found each employee's manager by joining employees back to itself. LAG() and LEAD() solve a related but different problem, comparing a row to the one next to it in a chosen order, like each new hire against the one hired right before them, without writing a join at all.
Learning Objectives
- Define LAG() and LEAD() precisely, and when each is the right choice over the other.
- Use the optional offset and default arguments correctly.
- Explain what a partition's first or last row actually gets back from LAG()/LEAD().
Ordered Partition
hire_date, within each department
LAG() looks back / LEAD() looks ahead
No self-join needed
Neighboring Row's Value
Pulled onto the current row
Result
NULL (or a default) at the edges
What LAG() and LEAD() Actually Do
LAG() returns a value from a preceding row, within the same partition and ORDER BY, LEAD() returns a value from a following row. Both read a neighboring row's value directly onto the current row, the exact result a self-join would otherwise need, built into a single window function call.
Previous and Next Hire, Per Department
SELECT first_name, department_id, hire_date,
LAG(first_name) OVER (PARTITION BY department_id ORDER BY hire_date) AS previous_hire,
LEAD(first_name) OVER (PARTITION BY department_id ORDER BY hire_date) AS next_hire
FROM employees;SQL Server needs 2012 or later specifically for LAG()/LEAD(), its window function support arrived in stages, 2005 added the basic OVER() clause, ranking and offset functions like these came later.
The Optional Offset and Default Arguments
The second argument is the offset, how many rows back (or, for LEAD(), forward) to look, defaulting to 1 if left out. The third argument is the default value to return when there's no such row, the start or end of the partition, defaulting to NULL if left out. Here, an employee with fewer than 2 people hired before them in their department gets 0 instead of NULL.
LAG(salary, 2, 0) OVER (PARTITION BY department_id ORDER BY hire_date) AS salary_two_hires_ago
Try It Yourself
Write a query showing each employee's salary alongside the salary of the next employee hired after them (company-wide, no partition), on PostgreSQL.
SELECT first_name, hire_date, salary,
LEAD(salary) OVER (ORDER BY hire_date) AS next_hire_salary
FROM employees;Frequently Asked Questions
What does LAG() return for the very first row in a partition?
NULL, by default, there's no preceding row to look at. Supplying a third argument, like LAG(salary, 1, 0), returns that default value instead of NULL for exactly this case.
Could the same result be achieved with a self-join instead?
Usually yes, but it's meaningfully more verbose, a self-join needs an explicit join condition matching each row to its neighbor by position, which is awkward to express correctly, and typically performs worse than a window function the database can evaluate in a single pass over already-ordered data.
Common Beginner Mistakes
Expecting LAG()/LEAD() to cross partition boundaries
They never do, LAG() on the first row of a department always returns NULL (or the supplied default), even if there are plenty of rows in the previous department. PARTITION BY strictly isolates each group from every other.
Forgetting that ORDER BY inside OVER() defines what "previous" and "next" actually mean
LAG()/LEAD() have no inherent sense of order beyond the ORDER BY given inside their own OVER() clause, changing that ORDER BY changes which row counts as "previous", it isn't tied to the table's physical row order at all.
Best Practices
- Supply an explicit default (the third argument) whenever NULL at a partition's edge would be confusing or need extra handling downstream.
- Prefer LAG()/LEAD() over a self-join for "compare to the neighboring row" problems, it's simpler to write and usually faster.
- Double-check SQL Server's version (2012+) before relying on LAG()/LEAD() there specifically.
Interview Questions
Explain what LAG() and LEAD() do, and why they're usually preferred over a self-join for the same problem.
LAG() reads a value from a preceding row (within a given partition and order), LEAD() reads one from a following row, both directly, without a join. A self-join can achieve the same result, matching each row to its neighbor by position, but the join condition is awkward to write correctly and the database generally has to work harder to evaluate it than a window function operating on already-ordered data in a single pass.
What happens when LAG() is called on the first row of a partition, and how can that be controlled?
By default, it returns NULL, there's no preceding row. Supplying LAG()'s optional third argument, a default value, like LAG(salary, 1, 0), returns that value instead of NULL whenever there's no row at the requested offset.
Summary
LAG() and LEAD() read a preceding or following row's value directly, within a partition and order, without a self-join. Both take an optional offset (how many rows away, default 1) and an optional default value (what to return at the partition's edge, default NULL). SQL Server needs 2012 or later specifically for these two.
What's Next?
SQL FIRST_VALUE() and LAST_VALUE() are next, including the single most common mistake with either of them: LAST_VALUE()'s default frame doesn't do what it looks like it should.
Ready to Practice SQL?
Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.
Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.
Or try 50 SQL practice questions →