SQL Server 2012 多表连接查询功能实例代码

2024-03-01 14:49:03 450

--交叉连接产生笛卡尔值(X*Y) SELECT* FROMStudent crossJoindbo.ClassInfo --另外一种写法 SELECT* FROMStudent,ClassInfo --内连接(Inner可以省略) SELECT* FROMStudent JOINdbo.ClassInfoONdbo.Student.Class=dbo.ClassInfo.ID; --InnerJoin SELECT* FROMStudent INNERJOINdbo.ClassInfoONdbo.Student.Class=dbo.ClassInfo.ID; --on条件，通常是主外键，但是不限于主外键 --on条件，允许有多个，而且可能是针对某个表的 SELECT* FROMStudent INNERJOINdbo.ClassInfoONdbo.Student.Class=dbo.ClassInfo.ID ANDdbo.Student.ClassIN(1,2)--针对Student表增加查询条件 --不等于(笛卡尔值减去相等的值) SELECT* FROMStudent INNERJOINdbo.ClassInfoONdbo.Student.Class<>dbo.ClassInfo.ID; --自连接从Class表中查询到Class所在的系 SELECTT1.*,T2.ClassNameFROMdbo.ClassInfoAST1 INNERJOINdbo.ClassInfoAST2ONT1.PID=T2.ID --LeftJoin SELECT* FROMStudent LeftJOINdbo.ClassInfoONdbo.Student.Class=dbo.ClassInfo.ID; --RightJoin SELECT* FROMStudent RIGHTJOINdbo.ClassInfoONdbo.Student.Class=dbo.ClassInfo.ID; --全外连接 SELECT* FROMStudent FULLOUTERJOINdbo.ClassInfoONdbo.Student.Class=dbo.ClassInfo.ID; --外连接和内连接的区别是： --内连接，on条件不符合的话，会过滤掉数据 --外连接，以保留表为主，on条件成立显示数据，否则显示NULL --Union纵向连接 SELECTStuID,StuName,StuEnName,StuAge,StuBirthday FROMStudentWHEREStuID<=2 UNION SELECTStuID,StuName,StuEnName,StuAge,StuBirthday FROMdbo.StudentWHEREStuID>2 --Union去重复 SELECTStuSex FROMStudentWHEREStuID<=2 UNION SELECTStuSex FROMdbo.StudentWHEREStuID>2 --Union显示全部 SELECTStuSex FROMStudentWHEREStuID<=2 UNIONALL SELECTStuSex FROMdbo.StudentWHEREStuID>2 --Except差集,排除 SELECTStuID,StuName,StuEnName,StuAge,StuBirthday FROMStudent Except SELECTStuID,StuName,StuEnName,StuAge,StuBirthday FROMdbo.StudentWHEREStuID<=2 --Intersect交集 SELECTStuID,StuName,StuEnName,StuAge,StuBirthday FROMStudentWHEREStuID>=2 Except SELECTStuID,StuName,StuEnName,StuAge,StuBirthday FROMdbo.StudentWHEREStuID<=3

SQL Server 2012 多表连接查询功能实例代码

热门推荐

随机推荐