sql server 多表查询 内、外、全 连接

--多表查询
select * from Department
select * from People
--简单多条查询
--查询员工信息,显示部门名称
select * from People,Department where People.DepartmentId=Department.DepartmentId
--查询员工信息显示职级名称
select * from People,Rank where People.RankId=Rank.RankId
--查询员工信息，显示部门名称，显示职级名称
select * from People,Department,Rank where People.DepartmentId=Department.DepartmentId and People.RankId=Rank.RankId



--内连接查询
--查询员工信息，显示部门名称
select * from People inner join Department on Department.DepartmentId=People.DepartmentId

--查询员工信息，显示职级名称
select * from People inner join Rank on People.RankId=Rank.RankId


--查询员工信息，显示部门名称，显示职级名称
select * from People inner join Department on    Department.DepartmentId=People.DepartmentId 
inner join Rank on People.RankId=Rank.RankId


--简单多表查询和内连接共同特点：
--不符合主外键关系的数据不会被显示出来

--将People表中的外键约束删除插入数据
insert into People([DepartmentId],[RankId],[PeopleName],[PeopleSex],[PeopleBirth],[PeopleSalary],[PeoplePhone],[PeopleAddress],[PeopleAddTime])
values(69,84,'德华','男','1985-9-1',10000,'6556446464','西安',GETDATE())




--外连接(左外连接，右外连接，全连接)
--左外连接 ：以左表为主进行显示 主外键关系找不到的数据null取代

--查询员工信息 显示员工名称
select * from People Left join Department on Department.DepartmentId=People.DepartmentId
--可以显示德华信息
select * from Department Left join People on Department.DepartmentId=People.DepartmentId
--显示所有部门信息


--查询员工信息，显示职级名称
select * from People left join Rank on People.RankId=Rank.RankId


--查询员工信息，显示部门名称，显示职级名称
select * from People left join Department on    Department.DepartmentId=People.DepartmentId 
left join Rank on People.RankId=Rank.RankId


--右连:A left join B == B right join A
--下面两个查询含义相同
select * from People left join Department on Department.DepartmentId=People.DepartmentId

select * from Department right join People  on Department.DepartmentId=People.DepartmentId


--全外连：两个表的数据，无论是否符合关系，都要显示
select * from People full join Department on Department.DepartmentId=People.DepartmentId