外连接:(左连接 + 右连接)
内连接: 内连接
比如有person(人员)和job(工作)两张表,分别代表
person:
job:
比如现在需要查询每个人的对应的职业:
左连接
select
person.id as personId,
person.name as personName,
job.name as jobName
from person person
left join job job on person.id = job.person_id;
可以说明,左连接查询时,左边表(person)的数据会全部查询显示出来,其他副表(job)没查询到的信息就是null显示。
右连接
右连接就是以右边的job表为基准,连到左边的person表:
select
person.id as personId,
person.name as personName,
job.id as jobId,
job.name as jobName
from person person
right join job job on person.id = job.person_id
这样会得到多少条记录呢?
因为右边的job表是两条数据,因此一共是两条:
内连接
内连接又叫等值连接(inner join),这个比较简单,就是必须是两张表的数据完全匹配才返回。
select
person.id as personId,
person.name as personName,
job.id as jobId,
job.name as jobName
from person person
inner join job job on person.id = job.person_id;