SQL多表连接

有这样一个站点：

用户存在user表中，然后有role角色表，通过user_to_role表实现user和role之间的多对多关系。

另外有action表，里面存有站点的各个url——或者说功能。然后有action_group表，通过action_to_action_group表和action表形成多对多关系。这样在给用户发放权限的时候就可以发放“套餐”，这样管理会方便一些。

而role和action_group通过role_to_action_group表形成多对多关系。

这样当一个user登录后，通过user->user_to_role->role->role_to_action_group->action_group->action_to_action_group->action这样的链条就可以查询到该user有权访问的全部action。这样的关系通过ORM来写实在是太复杂了，所以最好还是使用原生SQL来写。

因为涉及到跨表，所以现代SQL推荐采用JOIN方法来处理。

在SQL中

SELECT tb1.name AS name, tb2.mark AS mark
FROM tb1 LEFT JOIN tb2 ON tb1.id=tb2.tb1_id
WHERE tb1.name='test'

JOIN语法有左侧的表tb1和右侧的表tb2

LEFT JOIN的意思就是说，以左侧的tb1为基础形成一个扩展表，扩展表会把tb1和tb2的各个column加入到扩展表中，然后把tb1中的所有条目填充到扩展表中。然后以ON为条件，针对tb1的每一条到右侧的tb2中查找对应的条目，如果有符合ON条件的则把tb2的条目填充到扩展表中的对应条目。如果tb2中没有符合ON条件的，则以null填充到扩展表中tb1条目对应的条目。然后以WHERE条件过滤扩展表，最后把结果输出。

以user、role、user_to_role这个多对多关联为例。如果把user表作为左侧的tb1，那么扩展表中每一条对应的是一个user，而且在user表中user是独特的unique，非重复的，所以每个user都不同。但是这个多对多关联中一个user要对应多个role，如果最后的扩展表每一条对应一个user，那么也就只能对应最多一个role，这明显是不符合预期的。

所以处理多对多关联的时候，应该以中间关系表作为LEFT JOIN的左表。这样扩展表的每一条就是一个user和role的对应关系，然后以WHERE过滤就可以得到一个user的全部role、或者一个role的全部user、或者其他的需求。比如下面这条语句就可以查到一个user到底有哪几条对应的user_to_role关系。

SELECT user.id AS id, user.name AS user_name, user_to_role.id AS relationship_id
FROM user_to_role LEFT JOIN user ON user.id=user_to_role.user_id
WHERE user.name='test'

这样形成的扩展表本质上也是一个“表”，只要把这个扩展表与role表再做一个JOIN就可以查出对应的role。因为这个新表曾经被user过滤过，所以新表中每一条目的role应该是不同的，此时就不需要在意JOIN的顺序了。

SELECT user.id AS id, user.name AS user_name, role.name AS role_name
FROM (user_to_role LEFT JOIN user ON user.id=user_to_role.user_id)
    LEFT JOIN role ON user_to_role.role_id=role.id
WHERE user.name='test'

其实更加复杂的user->user_to_role->role->role_to_action_group->action_group->action_to_action_group->action查询链条类似，在进行LEFT JOIN时，一定要注意把可能含有多个重复条目的表放在LEFT JOIN的左侧，然后把不含有重复条目的放在LEFT JOIN的右侧。JOIN之后形成的扩展表也视为一个表，这个新的表也可以继续JOIN，JOIN时一样注意JOIN的顺序即可。对于现代SQL，JOIN的顺序是可以通过括号来控制的。所以这个长链条的最后的查询语句如下：

SELECT public.user.id, public.user.name, public.action.url, public.action.name
FROM (public.action_to_action_group LEFT JOIN ((public.role_to_action_group LEFT JOIN 
	  ((public.user_to_role LEFT JOIN public.user ON public.user_to_role.user_id=public.user.id)
	  LEFT JOIN public.role ON public.user_to_role.role_id=public.role.id)
	  ON public.role_to_action_group.role_id=public.role.id)
	  LEFT JOIN public.action_group ON public.role_to_action_group.action_group_id=public.action_group.id)
	  ON public.action_to_action_group.action_group_id=public.action_group.id)
	  LEFT JOIN public.action ON public.action.id=public.action_to_action_group.action_id
WHERE public.user.name='test'

因为这里用的是PostgreSQL，所以所有的表名前面要加上schema名，这里用的是public。如果是MySQL，不用加schema名