EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • 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
  • Login
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).

ADVERTISEMENT
Popular Course in this category
ORACLE DBA Database Management System Course Bundle - 2 Courses in 1

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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
ADVERTISEMENT
GOLANG Course Bundle - 6 Courses in 1
23+ Hours of HD Videos
6 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
iOS DEVELOPER Course Bundle - 61 Courses in 1
147+ Hours of HD Videos
61 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
JAVA SERVLET Course Bundle - 18 Courses in 1 | 6 Mock Tests
56+ Hours of HD Videos
18 Courses
6 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
RED HAT LINUX Course Bundle - 5 Courses in 1
28+ Hours of HD Videos
5 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - 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

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

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

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

Forgot Password?

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Extended Cyber Monday Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW