定义
- left join(左联接) 返回包括 左表中的所有记录和右表中联结字段相等的记录
- right join(右联接) 返回包括 右表中的所有记录和左表中联结字段相等的记录
- inner join(等值连接) 只返回两个表中联结字段相等的行
实践证明
表1 t_student
表2 t_s_course
left join
select a.id,a.`name` as sName,b.`name` as cName
from t_student a left join t_s_course b on a.id=b.uid
order by a.id
左表数据都返回,与右边表不匹配的返回null
right join
select a.id as uId,a.`name` as sName,b.id as cId,b.`name` as cName from t_s_course b right join t_student a on a.id=b.uid
order by b.id
inner join
select a.id as uId,a.`name` as sName,b.id as cId,b.`name` as cName from t_s_course b inner join t_student a on a.id=b.uid
order by b.id
corss join
select a.id as uId,a.`name` as sName,b.id as cId,b.`name` as cName from t_s_course b cross join t_student a
即为笛卡尔积,左表与右表每个值进行连接。