Updated February 27, 2023
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:
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.
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.
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.
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 –
