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.