【T_SQL】基础续++

十三、多表联接查询
1、分类
(1)内联接(INNER JOIN)
(2)外联接
A、左外联结 (LEFT JOIN)
B、右外联结 (RIGHT JOIN)
C、完整外联结 (FULL JOIN)
(3)交叉联接(CROSS JOIN)

2、多表内联结查询
(1)建立联接
SELECT S.SName,C.CourseID,C.Score  
From Score AS C 
INNER JOIN Students AS S 
ON C.StudentID = S.SCode 
--显示结果：把 Score 表和 Students 表建立内联结,查询 C.StudentID = S.SCode 时,显显示S.SName,C.CourseID,C.Score 的内容。 
(2)未建立联接
SELECT Students.SName, Score.CourseID, Score.Score 
FROM Students,Score 
WHERE Students.SCode = Score.StudentID<pre name="code" class="sql">--显示结果：把 Score 表和 Students 表建立内联结,查询 C.StudentID = S.SCode 时,显显示S.SName,C.CourseID,C.Score 的内容。
(3)多表联接查询—三表联接
SELECT  S.SName AS 姓名, CS.CourseName AS 课程, C.Score AS 成绩 
FROM  Students AS S  
INNER JOIN  Score AS C  
ON (S.SCode = C.StudentID) 
INNER JOIN  Course AS CS  
ON (CS.CourseID = C.CourseID) 
(4)区别
建立联接的查询速度比没有建立的快得多。

3、多表外联接查询
(1)左外联接 (LEFT JOIN 或 LEFT OUTER JOIN)
<span style="font-family: Arial, Helvetica, sans-serif;">(LEFT JOIN 或 LEFT OUTER JOIN) </span>
SELECT S.SName,C.CourseID,C.Score  
From Students AS S 
LEFT JOIN Score AS C 
ON C.StudentID = S.SCode 
--显示结果：Students 为左表(left join)Score 为右表。左表中有的,右表中没有的显示空值(NULL)。 
(2)右外联接 (RIGHT JOIN 或 RIGHT OUTER JOIN)
SELECT Titles.Title_id, Titles.Title, Publishers.Pub_name 
FROM titles  
RIGHT OUTER JOIN  Publishers  
ON Titles.Pub_id = Publishers.Pub_id 
--显示结果：<span style="font-family: Arial, Helvetica, sans-serif;">Students 为右表(right join)Score 为左表。右表中有的,左表中没有的显示空值(NULL)。</span>
 
(3)完整外联接 (FULL JOIN 或 FULL OUTER JOIN)
SELECT Titles.Title_id, Titles.Title, Publishers.Pub_name 
FROM titles  
RIGHT OUTER JOIN  Publishers  
ON Titles.Pub_id = Publishers.Pub_id 
显示结果：左表和右表中的所有行。当某行在另一个表中没有匹配行时,则另一个表的
选择列为空值；如果有匹配行,则显示结果包括左右表中的所有列值。

4、多表交叉联接查询 (CROSS JOIN)
交叉联接返回左表中的所有行,左表中的每一行与右表中的所有行一一组合,相当于两
个表“相乘”。类似于我们学过的做笛卡儿积

摘自：http://vdisk.weibo.com/s/d0C3jikrGIj1N

【T_SQL】 基础 续++

【T_SQL】基础续++