SQL Server Join连接

常用有INNER JOIN，LEFT JOIN，RIGHT JOIN， FULL JOIN。

学生表

成绩表

INNER JOIN (取T_B_Student表和T_B_Achievement表的交集)

select 
S.NO,S.NAME,S.SEX,S.CLASS,
A.Chinese,A.Math,A.English,A.History,A.Geography,A.Biology,A.Politics,A.Physics,A.Chemistry 
from T_B_Student S inner join T_B_Achievement A on S.NO= A.SNO order by S.NO

查询结果如下:

FULL JOIN (取T_B_Student表和T_B_Achievement表的并集)

select 
S.NO,S.NAME,S.SEX,S.CLASS,
A.Chinese,A.Math,A.English,A.History,A.Geography,A.Biology,A.Politics,A.Physics,A.Chemistry 
from T_B_Student S full join T_B_Achievement A on S.NO= A.SNO order by S.NO

查询结果如下:

LEFT JOIN (以T_B_Student表为准，T_B_Achievement表与之匹配)

select 
S.NO,S.NAME,S.SEX,S.CLASS,
A.Chinese,A.Math,A.English,A.History,A.Geography,A.Biology,A.Politics,A.Physics,A.Chemistry 
from T_B_Student S left join T_B_Achievement A on S.NO= A.SNO order by S.NO

查询结果如下:

RIGHT JOIN (以T_B_Achievement表为准，T_B_Student表与之匹配)

select 
S.NO,S.NAME,S.SEX,S.CLASS,
A.Chinese,A.Math,A.English,A.History,A.Geography,A.Biology,A.Politics,A.Physics,A.Chemistry 
from T_B_Student S right join T_B_Achievement A on S.NO= A.SNO order by S.NO

查询结果如下:

结束！