Oracle LEAD

Summary: in this tutorial, you will learn how to access the following row from the current row by using the Oracle LEAD() function.

Introduction to Oracle LEAD() function #

Oracle LEAD() is an analytic function that allows you to access the following row from the current row without using a self-join.

The following shows the syntax of the LEAD() function:

LEAD(expression [, offset ] [, default ])
OVER (
	[ query_partition_clause ] 
	order_by_clause
)Code language: SQL (Structured Query Language) (sql)

In this syntax:

expression #

is a scalar expression evaluated against the value of the row specified by offset. It must return a single value.

offset #

is the number of rows forwarding from the current row from which to get the value. The default is 1.

default #

If the offset goes beyond the scope of the partition, the function returns the default. If you skip the default, then the function will return NULL.

query_partition_clause #

The query_partition_clause clause divided the rows into partitions to which the LEAD() function is applied. By default, the function treats the whole result set as a single partition.

order_by_clause #

The order_by_clause clause specifies the order of the rows in each partition to which the LEAD() function is applied.

The LEAD() function is very useful for calculating the difference between the values of current and subsequent rows.

Oracle LEAD() function examples #

We will create a view named salesman_performance that returns the sales of the salesman by year based on the orders and order_items tables from the sample database:

CREATE OR REPLACE VIEW salesman_performance (salesman_id, YEAR, sales) AS
SELECT
  salesman_id,
  EXTRACT(
    YEAR
    FROM
      order_date
  ),
  SUM(quantity * unit_price)
FROM
  orders
  INNER JOIN order_items USING (order_id)
WHERE
  salesman_id IS NOT NULL
GROUP BY
  salesman_id,
  EXTRACT(
    YEAR
    FROM
      order_date
  );Code language: SQL (Structured Query Language) (sql)

The following statement returns the data through the salesman_performance view :

SELECT
  salesman_id,
  YEAR,
  sales
FROM
  salesman_performance
ORDER BY
  salesman_id,
  YEAR,
  sales;Code language: SQL (Structured Query Language) (sql)

Here is the partial output:

Using Oracle LEAD() function over the result set example #

The following query uses the LEAD() function to return sales of the following year of the salesman id 55:

SELECT
  salesman_id,
  YEAR,
  sales,
  LEAD (sales) OVER (
    ORDER BY
      YEAR
  ) following_year_sales
FROM
  salesman_performance
WHERE
  salesman_id = 55;Code language: SQL (Structured Query Language) (sql)

Here is the output:

The last row returned NULL for the following_year_sales column because the offset went beyond the scope of the result set.

Using Oracle LEAD() function over partitions example #

The following statement uses the LEAD() function to return sales of the following year for every salesman:

SELECT
  salesman_id,
  YEAR,
  sales,
  LEAD (sales) OVER (
    PARTITION BY
      SALESMAN_ID
    ORDER BY
      YEAR
  ) following_year_sales
FROM
  salesman_performance;Code language: SQL (Structured Query Language) (sql)

The following picture displays the partial output:

Summary #

• Use the Oracle LEAD() function to access data of the following row from the current row in the partition without using a self-join.