有如下两张表:people和stu_peo 。表中的数据如下所示:
内连接
1、两个表之间逗号的作用相当于一个inner join。
select people.Id,people.Name,stu_peo.Id,stu_peo.Name
from people,stu_peo
where people.Name = stu_peo.Name
2、inner join
select people.Id,people.Name,stu_peo.Id,stu_peo.Name
from people inner join stu_peo on people.Name = stu_peo.Name
3、只写join默认是inner join
select people.Id,people.Name,stu_peo.Id,stu_peo.Name
from people join stu_peo on people.Name = stu_peo.Name
外连接
1、left join
select people.Id,people.Name,stu_peo.Id,stu_peo.Name
from people left join stu_peo on people.Name = stu_peo.Name
left join 是以左表(people)为基础,左表的记录会全部显示出来,而右表(stu_peo)只会显示符合on条件的记录,右表中不符合on条件的记录为显示为Null。
2、right join
select people.Id,people.Name,stu_peo.Id,stu_peo.Name
from people right join stu_peo on people.Name = stu_peo.Name
right join 是以表右表(stu_peo)为基础,右表的记录会全部显示出来,而左表(people)只会显示符合on条件的记录,左表中不符合on条件的记录为显示为Null。