EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials Oracle Tutorial Oracle LEAD()
 

Oracle LEAD()

Priya Pedamkar
Article byPriya Pedamkar

Updated February 27, 2023

oracle lead

 

 

Definition of Oracle LEAD()

Oracle lead can be defined as an analytic function which can be used to simply query more than one row of a table with the help of a query without the requirement of joining the table to itself (self join) by giving us the data of the row which is beyond the current cursor (position in the table) using the offset provided in the query (default offset is one in case of lead function meaning it will provide the data of the next row).

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax:

Let us now look into the syntax of the oracle lead function after we have already discussed the definition of Oracle lead function.

LEAD(expression [, offset number ] [, default value])
OVER (
[partition_clause] order_by_clause
)

Let us now look at the various parameters present in the syntax

Parameters:

  • expression: It refers to the expression which can be any other build in function but not analytical function. It is scalar and it is evaluated against the value of the offset.
  • offset number: It refers to the physical offset value. It means that the number of rows forward from the current row. The default value is 1.
  • default value: It refers to the default value and it kicks in when we provide the offset value parameter beyond the scope of the partition. In case we skip the default the function will return null.
  • partion_clause: It is optional and it is used to partition the results into groups.
  • order_by_clause: It is also an optional clause and it is used to sort the data in each partition.

How does Oracle Lead Function Work?

We will now discuss the way the oracle lead function actually works. As we can see in the definition that the lead function actually has five parameters. Suppose we have a table with product_date and product_id. A simple select statement would give us the details of the whole table with product date and product id. If we use the lead function over the product date then the query would first go to the first row of the table and that would be the position of a cursor and then depending on the offset value the lead function will provide access to a row which is offset position ahead of the current cursor position. For example, if the offset value is one then it will give us the next row. This process goes on with each increment of the original cursor position the offset cursor also increments by the value of the offset each time until we reach the end of the result set in case we have used a condition or the end of the table in case it is a simple select statement. The result set is prepared with the current product date as well as the next product data and then based on the order by clause the entire retrieved result set is sorted and then the updated result set is returned as output.

One point to note is that the lead function cannot be nested with other analytical functions in the partition clause or the expression but we can use other built-in functions or expressions.

Examples of Oracle Lead Function

Following are the examples are given below:

Example #1 – Lead Function without Using the Offset

In the first scenario, we are going to use the Lead Function without using the offset which means that the offset value since it is not mentioned explicitly will be used as ONE. In this example, we will use a SELECT statement to retrieve the employee_id, name, and the next employee id from the employee table. The lead function is going to help us in retrieving the next employee id. Let us look at the query for the same.

Query:

SELECT employee_id, name,
LEAD (employee_id)
OVER (ORDER BY employee_id)
AS next_employee_id
FROM employee;

As we can see in the query we have not used the offset. So the value of the offset is ONE. Let us run the query in SQL developer and look at the result.

Oracle LEAD()-1.1

As, we can see in the output that the query shows both current and next employee id from the employee table.

Example #2 – Lead Function with User Specified Offset Value

Unlike the other scenario, in this case, we are going to specify the offset value explicitly in the function. In this example, we will find the employee id name and the next employee id from the employee table with an offset of TWO. Let us look at the query below.

Query:

SELECT employee_id, name,
LEAD (employee_id,2)
OVER (ORDER BY employee_id)
AS next_employee_id
FROM employee;

As we can see in the query the offset value is 2. Let us execute and look at the result of this query.

Oracle LEAD()-1.2

As we can see in the output the last two values of the NEXT_EMPLOYEE_ID column is null because the offset went beyond the scope and since there was no default value mentioned in the function, so it returned NULL.

Example #3 – Lead Function with User Specified Offset Value and Default Value

Unlike the second scenario in this scenario we are going to return a string value instead of a NULL value. In this example we will find the employee id name and the next employee id from the employee table with an offset of TWO. Let us look at the query below.

Query:

SELECT employee_id, name,
LEAD (employee_id,2,'Not Available')
OVER (ORDER BY employee_id)
AS next_employee_id
FROM employee;

As we can see in the query we have a use default value as’ Not Available’. Let us run the query in SQL developer and look at the result.

Oracle LEAD()-1.3

As we can see in the screenshot that the last two values in column NEXT_EMPLOYEE_ID are ‘Not Available’ instead of Null.

Recommended Articles

This is a guide to Oracle LEAD(). Here we also discuss the definition and how does oracle lead function work? along with different examples and its code implementation. You may also have a look at the following articles to learn more –

  1. Oracle Self Join
  2. Oracle While Loop
  3. Oracle Window Functions
  4. Oracle MD5

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW