SQL LEAD Function: Explained, Emulated and Compared

We emulate the SQL Lead window function with the ROWS BETWEEN clause, and compare it with a direct call. We show you a visual image of the partition on which both LEAD/LAG and ROWS-based window functions work.

Visual SQL Query with the SQL LEAD Function

If you have time and want to do it hands-on, download the free Data Xtractor for SQLite, and create the SQL query below with the visual query builder. It should work on most databases with window functions support, but try it for free on SQLite.

What it does is it selects all fruit names from a simple dynamic table (FruitsU is the result of a UNION SELECT which returns rows with constants that work on ANY database).

1. First column returns all fruit names sorted in ascending order.
2. Second column uses our LEAD function with an offset of 2, on a similar partition of fruit names.
3. Third column emulates our SQL LEAD function with the MAX aggregate OVER the same partition, but limiting the window with the ROWS BETWEEN clause, to select one single entry two rows ahead.

Second and third column both return a value not yet displayed by the main query, at two rows difference ahead. This is what LEAD does: it looks forward on the partition, starting from the current row, and it returns the row value at some specified offset ahead.

Generated SQL Query with LEAD Call

Switch to SQL mode, and you see this generated SQL query (we removed parts of the UNION SELECT to keep it short and focused):

WITH fruitsu_ AS (
   SELECT 'Apples' AS "name"
   UNION SELECT 'Oranges'
   UNION SELECT 'Plums'
   ...
   UNION SELECT 'Clementines')

SELECT fruitsu_."name" AS "name",
   LEAD(fruitsu_."name", 2) OVER (ORDER BY fruitsu_."name") AS "Lead",
   MAX(fruitsu_."name") OVER (ORDER BY fruitsu_."name"
      ROWS BETWEEN 2 FOLLOWING AND 2 FOLLOWING) AS "Max"
FROM fruitsu_
ORDER BY 1

The initial top subquery just returns a few rows with fruit names. All window function calls are in the second SELECT subquery.

Second SELECT line calls the SQL LEAD function as we described before. Remark the OVER clauses after both LEAD and MAX, which define partitions similar to the main query, with sorted fruit names. In real-life scenarios, the partitions may be different, but here, for demo purposes, it’s just easier to use the same set.

How the SQL LEAD Function Actually Works

Both LEAD and LAG window functions look at some row forwards or backwards from the current row, in their partitions defined with OVER. In our example, the SQL LEAD function jumped two rows ahead. For Grapes in current row, it returned Melons.

When emulated with ROWS BETWEEN n AND n, we also limited the partition to the one record n rows ahead. You can call a MIN or MAX aggregate function, or even a SUM or AVG on numeric fields, to return that single value.

For the last two rows, both LEAD and MAX look two records ahead, but they find nothing and return NULL.

Support and Compatibility Issues

This has been tested with Data Xtractor and works well on the following relational database systems, which have window function support:

• SQLite – which supports window functions since 2018, with v3.25.
• Oracle – it works fine.
• Microsoft SQL Server – works well, also on Azure.
• IBM DB2 – works fine.
• PostgreSQL – ok. Amazon Redshift, which is based on Postgres, should work as well (not tested yet).
• MySQL – which supports window functions since v8.0.2. Amazon Aurora, which is MySQL-based, should work as well once they start using a more recent version.
• MariaDB – which supports window functions since v10.2. It works only on numeric fields. Text fields likely have a bug, they return some binary data instead.
• Firebird – LEAD works fine, but it does not support the ROWS/RANGE clause yet.
• SQL Anywhere – it may work as well, but not tested.