EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials Oracle Tutorial Oracle LEAD()
Secondary Sidebar
Oracle Tutorial
  • Advanced
    • Timestamp to Date in Oracle
    • Oracle Golden Gate
    • Oracle Virtual Machine
    • Oracle Describe Table
    • Oracle Clauses
    • Oracle Having Clause
    • Oracle?Primavera
    • Oracle FOREIGN Key
    • PIVOT in Oracle
    • Oracle Alter Table
    • Oracle Queries
    • Oracle Views
    • Oracle Window Functions
    • Oracle String Functions
    • Oracle Date Functions
    • Oracle Analytic Functions
    • Oracle Aggregate Functions
    • Select in Oracle
    • INSERT in Oracle
    • DISTINCT in Oracle
    • Function in Oracle
    • Oracle GROUP_CONCAT
    • Oracle INSTR()
    • Oracle CONVERT
    • Oracle LENGTH()
    • Oracle EXISTS
    • Oracle REPLACE()
    • Oracle MERGE
    • Oracle LEAD()
    • Oracle EXTRACT()
    • Oracle LISTAGG()
    • Oracle SYS_CONTEXT()
    • Oracle COALESCE
    • Oracle NVL()
    • Oracle SYSDATE()
    • Oracle?Date Format
    • Oracle SYS_GUID()
    • Oracle WILDCARDS
    • Oracle Synonyms
    • Oracle Subquery
    • BETWEEN in Oracle
    • FETCH in Oracle
    • Oracle Index
    • Oracle Function-based Index
    • Oracle UNIQUE Index
    • Oracle Bitmap Index
    • Oracle Column
    • Oracle Triggers
    • Oracle Procedures
    • Sample Database for Oracle
    • Oracle LIKE Operator
    • ORDER BY in Oracle
    • Oracle ORDER BY DESC
    • GROUP BY in Oracle
    • Oracle GROUP BY HAVING
    • Oracle Aliases
    • Table in Oracle
    • Oracle Temporary Table
    • Oracle? Table Partition
    • Oracle rename table
    • Oracle CTE
    • Cursor in Oracle
    • Oracle LOCK TABLE
    • Oracle Tablespace
    • Oracle CARDINALITY
    • Oracle REGEXP
    • Oracle REGEXP_REPLACE
    • Oracle to_date
    • JSON in Oracle
    • Oracle COMMIT
    • Oracle GRANT
    • Oracle MD5
    • Oracle ROLLBACK
    • Oracle Users
    • Oracle TIMESTAMP
    • IF THEN ELSE in Oracle
    • Oracle While Loop
    • Oracle Clone Database
    • Oracle Backup Database
    • Oracle? XML
    • Oracle XMLAGG
    • Oracle XMLTABLE
    • Oracle Performance Tuning
    • Oracle B Tree Index
    • Oracle fusion
    • Oracle ebs
    • Oracle GRC
    • Oracle ERP
    • Oracle ASM
    • Oracle Cloud
    • Oracle HCM Cloud
    • Oracle Integration Cloud
    • Oracle Jinitiator
    • Oracle pathfinder
    • Oracle VirtualBox
    • Oracle Weblogic Server
    • Oracle decode
    • Oracle Exadata
    • Oracle ZFS
    • Oracle? utilities
    • JDBC Driver for Oracle
    • Oracle? DBA Versions
    • Oracle DBA Salary
  • Basic
    • Oracle Marketing Cloud
    • What is Oracle?
    • Career in Oracle
    • How to Install Oracle
    • Oracle Versions
    • What Is Oracle Database
    • Oracle Data Warehousing
    • Oracle Warehouse Builder
    • Career In Oracle Database Administrator
    • Career In Oracle DBA
    • What is Oracle RAC
    • Oracle DBA
    • Oracle? Vanderbilt
    • What is RMAN Oracle
    • Oracle Database Administration
    • Oracle Operators
    • Oracle Constraints
    • Oracle number
    • Oracle Data Types
    • Oracle UNIQUE Constraint
    • Oracle Check Constraint
  • Joins
    • Joins in Oracle
    • Inner Join in Oracle
    • Oracle Cross Join
    • Left Join in Oracle
    • OUTER Join in Oracle
    • Oracle Full Outer Join
    • Natural Join in Oracle
    • Oracle Self Join
    • Oracle hash join
    • Oracle? Update with Join
  • Oracle SET Operators
    • UNION in Oracle
    • Oracle UNION ALL
    • INTERSECT in Oracle
    • MINUS in Oracle
  • Interview Questions
    • Oracle Interview Questions
    • Oracle Apps Interview Questions
    • Oracle Apps Technical Interview Questions
    • Oracle Database Interview Questions
    • Oracle Forms Interview Questions
    • Oracle PL/SQL Interview Questions
    • Oracle RAC Interview Questions
    • Oracle SOA Interview Questions

Oracle LEAD()

By Priya PedamkarPriya Pedamkar

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).

Syntax:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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
Popular Course in this category
Oracle Training (17 Courses, 8+ Projects)
  17 Online Courses |  8 Hands-on Projects |  140+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course

Related Courses

Oracle DBA Database Management System Training (2 Courses)4.9
Financial Analyst Masters Training Program4.8
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • 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
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

Let’s Get Started

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

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?

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

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