LEFT JOIN in MySQL

I encounter a complex SQL query when walking through the source code of chillyCMS:

select m1.*, ifnull(m2.id,0) as prev, ifnull(m3.id,0) as next, s.name as modname from site_modules as m1 
left join site_modules m2 on m2.order = m1.order-1 and m1.position = m2.position 
left join site_modules m3 on m3.order = m1.order+1 and m1.position = m3.position 
left join system_modules s on s.modid = m1.modid order by m1.position,m1.order;

And I explore the topic in this post.

the table is named site_modules, its data is:

id	name	modid	position	order	active	access	specialaccess	settings
1	Mainmenu	2	left	1	1	0		1,1,1,0
2	Special Menu	2	left	2	1	1		10,1,1,0
3	Login	3	right	1	1	0		1,1,1,1,1
4	Popular	14	right	2	1	0		5
5	Visitors	17	user0	1	1	0		0,5,oldschool.jpg

Simplity the query first of all, perform the query:

select m1.id, m1.position, m1.order, m2.id, m2.position, m2.order 
from site_modules as m1 
left join site_modules m2 on m1.position = m2.position;

(according to mysql online toturial, this query is quivalent to:

select m1.id, m1.position, m1.order, m2.id, m2.position, m2.order 
from site_modules as m1 
left join site_modules m2 using (position);

)

id	position	order	id	position	order
1	left	1	1	left	1
1	left	1	2	left	2
2	left	2	1	left	1
2	left	2	2	left	2
3	right	1	3	right	1
3	right	1	4	right	2
4	right	2	3	right	1
4	right	2	4	right	2
5	user0	1	5	user0	1

For investigation purpose, let's execute the query:

select m1.id, m1.position, m1.order, m2.id, m2.position, m2.order
from site_modules as m1 
left join site_modules m2 on m1.position = m2.position and m2.order = m1.order-1; 

and the output:

id	position	order	id	position	order
1	left	1
2	left	2	1	left	1
3	right	1
4	right	2	3	right	1
5	user0	1

Since left join preserves all the records in the left table, it will yeild some combination having the second table's records as null. 

Then, perform the query with twice left join:

select m1.id, m1.position, m1.order, m2.id, m2.position, m2.order, m3.id, m3.position, m3.order from site_modules as m1 left join site_modules m2 on m1.position = m2.position 
left join site_modules m3 on m1.position = m3.position;

id	position	order	id	position	order	id	position	order
1	left	1	1	left	1	1	left	1
1	left	1	1	left	1	2	left	2
1	left	1	2	left	2	1	left	1
1	left	1	2	left	2	2	left	2
2	left	2	1	left	1	1	left	1
2	left	2	1	left	1	2	left	2
2	left	2	2	left	2	1	left	1
2	left	2	2	left	2	2	left	2
3	right	1	3	right	1	3	right	1
3	right	1	3	right	1	4	right	2
3	right	1	4	right	2	3	right	1
3	right	1	4	right	2	4	right	2
4	right	2	3	right	1	3	right	1
4	right	2	3	right	1	4	right	2
4	right	2	4	right	2	3	right	1
4	right	2	4	right	2	4	right	2
5	user0	1	5	user0	1	5	user0	1

(the cell with blue coloe means m3.order = m1.order+1; andyellow means m2.order = m1.order-1)

Next, we add back one more condition, which the second order is one less than 1st one:

select m1.id, m1.position, m1.order, m2.id, m2.position, m2.order, m3.id, m3.position, m3.order 
from site_modules as m1 
left join site_modules m2 on m1.position = m2.position and m2.order = m1.order-1
left join site_modules m3 on m1.position = m3.position; 

the output:

id	position	order	id	position	order	id	position	order
1	left	1				1	left	1
1	left	1				2	left	2
2	left	2	1	left	1	1	left	1
2	left	2	1	left	1	2	left	2
3	right	1				3	right	1
3	right	1				4	right	2
4	right	2	3	right	1	3	right	1
4	right	2	3	right	1	4	right	2
5	user0	1				5	user0	1

Remove the condition just added, but add back the condition: m3.order = m1.order+1

select m1.id, m1.position, m1.order, m2.id, m2.position, m2.order, m3.id, m3.position, m3.order 
from site_modules as m1 
left join site_modules m2 on m1.position = m2.position
left join site_modules m3 on m1.position = m3.position and m3.order = m1.order+1; 

id	position	order	id	position	order	id	position	order
1	left	1	1	left	1	2	left	2
1	left	1	2	left	2	2	left	2
2	left	2	1	left	1
2	left	2	2	left	2
3	right	1	3	right	1	4	right	2
3	right	1	4	right	2	4	right	2
4	right	2	3	right	1
4	right	2	4	right	2
5	user0	1	5	user0	1

Then, we run the query:

select m1.id, m1.position, m1.order, m2.id, m2.position, m2.order, m3.id, m3.position, m3.order 
from site_modules as m1 
left join site_modules m2 on m1.position = m2.position and m2.order = m1.order-1
left join site_modules m3 on m1.position = m3.position and m3.order = m1.order+1;

and the output:

id	position	order	id	position	order	id	position	order
1	left	1				2	left	2
2	left	2	1	left	1
3	right	1				4	right	2
4	right	2	3	right	1
5	user0	1

Then perform the query:

select m1.id, m1.position, m1.order, m2.id, m2.position, m2.order, m3.id, m3.position, m3.order, s.modid 
from site_modules as m1 
left join site_modules m2 on m1.position = m2.position and m2.order = m1.order-1
left join site_modules m3 on m1.position = m3.position and m3.order = m1.order+1
left join system_modules s on s.modid = m1.modid;

and the output:

id	name	modid	position	order	active	access	specialaccess	settings	prev	next	modname
1	Mainmenu	2	left	1	1	0		1,1,1,0	0	2	menu
2	Special Menu	2	left	2	1	1		10,1,1,0	1	0	menu
3	Login	3	right	1	1	0		1,1,1,1,1	0	4	login
4	Popular	14	right	2	1	0		5	3	0	populararticles
5	Visitors	17	user0	1	1	0		0,5,oldschool.jpg	0	0	visitorcounter

reference:

http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_ifnull