{"id":1770,"date":"2018-09-10T07:53:11","date_gmt":"2018-09-10T14:53:11","guid":{"rendered":"https:\/\/sqltutorial.org\/?page_id=1770"},"modified":"2025-02-04T07:06:23","modified_gmt":"2025-02-04T14:06:23","slug":"sql-lead","status":"publish","type":"page","link":"https:\/\/www.sqltutorial.org\/sql-window-functions\/sql-lead\/","title":{"rendered":"SQL LEAD Function"},"content":{"rendered":"\r\n

Summary<\/strong>: in this tutorial, you will learn how to access data of a row at a specific physical offset that follows the current row using the SQL LEAD()<\/code> function.<\/p>\r\n\r\n\r\n\r\n

Overview of SQL LEAD() function #<\/a><\/h2>\r\n\r\n\r\n\r\n

SQL LEAD()<\/code> is a window function<\/a> that provides access to a row at a specified physical offset that follows the current row.<\/p>\r\n\r\n\r\n\r\n

For example, by using the LEAD()<\/code> function, from the current row, you can access data from the next row, the second row that follows the current row, the third row that follows the current row, and so on.<\/p>\r\n\r\n\r\n\r\n

The LEAD()<\/code> function can be very useful for calculating the difference between the value of the current row and the value of the following row.<\/p>\r\n\r\n\r\n\r\n

The syntax of the LEAD()<\/code> function is as follows:<\/p>\r\n\r\n\r\n

LEAD(return_value [,offset[, default ]]) OVER (\r\n    PARTITION BY expr1, expr2,...\r\n\tORDER BY expr1 [ASC | DESC], expr2,...\r\n)\r\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
\u00a0return_value #<\/a><\/h3>\r\n\r\n\r\n\r\n
The return value of the following row offsets from the current row.<\/p>\r\n\r\n\r\n\r\n
\u00a0offset #<\/a><\/h3>\r\n\r\n\r\n\r\n
The number of rows forwards from the current row from which to access data. The offset<\/code> must be a non-negative integer. If you don’t specify offset<\/code>, it defaults to 1.<\/p>\r\n\r\n\r\n\r\n
\u00a0default #<\/a><\/h3>\r\n\r\n\r\n\r\n
The function returns default<\/code> if the offset<\/code> goes beyond the scope of the partition. If you do not specify default<\/code>, NULL<\/code> is returned.<\/p>\r\n\r\n\r\n\r\n
\u00a0PARTITION BY clause #<\/a><\/h3>\r\n\r\n\r\n\r\n
The PARTITION BY<\/code> clause divides rows of the result set into partitions to which the LEAD()<\/code> function applies. If you do not specify the PARTITION BY<\/code> clause, the whole result set is treated as a single partition.<\/p>\r\n\r\n\r\n\r\n
ORDER BY clause #<\/a><\/h3>\r\n\r\n\r\n\r\n
The ORDER BY<\/code> clause sorts the rows in each partition to which the LEAD()<\/code> function applies.<\/p>\r\n\r\n\r\n\r\n
SQL LEAD() function examples #<\/a><\/h2>\r\n\r\n\r\n\r\n
We will use the employees<\/code> table from the sample database<\/a> for demonstration purposes.<\/p>\r\n\r\n\r\n\r\n
Using SQL LEAD() function over the result set example #<\/a><\/h3>\r\n\r\n\r\n\r\n
The following statement returns, for each employee in the company, the hire date of the employee hired just after:<\/p>\r\n\r\n\r\n
SELECT<\/span> \r\n\tfirst_name,\r\n\tlast_name, \r\n\thire_date, \r\n\tLEAD<\/span>(hire_date, 1<\/span>) OVER<\/span> (\r\n\t\tORDER<\/span> BY<\/span> hire_date\r\n\t) AS<\/span> next_hired\r\nFROM<\/span> \r\n\temployees;\r\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
The following shows the output:<\/p>\r\n\r\n\r\n\r\n
\"SQL<\/figure>\r\n\r\n\r\n\r\n
In this example, we omitted the PARTITION BY<\/code> clause. Therefore, the whole result was treated as a single partition. The ORDER BY<\/code> clause sorted employees by hire dates in ascending order. The LEAD()<\/code> function is applied to each row in the result set.<\/p>\r\n\r\n\r\n\r\n
Using SQL LEAD() function over partition example #<\/a><\/h3>\r\n\r\n\r\n\r\n
The following statement provides, for each employee, the hire date of the employee in the same department who was hired just after:<\/p>\r\n\r\n\r\n
SELECT<\/span> \r\n\tfirst_name,\r\n\tlast_name, \r\n\tdepartment_name,\r\n\thire_date, \r\n\tLEAD<\/span>(hire_date, 1<\/span>, 'N\/A'<\/span>) OVER<\/span> (\r\n\t\tPARTITION<\/span> by<\/span> department_name\r\n\t\tORDER<\/span> BY<\/span> hire_date\r\n\t) AS<\/span> next_hire_date\r\nFROM<\/span> \r\n\temployees e\r\nINNER<\/span> JOIN<\/span> departments d ON<\/span> \r\n\td.department_id = e.department_id;\r\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
The following picture shows the partial output:<\/p>\r\n\r\n\r\n\r\n
\"SQL<\/figure>\r\n\r\n\r\n\r\n
In this example, we used the PARTITION BY<\/code> clause to divide the employees by departments into partitions and use the ORDER BY<\/code> clause to sort\u00a0the employees in each department by hire dates in ascending order.<\/p>\r\n\r\n\r\n\r\n
The LEAD()<\/code> function was applied to each sorted partition independently to get the next hire dates of the employees in each department.<\/p>\r\n\r\n\r\n\r\n
In this tutorial, you have learned how to use the SQL LEAD()<\/code> function to access data of the forward row from the current row.<\/p>\r\n\r\n\r\n\r\n
Databases #<\/a><\/h2>\r\n\r\n\r\n\r\n