数据库连接查询,使用的是mysql数据库
-- 左连接
SELECT DISTINCT u.user_id,u.user_name,r.role_id,r.role_name
FROM user_tb u
LEFT JOIN user_role_rel ur ON u.user_id=ur.user_id
left join role_tb r on u.role_id=r.role_id
where u.user_name='name';
-- 内连接
SELECT DISTINCT u.user_id,u.user_name,r.role_id,r.role_name
FROM user_tb u,user_role_rel ur,role_tb r
where
u.user_id=ur.user_id
and r.role_id=ur.role_id
and u.user_name='name';
-- 括号连接
SELECT DISTINCT a.user_id,a.user_name,r.role_id,r.role_name
FROM (SELECT u.user_id,u.user_name,ur.role_id FROM user_tb u,user_role_rel ur where u.user_id=ur.user_id) a ,role_tb r
where a.role_id=r.role_id and a.user_name='name';
-- 内连接
SELECT DISTINCT u.user_id,u.user_name,r.role_id,r.role_name
FROM user_tb u INNER JOIN user_role_rel ur ON u.user_id=ur.user_id INNER JOIN role_tb r ON ur.role_id=r.role_id
where u.user_name='name';
内连接可参考