LEAD Function in Oracle SQL – PLSQL

The LEAD function in Oracle SQL / PLSQL is an analytical function and helps us to query more than one row at a time without having self join on the table.
The LEAD function returns the value from the rows ahead in the table.

Syntax for the LEAD Function in Oracle SQL / PLSQL is:
SELECT column(s)
,LEAD (expression [,offset,default]) OVER ([PARTITION BY column(s) ORDER BY column(s)]
FROM table_name(s);

Note:

• ‘expression’ is an expression that can contain other built in Oracle SQL functions, but cannot contain any Oracle SQL analytica function.
• ‘offset’ is an optional value. It is a physical offset from the current records in a table, if omitted the default is 1.
• ‘default’ is also optional. It is the value that would be returned if the offset goes out of bounds of the table, if omitted default will be NULL.

Example 1:

Using LEAD function to without PARTITION BY
Suppose we have a table named ‘orders as shown below:

If we write our query for LEAD without PARTITION BY as:

We will get the following output:

Let’s analyze the fetched records for understanding the LEAD Analytical function without PARTITION BY:

• 1^{st, 2nd,} 3^rd, 4^th and 5^th record tells us the product_id, order_date and also the next_order_date when a product was ordered, it doesn’t matter what product was order, what matters here is that on order was placed on a date.
• The 6^th records tell us that after 1/30/2012 no product was ordered and hence NULL was returned as next_order_date.

Example 2:

Using LEAD function to with PARTITION BY

Suppose we have a table named ‘orders as shown below:

If we write our query for LEAD with PARTITION BY as:

We will get the following output:

Let’s analyze the fetched records for understanding the LEAD Analytical function with PARTITION BY:

• 1^st, 2^nd and 3^rd record tells us for the product with product_id = 1001 an order was placed on 01/25/2012 and next order was placed on 01/26/2012 and then the next order was placed on 01/29/2012, it doesn’t matter when the product was ordered, what matters here is that on order was for a particular product was placed.
• 4^th and 5^th record tells us that an order for product with product_id = 1002 was placed on 01/27/2012 and the next order for the same was placed on 01/28/2012.
• The 6^th records tell us that after 1/30/2012 product with product_id = 1003 was not ordered and hence NULL was returned as next_order_date.