四种常用联接

create table a(id int,name varchar(20));
insert into a values(1,'a');
insert into a values(2,'a');

create table b(id int,score int);
insert into b values(1,90);
insert into b values(3,80);

inner join :内连接，主要是看两个表中用于连接的字段是否相等

select a.id,a.name,b.id,b.score from a inner join b on a.id=b.id

结果:
id name id score
----------- -------------------- ----------- -----------
1 a 1 90

left join:做连接，做进行联接时，左边表中用于联接的字段的值要完全保存下来，右边的表中若不存在相等的值则结果为null

select a.id,a.name,b.id,b.score from a left join b on a.id=b.id

结果:
id name id score
----------- -------------------- ----------- -----------
1 a 1 90
2 a NULL NULL

right join:与左联接相对，只需左改成右就行了

结果:
select a.id,a.name,b.id,b.score from a right join b on a.id=b.id
id name id score
----------- -------------------- ----------- -----------
1 a 1 90
NULL NULL 3 80

full join:可以简单的看成是左联接和右联接的并集，即左边和右边中用于联接的字段所有值都需保存下来，两边不存在匹配字段的则为null

select a.id,a.name,b.id,b.score from a full join b on a.id=b.id

结果:

id name id score
----------- -------------------- ----------- -----------
1 a 1 90
2 a NULL NULL
NULL NULL 3 80