SQL JOIN 五种查询

 use boris
 IF OBJECT_ID('Student') IS NOT NULL
DROP TABLE Student
CREATE TABLE  Student
(Sname nvarchar(50) null, Ssex nvarchar(50) null,Sage int null)
insert into Student
select '张三','男','14' union all
select '李四','女','15'union all
select '王五','男','16'
--select * from Student
 if OBJECT_ID('S_otherInfo') IS NOT NULL
 DROP TABLE S_otherInfo
 create table S_otherInfo(Sname nvarchar(50) not null,Adress nvarchar(50) null,Othername nvarchar(50) null)
 insert into S_otherInfo
 SELECT '赵六','天津','六' union all
 select '张三','上海','三' union all
 select '王五','海南','五'
--select * from S_otherInfo
--只显示符合条件的记录
select * FROM Student S inner JOIN S_otherInfo ON  S.Sname=S_otherInfo.Sname
--显示左边表所有的记录，以及右边表中符合条件的记录
SELECT * FROM Student S LEFT JOIN  S_otherInfo ON  S.Sname=S_otherInfo.Sname
--显示右边表所有的记录，以及左边表中符合条件的记录
SELECT * FROM Student S RIGHT JOIN  S_otherInfo ON  S.Sname=S_otherInfo.Sname
--显示所有表的记录，包括符合条件的记录和不符合条件的记录
SELECT * FROM Student S FULL JOIN S_otherInfo ON  S.Sname=S_otherInfo.Sname
--将一个表的每一条记录和另一个表中的每一条记录搭配成新的记录,不需要用ON来设置条件
SELECT * FROM Student S CROSS JOIN  S_otherInfo ORDER BY s.Sname