SQL Server 2012 - 多表串連查詢

標籤：pre   left join   stun   否則   blog   pid   --   sql   條件   

-- 交叉串連產生笛卡爾值 (X*Y)SELECT  * FROM    Student        cross Join dbo.ClassInfo  --另外一種寫法SELECT  * FROM    Student , ClassInfo  -- 內串連 (Inner 可以省略)SELECT  *FROM    Student        JOIN dbo.ClassInfo ON dbo.Student.Class = dbo.ClassInfo.ID;-- Inner Join SELECT  *FROM    Student        INNER  JOIN dbo.ClassInfo ON dbo.Student.Class = dbo.ClassInfo.ID;  -- on 條件，通常是 主外鍵，但是不限於主外鍵  -- on 條件，允許有多個，而且可能是針對某個表的SELECT  *FROM    Student        INNER  JOIN dbo.ClassInfo ON dbo.Student.Class = dbo.ClassInfo.IDAND dbo.Student.Class IN (1,2)  -- 針對Student表增加查詢條件-- 不等於 (笛卡爾值減去 相等的值)SELECT  *FROM    Student        INNER  JOIN dbo.ClassInfo ON dbo.Student.Class <> dbo.ClassInfo.ID;--自串連  從Class表中查詢到Class所在的系SELECT T1.* ,T2.ClassName FROM dbo.ClassInfo AS T1INNER JOIN dbo.ClassInfo AS T2 ON T1.PID=T2.ID-- Left JoinSELECT  *FROM    Student        Left  JOIN dbo.ClassInfo ON dbo.Student.Class = dbo.ClassInfo.ID;-- Right JoinSELECT  *FROM    Student        RIGHT   JOIN dbo.ClassInfo ON dbo.Student.Class = dbo.ClassInfo.ID;-- 全外串連SELECT  *FROM    Student        FULL OUTER  JOIN dbo.ClassInfo ON dbo.Student.Class = dbo.ClassInfo.ID;-- 外串連和內串連的區別是：-- 內串連，on條件不符合的話，會過濾掉資料-- 外串連，以保留表為主，on條件成立顯示資料，否則顯示NULL--Union 縱向串連SELECT  StuID,StuName,StuEnName,StuAge,StuBirthdayFROM    Student WHERE StuID<=2UNIONSELECT StuID,StuName,StuEnName,StuAge,StuBirthdayFROM dbo.Student WHERE StuID>2--Union 去重複SELECT  StuSexFROM    Student WHERE StuID<=2UNIONSELECT StuSexFROM dbo.Student WHERE StuID>2--Union 顯示全部SELECT  StuSexFROM    Student WHERE StuID<=2UNION ALLSELECT StuSexFROM dbo.Student WHERE StuID>2--Except 差集,排除SELECT  StuID,StuName,StuEnName,StuAge,StuBirthdayFROM    Student ExceptSELECT StuID,StuName,StuEnName,StuAge,StuBirthdayFROM dbo.Student WHERE StuID<=2--Intersect 交集SELECT  StuID,StuName,StuEnName,StuAge,StuBirthdayFROM    Student  WHERE StuID>=2ExceptSELECT StuID,StuName,StuEnName,StuAge,StuBirthdayFROM dbo.Student WHERE StuID<=3

　　

SQL Server 2012 - 多表串連查詢