-- 交叉連接產生笛卡爾值 (X*Y) SELECT * FROM Student cross Join dbo.ClassInfo --另外一種寫法 SELECT * FROM Student , ClassInfo -- 內連接 (Inner 可以省略) SELECT * FROM Studen... ...
-- 交叉連接產生笛卡爾值 (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.ID AND 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 T1 INNER JOIN dbo.ClassInfo AS T2 ON T1.PID=T2.ID -- Left Join SELECT * FROM Student Left JOIN dbo.ClassInfo ON dbo.Student.Class = dbo.ClassInfo.ID; -- Right Join SELECT * 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,StuBirthday FROM Student WHERE StuID<=2 UNION SELECT StuID,StuName,StuEnName,StuAge,StuBirthday FROM dbo.Student WHERE StuID>2 --Union 去重覆 SELECT StuSex FROM Student WHERE StuID<=2 UNION SELECT StuSex FROM dbo.Student WHERE StuID>2 --Union 顯示全部 SELECT StuSex FROM Student WHERE StuID<=2 UNION ALL SELECT StuSex FROM dbo.Student WHERE StuID>2 --Except 差集,排除 SELECT StuID,StuName,StuEnName,StuAge,StuBirthday FROM Student Except SELECT StuID,StuName,StuEnName,StuAge,StuBirthday FROM dbo.Student WHERE StuID<=2 --Intersect 交集 SELECT StuID,StuName,StuEnName,StuAge,StuBirthday FROM Student WHERE StuID>=2 Except SELECT StuID,StuName,StuEnName,StuAge,StuBirthday FROM dbo.Student WHERE StuID<=3