Introduction
In this post, we will continue the series of unused Oracle Database features.
The Oracle LEAD analytic function allows you to access data from the next row in a result set without using a self-join.
It is extremely useful for:
• comparing current and next rows,
• trend analysis,
• finding changes between records,
• calculating differences,
• and simplifying SQL queries.
In this article, we will explore practical examples of the Oracle LEAD function.
What is Oracle LEAD?
The LEAD function returns data from a subsequent row based on a specified offset.
Basic Syntax
LEAD(column_name, offset, default_value)OVER ( PARTITION BY column_name ORDER BY column_name)
Parameters:
- column_name → value to retrieve
- offset → number of rows forward (default is 1)
- default_value → returned if no next row exists
Examples
Create Sample Table
Let us create a simple employees table. I name it with suffix 2 to not confuse it with same table example used in other articles from Parvu.org:
CREATE TABLE employees2 ( employee_id NUMBER, employee_name VARCHAR2(100), department_id NUMBER, salary NUMBER);
Insert sample data
INSERT INTO employees2 VALUES (1, 'John', 10, 5000);INSERT INTO employees2 VALUES (2, 'Emma', 10, 6500);INSERT INTO employees2 VALUES (3, 'Liam', 10, 7200);INSERT INTO employees2 VALUES (4, 'Olivia', 20, 4500);INSERT INTO employees2 VALUES (5, 'Noah', 20, 5200);COMMIT;
The table data is :
EMPLOYEE_ID EMPLOYEE_NAME DEPARTMENT_ID SALARY----------- ---------------------------------------------------------------------------------------------------- ------------- ---------- 1 John 10 5000 2 Emma 10 6500 3 Liam 10 7200 4 Olivia 20 4500 5 Noah 20 5200
Basic LEAD Example
The following query retrieves the next employee salary based on employee ID:
SELECT employee_id, employee_name, salary, LEAD(salary) OVER (ORDER BY employee_id) AS next_salaryFROM employees2;
Result
EMPLOYEE_ID EMPLOYEE_NAME SALARY NEXT_SALARY----------- ---------------------------------------------------------------------------------------------------- ---------- ----------- 1 John 5000 6500 2 Emma 6500 7200 3 Liam 7200 4500 4 Olivia 4500 5200 5 Noah 5200
| EMPLOYEE_ID | EMPLOYEE_NAME | SALARY | NEXT_SALARY |
| 1 | John | 5000 | 6500 |
| 2 | Emma | 6500 | 7200 |
| 3 | Liam | 7200 | 4500 |
| 4 | Olivia | 4500 | 5200 |
| 5 | Noah | 5200 | NULL |
The LEAD function reads the value from the next row.
LEAD with PARTITION BY
You can restart calculations within groups!
The following example retrieves the next salary within each department.
SELECT employee_name, department_id, salary, LEAD(salary) OVER ( PARTITION BY department_id ORDER BY salary ) AS next_salaryFROM employees2;
Output:
EMPLOYEE_NAME DEPARTMENT_ID SALARY NEXT_SALARY---------------------------------------------------------------------------------------------------- ------------- ---------- -----------John 10 5000 6500Emma 10 6500 7200Liam 10 7200 Olivia 20 4500 5200Noah 20 5200
Explanation
- PARTITION BY department_id
creates separate groups. - ORDER BY salary
defines row sequence inside each department.
LEAD with Offset
The second parameter defines how many rows forward to look:
SELECT employee_name, salary, LEAD(salary, 2) OVER (ORDER BY employee_id) AS salary_after_nextFROM employees2;
This retrieves the salary two rows ahead:
EMPLOYEE_NAME SALARY SALARY_AFTER_NEXT---------------------------------------------------------------------------------------------------- ---------- -----------------John 5000 7200Emma 6500 4500Liam 7200 5200Olivia 4500 Noah 5200
LEAD with Default Value
Instead of returning NULL, you can define a custom value:
SELECT employee_name, salary, LEAD(salary, 1, 0) OVER (ORDER BY employee_id) AS next_salaryFROM employees2;
If no next row exists, Oracle returns 0.
Output
EMPLOYEE_NAME SALARY NEXT_SALARY---------------------------------------------------------------------------------------------------- ---------- -----------John 5000 6500Emma 6500 7200Liam 7200 4500Olivia 4500 5200Noah 5200 0
Calculate Salary Difference Using LEAD
One of the most practical uses of LEAD is calculating differences between rows.
SELECT employee_name, salary, LEAD(salary) OVER (ORDER BY employee_id) AS next_salary, LEAD(salary) OVER (ORDER BY employee_id) - salary AS salary_differenceFROM employees2;
Output
EMPLOYEE_NAME SALARY NEXT_SALARY SALARY_DIFFERENCE---------------------------------------------------------------------------------------------------- ---------- ----------- -----------------John 5000 6500 1500Emma 6500 7200 700Liam 7200 4500 -2700Olivia 4500 5200 700Noah 5200
Use case
This application This Is useful for:
- time-series comparisons.
- financial analysis,
- trend reporting,
- inventory tracking,
Common Use Cases
Oracle LEAD is commonly used for:
• comparing consecutive rows,
• identifying value changes,
• trend analysis,
• reporting,
• and time-based calculations.
Conclusion
The Oracle LEAD analytic function is a powerful feature that allows you to access future rows without complex joins.
Key advantages:
• simpler SQL,
• better readability,
• powerful analytical capabilities,
• and efficient reporting queries.
When working with sequential or grouped data, LEAD can significantly simplify your SQL statements.