多表查询-1

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 People.DepartmentId = Department.DepartmentId
inner join [Rank] on People.RankId = [Rank].RankId
--左外连
select * from People --以左表为主 people表的都会显示
left join Department on People.DepartmentId = Department.DepartmentId

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

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

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

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