{"id":7001,"date":"2018-08-26T06:48:23","date_gmt":"2018-08-26T13:48:23","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=7001"},"modified":"2024-01-04T03:54:40","modified_gmt":"2024-01-04T10:54:40","slug":"mysql-lead-function","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-window-functions\/mysql-lead-function\/","title":{"rendered":"MySQL LEAD Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MySQL LEAD()<\/code> function to access data from the next row in the result set <\/p>\n\n\n\n

Introduction to MySQL LEAD() function<\/h2>\n\n\n\n

The LEAD()<\/code> function is a window function<\/a> that allows you to access data from the next row in a result set<\/p>\n\n\n\n

Here’s the basic syntax of the LEAD()<\/code> function:<\/p>\n\n\n

LEAD(expression, offset, default_value) \nOVER (\n  PARTITION BY partition_expression \n  ORDER BY sort_expression\n)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax:<\/p>\n\n\n\n
expression<\/h3>\n\n\n\n
This is the column or expression from which you want to retrieve the next value.<\/p>\n\n\n\n
offset<\/h3>\n\n\n\n
The offset<\/code> specifies the number of rows to look ahead. If you skip it, it defaults to 1, which is the immediate row.<\/p>\n\n\n\n
default_value<\/h3>\n\n\n\n
This is the default value if there is no next row. For example, the last row in the result set (or in a partition) will not have the next row. <\/p>\n\n\n\n
If you don’t specify the default_value, it’ll default to NULL.<\/p>\n\n\n\n
PARTITION BY partition_expression<\/h3>\n\n\n\n
The PARTITION BY<\/code> is an optional clause that divides the result set into partitions to which the LEAD()<\/code> function is applied independently.<\/p>\n\n\n\n
ORDER BY order_expression<\/h3>\n\n\n\n
The ORDER BY<\/code> clause specifies the order in which the rows are processed within each partition.<\/p>\n\n\n\n
The LEAD()<\/code> function can be useful for queries like finding the next value in a sequence or calculating the differences between the current and the next values in a column.<\/p>\n\n\n\n
MySQL LEAD() function examples<\/h2>\n\n\n\n
Let’s take some examples of using the LEAD()<\/code> function. We’ll use the following sales<\/code> table for the demonstration:<\/p>\n\n\n
CREATE TABLE sales(\r\n    sales_employee VARCHAR(50<\/span>) NOT NULL<\/span>,\r\n    fiscal_year INT NOT NULL<\/span>,\r\n    sale DECIMAL(14<\/span>,2<\/span>) NOT NULL<\/span>,\r\n    PRIMARY KEY(sales_employee,fiscal_year)\r\n);\r\n\r\nINSERT INTO sales(sales_employee,fiscal_year,sale)\r\nVALUES('Bob'<\/span>,2016<\/span>,100<\/span>),\r\n      ('Bob'<\/span>,2017<\/span>,150<\/span>),\r\n      ('Bob'<\/span>,2018<\/span>,200<\/span>),\r\n      ('Alice'<\/span>,2016<\/span>,150<\/span>),\r\n      ('Alice'<\/span>,2017<\/span>,100<\/span>),\r\n      ('Alice'<\/span>,2018<\/span>,200<\/span>),\r\n       ('John'<\/span>,2016<\/span>,200<\/span>),\r\n      ('John'<\/span>,2017<\/span>,150<\/span>),\r\n      ('John'<\/span>,2018<\/span>,250<\/span>);\r\n\r\nSELECT * FROM sales;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+----------------+-------------+--------+\r\n| sales_employee | fiscal_year | sale   |\r\n+----------------+-------------+--------+\r\n| Alice          |        2016 | 150.00 |\r\n| Alice          |        2017 | 100.00 |\r\n| Alice          |        2018 | 200.00 |\r\n| Bob            |        2016 | 100.00 |\r\n| Bob            |        2017 | 150.00 |\r\n| Bob            |        2018 | 200.00 |\r\n| John           |        2016 | 200.00 |\r\n| John           |        2017 | 150.00 |\r\n| John           |        2018 | 250.00 |\r\n+----------------+-------------+--------+<\/code><\/span><\/pre>\n\n\n
1) Basic MySQL LEAD() function example<\/h3>\n\n\n\n
The following example uses the LEAD()<\/code> function to pull the sales of the next row into the current row:<\/p>\n\n\n
SELECT<\/span> \n  sales_employee,\n  fiscal_year, \n  sale, \n  LEAD<\/span>(sale) OVER<\/span> (\n    PARTITION<\/span> BY<\/span> sales_employee\n    ORDER<\/span> BY<\/span> fiscal_year\n  ) AS<\/span> next_year_sale \nFROM<\/span> \n  sales;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+----------------+-------------+--------+----------------+\r\n| sales_employee | fiscal_year | sale   | next_year_sale |\r\n+----------------+-------------+--------+----------------+\r\n| Alice          |        2016 | 150.00 |         100.00 |\r\n| Alice          |        2017 | 100.00 |         200.00 |\r\n| Alice          |        2018 | 200.00 |           NULL |\r\n| Bob            |        2016 | 100.00 |         150.00 |\r\n| Bob            |        2017 | 150.00 |         200.00 |\r\n| Bob            |        2018 | 200.00 |           NULL |\r\n| John           |        2016 | 200.00 |         150.00 |\r\n| John           |        2017 | 150.00 |         250.00 |\r\n| John           |        2018 | 250.00 |           NULL |\r\n+----------------+-------------+--------+----------------+\r\n9 rows in set (0.00 sec)<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
First, the PARTITION BY sales_employee<\/code> divides the rows in the table into three partitions, each representing the sales of one sales employee.<\/p>\n\n\n\n
Second, the ORDER BY fiscal_year<\/code> sorts the rows in each partition by fiscal year in ascending order.<\/p>\n\n\n\n
Therefore, the LEAD()<\/code> function returns the sales of the next year from the current year for each sales employee. If there is no next year such as for the year 2018, the LEAD()<\/code> function returns NULL.<\/p>\n\n\n\n
2) Using multiple LEAD() functions in a query<\/h3>\n\n\n\n
The following query uses multiple LEAD()<\/code> functions to calculate the sales vs. next year’s in percentage:<\/p>\n\n\n
SELECT \r\n  sales_employee, \r\n  fiscal_year, \r\n  sale, \r\n  LEAD(sale) OVER (\r\n    PARTITION BY sales_employee \r\n    ORDER BY fiscal_year\r\n  ) AS<\/span> next_year_sale,\r\n  ROUND(sale - (LEAD(sale) OVER (\r\n    PARTITION BY sales_employee \r\n    ORDER BY fiscal_year)) \/ sale * 100<\/span>, 2<\/span>)  'vs_next_year (%)'<\/span>\r\nFROM \r\n  sales;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+----------------+-------------+--------+----------------+------------------+\r\n| sales_employee | fiscal_year | sale   | next_year_sale | vs_next_year (%) |\r\n+----------------+-------------+--------+----------------+------------------+\r\n| Alice          |        2016<\/span> | 150.00<\/span> |         100.00<\/span> |            83.33<\/span> |\r\n| Alice          |        2017<\/span> | 100.00<\/span> |         200.00<\/span> |          -100.00<\/span> |\r\n| Alice          |        2018<\/span> | 200.00<\/span> |           NULL<\/span> |             NULL<\/span> |\r\n| Bob            |        2016<\/span> | 100.00<\/span> |         150.00<\/span> |           -50.00<\/span> |\r\n| Bob            |        2017<\/span> | 150.00<\/span> |         200.00<\/span> |            16.67<\/span> |\r\n| Bob            |        2018<\/span> | 200.00<\/span> |           NULL<\/span> |             NULL<\/span> |\r\n| John           |        2016<\/span> | 200.00<\/span> |         150.00<\/span> |           125.00<\/span> |\r\n| John           |        2017<\/span> | 150.00<\/span> |         250.00<\/span> |           -16.67<\/span> |\r\n| John           |        2018<\/span> | 250.00<\/span> |           NULL<\/span> |             NULL<\/span> |\r\n+----------------+-------------+--------+----------------+------------------+\r\n9<\/span> rows in set (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example, we added a new column named vs_next_year (%)<\/code>. This column calculates the percentage change from the current year’s sales to the next year’s sales using the formula:<\/p>\n\n\n
( current year's sale - next year'<\/span>s sale ) x 100<\/span> \/ next year's sale<\/span><\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
This expression calculates the percentage change and assigns it to the vs_next_year (%)<\/code><\/code> column.<\/p>\n\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Use the MySQL LEAD()<\/code> function to retrieve the value from the next row in a specified sequence within a partitioned result set.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t