SQL Server 2012连接查询

create database test;
use test;

create table student(
sid int not null primary key,
sname varchar(20) not null,
ssex char(2) not null
)

create table borrowbook(
bid int not null primary key,
bname varchar(20) not null,
sid int not null,
foreign key(sid) references student(sid)
)

insert into student values(1,‘张三’,‘男’);
insert into student values(2,‘李四’,‘男’);
insert into student values(3,‘王五’,‘女’);
insert into student values(4,‘李六’,‘男’);
insert into student values(5,‘田七’,‘女’);

insert into borrowbook values(1,‘高数’,1)
insert into borrowbook values(2,‘高数’,1)
insert into borrowbook values(3,‘高数’,2)
insert into borrowbook values(4,‘高数’,2)
insert into borrowbook values(5,‘高数’,5)

select * from student
select * from borrowbook

–1、内连接（他可以 省略inner）
select student.,borrowbook. from student
inner join borrowbook on student.sid=borrowbook.sid

select student.,borrowbook. from student
join borrowbook on student.sid=borrowbook.sid

–2、外连接（左外连接，右外连接，全外连接）
–2.1左连接（left join 或 left outer join）
–左连接又称左向外连接，查询的结果集包括SQL语句中左表的所有行，
–右表中匹配的行。如果左表的某行在右表中没有匹配行，则用空值表示。

select * from student left join borrowbook on student.sid=borrowbook.sid;
select * from borrowbook left join student on student.sid=borrowbook.sid;

–2.2右连接（right join 或 right outer join)
– 右连接也成右向外连接(是左向外联接的反向联接)，查询的结果集包括SQL语句中右表的所有行，左表中匹配的行。
–如果右表的某行在左表中没有匹配的行，则用空值表示

select * from student right join borrowbook on student.sid=borrowbook.sid
select * from borrowbook right join student on student.sid=borrowbook.sid

–2.3完全外连接（full join 或 full outer join）
– 完全外连接，查询的结果集包括SQL语句中左表和右表的所有行。
–如果某行在另一个表中没有匹配行时，则用空值表示。
select * from student full join borrowbook on student.sid=borrowbook.sid
select * from borrowbook full join student on student.sid=borrowbook.sid