MySQL lead() lag() non-aggregate functions in MySQL

Using non-aggregate function LEAD(), LAG() with OVER we can compare row value with previous and next values.

LEAD(val) OVER ([ partition_clause ] order_by)

Note : LEAD() , LAG() are available in MySQL – 8 and above only.

SELECT id, name,class,mark,gender,
LEAD(mark) OVER(ORDER BY mark ) as my_lead
FROM student WHERE class='Four';

The last row value for my_lead is NULL as there is no next row to use the value.

id	name	class	mark	gender	my_lead
6	Alex John	Four	55	male	55
10	Big John	Four	55	female	60
4	Krish Star	Four	60	female	60
5	John Mike	Four	60	female	69
21	Babby John	Four	69	female	75
1	John Deo	Four	75	female	88
15	Tade Row	Four	88	male	88
16	Gimmy	Four	88	male	88
31	Marry Toeey	Four	88	male	NULL

Using PRTITION

There are two NULL values for my_lead due to PARTITION over gender column.

SELECT id, name,class,mark,gender,
LEAD(mark) OVER(PARTITION by GENDER ORDER BY mark ) as my_lead
FROM student WHERE class='Four';

id	name	class	mark	gender	my_lead
10	Big John	Four	55	female	60
4	Krish Star	Four	60	female	60
5	John Mike	Four	60	female	69
21	Babby John	Four	69	female	75
1	John Deo	Four	75	female	NULL
6	Alex John	Four	55	male	88
15	Tade Row	Four	88	male	88
16	Gimmy	Four	88	male	88
31	Marry Toeey	Four	88	male	NULL

The lagging column value is displayed for comparison, for the first row NULL is shown.

SELECT id, name,class,mark,gender,
LAG(mark) OVER(ORDER BY mark ) as my_lag
FROM student WHERE class='Four';

Output

id	name	class	mark	gender	my_lead
6	Alex John	Four	55	male	NULL
10	Big John	Four	55	female	55
4	Krish Star	Four	60	female	55
5	John Mike	Four	60	female	60
21	Babby John	Four	69	female	60
1	John Deo	Four	75	female	69
15	Tade Row	Four	88	male	75
16	Gimmy	Four	88	male	88
31	Marry Toeey	Four	88	male	88

The window is created by using gender column so NULL is present at the staring of the window ( id = 10 , id = 6 )

SELECT id, name,class,mark,gender,
LAG(mark) OVER(PARTITION BY gender ORDER BY mark ) as my_lag
FROM student WHERE class='Four';

id	name	class	mark	gender	my_lead
10	Big John	Four	55	female	NULL
4	Krish Star	Four	60	female	55
5	John Mike	Four	60	female	60
21	Babby John	Four	69	female	60
1	John Deo	Four	75	female	69
6	Alex John	Four	55	male	NULL
15	Tade Row	Four	88	male	55
16	Gimmy	Four	88	male	88
31	Marry Toeey	Four	88	male	88