SQL SERVER多表连接查询

--查询员工及所属部门的信息
select * from people,department where people.departmentid=department.departmentid;

--查询员工及所属职级的详细信息
select * from people,[Rank] where people.rankid=[Rank].rankid;

--查询员工职级、部门详细信息
select * from people,[rank],department where
people.departmentid=department.departmentid
and 
people.rankid=[Rank].rankid;


--inner join ...on 条件
select * from people inner join department on people.departmentid=department.departmentid;

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

--外连接（左连接、右连接、全连接）
  --左连接：以左表为主表进行数据的显示，主外键关系找不到的数据null取代
    select *from people LEFT join Department on  people.DepartmentId=Department.DepartmentID;
      select *from department LEFT join people on  people.DepartmentId=Department.DepartmentID;
  --右连接：以右表为主表进行数据的显示，主外键关系找不到的数据null取代 ，A left join B=B right join A
      select *from Department right join people on  people.DepartmentId=Department.DepartmentID;
  --全外连，两张表的数据，无论是否符合关系，都需要显示
     select * from people FULL join Department on  people.DepartmentId=Department.DepartmentID;