SQL SERVER LEFT JOIN, INNER JOIN, RIGHT JOIN

標籤：value   into   table   blog   varchar   ble   png   系統   img   

• JOIN: 如果表中有至少一個匹配，則返回行
• LEFT JOIN: 即使右表中沒有匹配，也從左表返回所有的行
• RIGHT JOIN: 即使左表中沒有匹配，也從右表返回所有的行
• FULL JOIN: 只要其中一個表中存在匹配，就返回行

CREATE TABLE dbo.Student( Sno int  null,Name nvarchar(23) )CREATE TABLE dbo.Score( Sno int ,Score INT)INSERT INTO dbo.Student(Sno,Name)VALUES(1,‘Jesse‘);INSERT INTO dbo.Student(Sno,Name)VALUES(2,‘Jessca‘);INSERT INTO dbo.Student(Sno,Name)VALUES(3,‘June‘);INSERT INTO dbo.Student(Sno,Name)VALUES(4,‘Supper‘);INSERT INTO dbo.Score(Sno,Score)VALUES (1,45)INSERT INTO dbo.Score(Sno,Score)VALUES (1,56)INSERT INTO dbo.Score(Sno,Score)VALUES (2,100)INSERT INTO dbo.Score(Sno,Score)VALUES (3,25)  

　查看資料：

SELECT * FROM dbo.StudentSELECT * FROM dbo.Score

　　

Left Join 會顯示出左邊的全部資料，右邊如果沒有對應的值，系統預設NULL

SELECT *FROM dbo.Student aLEFT JOIN dbo.Score bON a.Sno=b.Sno

　　

INNER JOIN 顯示交集

SELECT *FROM dbo.Student aINNER JOIN dbo.Score bON a.Sno=b.Sno

　　

right join 右表全部顯示，左表沒有，預設設定為NULL

SELECT *FROM dbo.Student aRIGHT JOIN dbo.Score bON a.Sno=b.Sno

　　

FULL JOIN 左右邊都顯示，如果對方沒有值得統一設定NULL

SELECT *FROM dbo.Student aFULL JOIN dbo.Score bON a.Sno=b.Sno

　　

 

SQL SERVER LEFT JOIN, INNER JOIN, RIGHT JOIN