SQL - join

join的七种用法

story table

story_id	story_name	create_user_id	update_user_id
1	1111	1	2
2	2222	1	3
3	3333	4	10
4	4444	5	10

user table

user_id	user_name
1	AAA
2	BBB
3	CCC
4	DDD

LEFT JOIN

select A.story_id id,
	   A.story story,
	   B.user_name create_user_name,
	   C.user_name update_user_name
from story A
left join user B on A.create_user_id = B.user_id
left join user C on A.update_user_id = C.user_id

结果：

id	story	create_user_name	update_user_name
1	1111	AAA	BBB
2	2222	AAA	CCC
3	3333	DDD
4	4444

where

select A.story_id id,
	   A.story story,
	   B.user_name create_user_name,
	   C.user_name update_user_name
from story A, user B, user C
where A.create_user_id = B.user_id and A.update_user_id = C.user_id

结果：

id	story	create_user_name	update_user_name
1	1111	AAA	BBB
2	2222	AAA	CCC