LEAD and LAG Functions

Lead and Lag Functions can be used instead of self joins, if we need to access multiple rows within a table to combine them to get the desired results.

 

Lead and Lag Functions work pretty much the same in SQL Server and Oracle except that it was only implemented in SQL Server 2012 for all older versions we still need to use self join, see below for an example

 

SQL SERVER 2012

 

SYNTAX:

               LEAD ( scalar_expression [ ,offset ] , [ default ] ) OVER ( [ partition_by_clause ] order_by_clause )

               LAG ( scalar_expression [ ,offset ] , [ default ] ) OVER ( [ partition_by_clause ] order_by_clause )

 

Note: offset, default and partition by clause are optional, if not specified they are defaulted to 1 and null respectively

 

Example

 

In the below example we are trying to get the current, previous and next salary’s of an employee, which can be used to determine how much raise an employee got, for better understanding the demo we are putting all the records of an employee next to each other in the below table (CTE Table), they need not be next to each other

 

; WITH EMPLOYEES AS

(

SELECT 'SAM' AS EMPLOYEE_NAME, 80 AS SALARY, CAST ('11/01/2000' AS DATE) AS EFFECTIVE_ENDDATE

UNION ALL

SELECT 'SAM' AS EMPLOYEE_NAME, 100 AS SALARY, CAST ('10/01/2012' AS DATE) AS EFFECTIVE_ENDDATE

UNION ALL

SELECT 'SAM' AS EMPLOYEE_NAME, 120 AS SALARY, CAST ('09/09/9999' AS DATE) AS EFFECTIVE_ENDDATE

UNION ALL

SELECT 'PAT' AS EMPLOYEE_NAME, 50 AS SALARY, CAST ('12/31/2005' AS DATE) AS EFFECTIVE_ENDDATE

UNION ALL

SELECT 'PAT' AS EMPLOYEE_NAME, 110 AS SALARY, CAST ('01/01/2012' AS DATE) AS EFFECTIVE_ENDDATE

UNION ALL

SELECT 'PAT' AS EMPLOYEE_NAME, 150 AS SALARY, CAST ('09/09/9999' AS DATE) AS EFFECTIVE_ENDDATE

UNION ALL

SELECT 'TOM' AS EMPLOYEE_NAME, 10 AS SALARY, CAST ('04/01/2004' AS DATE) AS EFFECTIVE_ENDDATE

UNION ALL

SELECT 'TOM' AS EMPLOYEE_NAME, 90 AS SALARY, CAST ('08/01/2010' AS DATE) AS EFFECTIVE_ENDDATE

UNION ALL

SELECT 'TOM' AS EMPLOYEE_NAME, 190 AS SALARY, CAST ('09/09/9999' AS DATE) AS EFFECTIVE_ENDDATE

)

SELECT

EMPLOYEE_NAME,

LAG (SALARY, 1, 0) OVER (PARTITION BY EMPLOYEE_NAME ORDER BY EFFECTIVE_ENDDATE) PREV_SALARY

, SALARY AS CURR_SALARY

, LEAD (SALARY, 1, 0) OVER (PARTITION BY EMPLOYEE_NAME ORDER BY EFFECTIVE_ENDDATE) NEXT_SALARY

FROM EMPLOYEES

ORDER BY EMPLOYEE_NAME

 

Output

EMPLOYEE_NAME

PREV_SALARY

CURR_SALARY

NEXT_SALARY

Pat

0

50

110

Pat

50

110

150

Pat

110

150

0

Sam

0

80

100

Sam

80

100

120

Sam

100

120

0

Tom

0

10

90

Tom

10

90

190

Tom

90

190

0

 

 

SQL Server Older Versions Using Self Join

 

As we can see below we are joining the same table (here CTE) to itself, to determine the row before and after the current row we are using row number function and for lag we are joining current record row number to Previous record row number+1 similarly for Lead we are joining current record row number to Previous record row number-1 as shown below

Example

            ; WITH EMPLOYEES AS

(

SELECT 'SAM' AS EMPLOYEE_NAME, 80 AS SALARY, CAST ('11/01/2000' AS DATE) AS EFFECTIVE_ENDDATE

UNION ALL

SELECT 'SAM' AS EMPLOYEE_NAME, 100 AS SALARY, CAST ('10/01/2012' AS DATE) AS EFFECTIVE_ENDDATE

UNION ALL

SELECT 'SAM' AS EMPLOYEE_NAME, 120 AS SALARY, CAST ('09/09/9999' AS DATE) AS EFFECTIVE_ENDDATE

UNION ALL

SELECT 'PAT' AS EMPLOYEE_NAME, 50 AS SALARY, CAST ('12/31/2005' AS DATE) AS EFFECTIVE_ENDDATE

UNION ALL

SELECT 'PAT' AS EMPLOYEE_NAME, 110 AS SALARY, CAST ('01/01/2012' AS DATE) AS EFFECTIVE_ENDDATE

UNION ALL

SELECT 'PAT' AS EMPLOYEE_NAME, 150 AS SALARY, CAST ('09/09/9999' AS DATE) AS EFFECTIVE_ENDDATE

UNION ALL

SELECT 'TOM' AS EMPLOYEE_NAME, 10 AS SALARY, CAST ('04/01/2004' AS DATE) AS EFFECTIVE_ENDDATE

UNION ALL

SELECT 'TOM' AS EMPLOYEE_NAME, 90 AS SALARY, CAST ('08/01/2010' AS DATE) AS EFFECTIVE_ENDDATE

UNION ALL

SELECT 'TOM' AS EMPLOYEE_NAME, 190 AS SALARY, CAST ('09/09/9999' AS DATE) AS EFFECTIVE_ENDDATE

)

 

SELECT

CURR.EMPLOYEE_NAME,

PREV.SALARY AS PREV_SALARY,

CURR.SALARY,

NEX.SALARY AS NEXT_SALARY

FROM

(

            SELECT

                        EMPLOYEE_NAME,

                        ROW_NUMBER () OVER (PARTITION BY EMPLOYEE_NAME ORDER BY EFFECTIVE_ENDDATE) ROWNUM

                        , SALARY

            FROM EMPLOYEES

) CURR

LEFT JOIN

(

            SELECT

                        EMPLOYEE_NAME,

                        ROW_NUMBER () OVER (PARTITION BY EMPLOYEE_NAME ORDER BY EFFECTIVE_ENDDATE) ROWNUM

                        , SALARY

            FROM EMPLOYEES

) PREV

            ON CURR.EMPLOYEE_NAME=PREV.EMPLOYEE_NAME

            AND CURR.ROWNUM=PREV.ROWNUM+1   --or this can be CURR.ROWNUM -1=PREV.ROWNUM either way works

LEFT JOIN

(

            SELECT

                        EMPLOYEE_NAME,

                        ROW_NUMBER () OVER (PARTITION BY EMPLOYEE_NAME ORDER BY EFFECTIVE_ENDDATE) ROWNUM

                        , SALARY

            FROM EMPLOYEES

) NEX

            ON CURR.EMPLOYEE_NAME=NEX.EMPLOYEE_NAME

            AND CURR.ROWNUM=NEX.ROWNUM-1 --or this can be CURR.ROWNUM +1=NEX.ROWNUM either way works

ORDER BY EMPLOYEE_NAME

 

 

ORACLE

 

SYNTAX:

               LEAD ( scalar_expression [ ,offset ] , [ default ] ) OVER ( [ partition_by_clause ] order_by_clause )

               LAG ( scalar_expression [ ,offset ] , [ default ] ) OVER ( [ partition_by_clause ] order_by_clause )

 

Note: offset, default and partition by clause are optional, if not specified they are defaulted to 1 and null respectively

 

Example

 

In the below example we are trying to get the current, previous and next salary’s of an employee, which can be used to determine how much raise an employee got, for better understanding the demo we are putting all the records of an employee next to each other in the below table (CTE Table), they need not be next to each other

 

            WITH EMPLOYEES AS

(

SELECT 'SAM' AS EMPLOYEE_NAME, 80 AS SALARY, TO_DATE('11/01/2000', 'MM/DD/YYYY')  AS EFFECTIVE_ENDDATE FROM DUAL

UNION ALL

SELECT 'SAM' AS EMPLOYEE_NAME, 100 AS SALARY, TO_DATE('10/01/2012', 'MM/DD/YYYY')  AS EFFECTIVE_ENDDATE FROM DUAL

UNION ALL

SELECT 'SAM' AS EMPLOYEE_NAME, 120 AS SALARY, TO_DATE('09/09/9999', 'MM/DD/YYYY')  AS EFFECTIVE_ENDDATE FROM DUAL

UNION ALL

SELECT 'PAT' AS EMPLOYEE_NAME, 50 AS SALARY, TO_DATE('12/31/2005', 'MM/DD/YYYY')  AS EFFECTIVE_ENDDATE FROM DUAL

UNION ALL

SELECT 'PAT' AS EMPLOYEE_NAME, 110 AS SALARY, TO_DATE('01/01/2012', 'MM/DD/YYYY')  AS EFFECTIVE_ENDDATE FROM DUAL

UNION ALL

SELECT 'PAT' AS EMPLOYEE_NAME, 150 AS SALARY, TO_DATE('09/09/9999', 'MM/DD/YYYY')  AS EFFECTIVE_ENDDATE FROM DUAL

UNION ALL

SELECT 'TOM' AS EMPLOYEE_NAME, 10 AS SALARY, TO_DATE('04/01/2004', 'MM/DD/YYYY')  AS EFFECTIVE_ENDDATE FROM DUAL

UNION ALL

SELECT 'TOM' AS EMPLOYEE_NAME, 90 AS SALARY, TO_DATE('08/01/2010', 'MM/DD/YYYY')  AS EFFECTIVE_ENDDATE FROM DUAL

UNION ALL

SELECT 'TOM' AS EMPLOYEE_NAME, 190 AS SALARY, TO_DATE('09/09/9999', 'MM/DD/YYYY')  AS EFFECTIVE_ENDDATE FROM DUAL

)

SELECT

EMPLOYEE_NAME,

LAG (SALARY, 1, 0) OVER (PARTITION BY EMPLOYEE_NAME ORDER BY EFFECTIVE_ENDDATE) PREV_SALARY

, SALARY AS CURR_SALARY

, LEAD (SALARY, 1, 0) OVER (PARTITION BY EMPLOYEE_NAME ORDER BY EFFECTIVE_ENDDATE) NEXT_SALARY

FROM EMPLOYEES

ORDER BY EMPLOYEE_NAME

 

OUTPUT

EMPLOYEE_NAME

PREV_SALARY

CURR_SALARY

NEXT_SALARY

Pat

0

50

110

Pat

50

110

150

Pat

110

150

0

Sam

0

80

100

Sam

80

100

120

Sam

100

120

0

Tom

0

10

90

Tom

10

90

190

Tom

90

190

0

 

Thank you for reading this article, check out my other SQL Server vs. Oracle posts.