mysql-如果没有匹配项,则使用默认值进行LEFT JOIN

我有2个表-一个包含一个站点列表,另一个包含一个角色列表.一个用户可能只具有一个角色,但是他们可能具有与站点一样多的角色.

表“站点”

site_id  |  site_name  | site_domain
---------------------------------------
1        |  Site A     | sitea.com
2        |  Site B     | siteb.com

表“角色”

site_id  |  user_id  | role_id
---------------------------------
1        |  1        | 1
1        |  2        | 1
2        |  2        | 2

如上面的示例所示,用户1在一个站点上仅具有一个角色,但是用户2在两个不同站点上具有两个角色.

我想做的,可能只是一个星期天晚上,没有想到直接的事情,是要列出网站列表,以及每个用户的角色-如果该用户对该网站不存在任何角色,然后返回NULL(或0).

输出结果如何的两个示例：

用户1

site_id | site_name | site_domain | role_id
----------------------------------------------
1       | Site A    | sitea.com   | 1
2       | Site B    | siteb.com   | NULL

用户2

site_id | site_name | site_domain | role_id
----------------------------------------------
1       | Site A    | sitea.com   | 1
2       | Site B    | siteb.com   | 2

我目前的查询在下面,但是,第二个站点没有出现,因为用户没有定义角色(即,如上,但对于用户1,第二个站点B不会列出).我知道我只是想念一些东西,但今晚想不起来：o(

SELECT site_id, site_name, site_domain, role_id 
FROM `sites` 
LEFT JOIN `roles` 
ON sites.site_id=roles.site_id 
WHERE roles.user_id='1'

select s.site_id, s.site_name, s.site_domain, u.user_id, r.role_id
from (select distinct user_id from roles) u cross join
     sites s left outer join
     roles r
     on s.site_id = r.site_id and
        u.user_id = r.user_id

如果您有一个用户表,则可以用该表替换第一个子查询.

对于涉及单个用户的网站,您可以在子查询中放置一个常量：

select s.site_id, s.site_name, s.site_domain, u.user_id, r.role_id
from (select 1 as user_id) u cross join
     sites s left outer join
     roles r
     on s.site_id = r.site_id and
        u.user_id = r.user_id

或where子句：其中u.user_id = 1.