✅ mysql 所有连表查询（join）写法（小写）
1. inner join（内连接：两张表都匹配的才返回）
select *
from table_a as a
inner join table_b as b on a.id = b.a_id;
2. left join（左连接：左表全部 + 匹配的右表）
select *
from table_a as a
left join table_b as b on a.id = b.a_id;
3. right join（右连接：右表全部 + 匹配的左表）
select *
from table_a as a
right join table_b as b on a.id = b.a_id;
4. full join（mysql 没有 full join，需要使用 union）
select *
from table_a as a
left join table_b as b on a.id = b.a_id
union
select *
from table_a as a
right join table_b as b on a.id = b.a_id;
5. cross join（交叉连接：笛卡尔积）
select *
from table_a
cross join table_b;
6. self join（自连接）
select a.*, b.*
from users as a
left join users as b on a.parent_id = b.id;
🔥 常用连表查询模板（小写）
多表连接
select *
from user as u
inner join users_roles as ur on u.id = ur.user_id
inner join roles as r on ur.role_id = r.id;
带条件的 join
select *
from orders as o
left join users as u on o.user_id = u.id
where u.status = 1;
统计 + join
select u.id, u.username, count(o.id) as order_count
from users as u
left join orders as o on u.id = o.user_id
group by u.id;
