SQL SERVER实现连接与合并查询

CREATE table MyStudentInfo
(
Id int not null primary key,
Name varchar(16),
Age int,
Gender varchar(2),
Phone varchar(16),
Address varchar(50),
GradeId int
)

INSERT INTO MyStudentInfo
SELECT 1,'张三',20,'1','15801258912','上海',1 UNION
SELECT 2,'李四',22,'1','12345678901','北京',1 UNION
SELECT 3,'王五',16,'1','13976891234','天津',2 UNION
SELECT 4,'赵六',19,'1','18676891234','重庆',3 UNION
SELECT 5,'小红',21,'2','17776891234','广州',4 UNION
SELECT 6,'小王',25,'2','13176891234','深圳',5 UNION
SELECT 7,'小刘',18,'2','13374591234','南京',6 UNION
SELECT 8,'小张',16,'1','13974596734','长沙',6 UNION
SELECT 9,'小罗',27,'1','13175122786','武汉',7 UNION
SELECT 10,'小袁',21,'2','17715872346','石家庄',8

CREATE table GradeInfo
(
Id int not null primary key,
GradeName varchar(16)
)

INSERT INTO GradeInfo
SELECT 1,'.NET' UNION
SELECT 2,'Android' UNION
SELECT 3,'PHP' UNION
SELECT 4,'UI' UNION
SELECT 5,'HTML5' UNION
SELECT 6,'JAVA' UNION
SELECT 9,'HADOOP' UNION
SELECT 10,'大数据'

SELECT s.Id,s.Name,s.Age,s.Gender,s.Phone,s.GradeId,g.GradeName
FROM MyStudentInfo s INNER JOIN GradeInfo g ON s.GradeId=g.Id

SELECT s.Id,s.Name,s.Age,s.Gender,s.Phone,s.GradeId,g.GradeName
FROM MyStudentInfo s LEFT JOIN GradeInfo g ON s.GradeId=g.Id

SELECT s.Id,s.Name,s.Age,s.Gender,s.Phone,s.GradeId,g.GradeName
FROM MyStudentInfo s RIGHT JOIN GradeInfo g ON s.GradeId=g.Id

SELECT s.Id,s.Name,s.Age,s.Gender,s.Phone,s.GradeId,g.GradeName
FROM MyStudentInfo s FULL JOIN GradeInfo g ON s.GradeId=g.Id

SELECT s.Id,s.Name,s.Age,s.Gender,s.Phone,s.GradeId,g.GradeName
FROM MyStudentInfo s CROSS JOIN GradeInfo g

SELECT Age,Name FROM MyStudentInfo
UNION
SELECT Id,GradeName FROM GradeInfo
ORDER BY Age DESC

SELECT Age,Name,Address FROM MyStudentInfo
UNION ALL
SELECT Id,GradeName,NULL FROM GradeInfo
ORDER BY Age DESC