SQL Server 2016 表操作：多表关联查询

• INNER JOIN 交叉关联

-- 返回两个表连接字段相等的行
SELECT * FROM 表1 INNER JOIN 表2 ON 表1.字段=表2.字段;

• LEFT JOIN 左关联

-- 返回左表中所有记录和右表中连接字段相等的行
SELECT * FROM 表1 LEFT JOIN 表2 ON 表1.字段=表2.字段;

• RIGHT JOIN 右关联

-- 返回右表中所有记录和左表中连接字段相等的行
SELECT * FROM 表1 RIGHT JOIN 表2 ON 表1.字段=表2.字段;

-- 3 表连接查询
SELECT a.Name,b.Subject,c.Score FROM Student as a
INNER JOIN Score as c ON a.ID=c.StudentID
INNER JOIN Subject as b ON b.ID=c.SubjectID;

image.png image.png

SELECT * FROM Student a INNER JOIN Score b ON a.id=b.StudentID;
SELECT * FROM Student a LEFT JOIN Score b ON a.id=b.StudentID;
SELECT * FROM Student a RIGHT JOIN Score b ON a.id=b.StudentID;

image.png image.png