Oracle LEAD function

The Oracle/PLSQL LEAD function is an analytical function that allows you to query more than one row in a table, while not having a table to join. Returns values from the next row in the table. To return values from the previous row, try the LAG function.

Oracle/PLSQL syntax of the LEAD function

LEAD ( expression_id [, offset_id [, default_id] ] ).
over ([ query_partition_clause_id ] order_by_clause_id )

Parameters or arguments

• expression_id – an expression that can contain other built-in functions, but cannot contain analytical functions.
• offset_id – optional. It is a physical offset from the current row in the table. If this parameter is not specified, it is 1 by default.
• default_id – optional. This is the value that is returned if offset is outside the table. If this option is not specified, it is Null by default.
• query_partition_clause_id – optional. It is used to divide results into groups based on one or more expressions.
• order_by_clause_id – optional. It is used to organize the data in each section.

The LEAD function returns values from the next table row.

You can use the LEAD function in the following versions of Oracle/PLSQL

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

The LEAD function can be used in Oracle/PLSQL

Let’s consider an example. If we have a table that contains the following data:

And we will launch the next request:

SELECT prod_id,
order_date_id,
LEAD (order_date_id,1) over (ORDER BY order_date_id) AS next_order_date_id
FROM orders_id;

then we’ll get the next result:

Since we used offset_id 1, the query returns the next ORDER_DATE.

If we used offset_id 2 instead, it would return ORDER_DATE_id 2 orders later. If we had used offset_id 3, it would have returned ORDER_DATE_id 3 orders later … and so on.

If we want to know only the orders for this prod_id, we will execute the following SQL query:

SELECT prod_id,
order_date_id,
LEAD (order_date_id,1) over (ORDER BY order_date_id) AS next_order_date_id
FROM orders_id
WHERE prod_id = 2000;

The request will return the following result:

In this example, the following ORDER_DATE for prod_id = 2000 will be returned and all other orders will be ignored.

Using partitions

Now let’s look at a more complex example where we use the query parameter partition to return the next order_date_id for each prod_id.

Enter the following SQL statement:

SELECT prod_id,
order_date_id,
LEAD (order_date_id,1) OVER (PARTITION BY prod_id ORDER BY order_date_id) AS next_order_date_id
FROM orders_id;

In this example, the LEAD function will divide the results by prod_id and then sort by order_date_id as specified in PARTITION BY prod_id ORDER BY order_date_id. This means that LEAD will only evaluate order_date_id if prod_id matches the prod_id of the current record. When a new prod_id occurs, the LEAD function restarts its calculations and uses the corresponding section prod_id.

As you can see, the third record in the result set is NULL for next_order_date_id, because this is the last record for the section where prod_id equals 1000 (sorted by order_date_id). This is also true for the 6th record, where prod_id is equal to 2000.

LAG vs LEAD Functions in Oracle Database