使用連接《SQLServer程序設計語言》

1、本部分主要所用的表結構要所用的表結構SQL Server 2005 數(shù)據(jù)庫程序設計語言數(shù)據(jù)庫程序設計語言第第8章章 數(shù)據(jù)操作數(shù)據(jù)操作8.2 使用連接使用連接1、使用表的別名、使用表的別名2、命名列、命名列3、組合多個表中的數(shù)據(jù)、組合多個表中的數(shù)據(jù)4、推薦操作、推薦操作 在查詢中，同時涉及兩個或兩在查詢中，同時涉及兩個或兩個以上的表，要根據(jù)表中數(shù)據(jù)的情個以上的表，要根據(jù)表中數(shù)據(jù)的情況作連接。況作連接。 SELECT buyer_name ,qty FROM sales cross join buyers一個示例：一個示例：例例1：本例列出：本例列出buyers. buyer_name和和sale

2、s. qty列中所有可能的值的組合列中所有可能的值的組合 SELECT buyer_name ,qty FROM sales,buyers查詢查詢結果結果buyers表表sales表表連接操作的執(zhí)行過程連接操作的執(zhí)行過程首先在表首先在表1中找到第一個元組，然后從頭開始掃描中找到第一個元組，然后從頭開始掃描表表2，逐一查找滿足條件的元組，找到后就將表，逐一查找滿足條件的元組，找到后就將表1中的第一個元組與該元組拼接起來，形成結果表中的第一個元組與該元組拼接起來，形成結果表中一個元組。中一個元組。表表2全部查找完后，再找表全部查找完后，再找表1中第二個元組，然后中第二個元組，然后再從頭開始掃描表再

3、從頭開始掃描表2，逐一查找滿足條件的元組，逐一查找滿足條件的元組，找到后就將表找到后就將表1中的第二個元組與該元組拼接起來，中的第二個元組與該元組拼接起來，形成結果表中一個元組。形成結果表中一個元組。重復上述操作，直到表重復上述操作，直到表1中的全部元組都處理完畢中的全部元組都處理完畢 8.2 使用連接使用連接1、使用表的別名、使用表的別名2、命名列、命名列3、組合多個表中的數(shù)據(jù)、組合多個表中的數(shù)據(jù)4、推薦操作、推薦操作使用表的別名使用表的別名指派表的別名時，可以使用也可以不使用指派表的別名時，可以使用也可以不使用 AS 關鍵字關鍵字： table_name AS table_aliastab

4、le_name table_alias 如果為表指派了別名，那么在該如果為表指派了別名，那么在該 Transact-SQL 語句中語句中對該表的所有顯式引用都必須使用別名，而不能使對該表的所有顯式引用都必須使用別名，而不能使用表名。用表名。有時復雜的有時復雜的 JOIN 語句和子查詢必須使用表的別名。語句和子查詢必須使用表的別名。 例如，聯(lián)接一個表和它自身時，必須使用別名例如，聯(lián)接一個表和它自身時，必須使用別名 使用表的別名使用表的別名在下例中，為在下例中，為 publishers 指派了別名指派了別名 p。USE pubsSELECT p.pub_id, p.pub_nameFROM pub

5、lishers AS p下列下列 SELECT 語句將產(chǎn)生語法錯誤，請問為什么？：語句將產(chǎn)生語法錯誤，請問為什么？： SELECT Employees.EmployeeID,emp.FirstName, emp.LastName FROM Northwind.dbo.Employees AS emp8.2 使用連接使用連接1、使用表的別名、使用表的別名2、命名列、命名列3、組合多個表中的數(shù)據(jù)、組合多個表中的數(shù)據(jù)4、推薦操作、推薦操作命名列命名列在查詢中使用多表聯(lián)接時，必須注意列的命在查詢中使用多表聯(lián)接時，必須注意列的命名名任何數(shù)據(jù)庫對象的全名包括四個標識符：任何數(shù)據(jù)庫對象的全名包括四個標識符：

6、服務器名服務器名.數(shù)據(jù)庫名數(shù)據(jù)庫名.架構名稱架構名稱.對象名對象名 在多表聯(lián)接中，如果表擁有相同的字段名，在多表聯(lián)接中，如果表擁有相同的字段名，則在指定字段時，必須包含表名則在指定字段時，必須包含表名例如：例如：school.dbo.student.sno8.2 使用連接使用連接1、使用表的別名、使用表的別名2、命名列、命名列3、組合多個表中的數(shù)據(jù)、組合多個表中的數(shù)據(jù)4、推薦操作、推薦操作3、組合多個表中的數(shù)據(jù)、組合多個表中的數(shù)據(jù)聯(lián)接概述聯(lián)接概述使用內聯(lián)接使用內聯(lián)接使用外聯(lián)接使用外聯(lián)接使用交叉聯(lián)接使用交叉聯(lián)接聯(lián)接兩個以上的表聯(lián)接兩個以上的表自聯(lián)接自聯(lián)接 （Self-Join）聯(lián)接概述聯(lián)接概述從

7、多個表中選擇指定的字段從多個表中選擇指定的字段關鍵字關鍵字 JOIN :指定要聯(lián)接的表，以及這些表聯(lián)指定要聯(lián)接的表，以及這些表聯(lián)接的方式接的方式關鍵字關鍵字 ON: 指定聯(lián)接條件指定聯(lián)接條件部分語法：部分語法： := on | CROSS JOIN 聯(lián)接概述（續(xù)）聯(lián)接概述（續(xù)）Join_type的格式：的格式： INNER | LEFT|RIGHT|FULL OUTER JOINANSI JOIN 操作符操作符 INNER JOIN LEFT OUTER JOIN、RIGHT OUTER JOIN、FULL OUTER JOIN、 GROSS JOIN查詢兩個或多個表并生成單個結果集查詢兩個或

8、多個表并生成單個結果集所有聯(lián)接的表必須所有聯(lián)接的表必須共同擁有共同擁有某些字段，這些字某些字段，這些字段必須有段必須有相同的或兼容相同的或兼容的數(shù)據(jù)類型的數(shù)據(jù)類型如果聯(lián)接的表有相同字段，則引用這些字段時如果聯(lián)接的表有相同字段，則引用這些字段時必須指定表名必須指定表名盡量在聯(lián)接中盡量在聯(lián)接中限制限制表的表的個數(shù)個數(shù)聯(lián)接概述（續(xù)）聯(lián)接概述（續(xù)）使用內聯(lián)接使用內聯(lián)接l內聯(lián)接通過比較被聯(lián)接的表所共同擁有的字段，內聯(lián)接通過比較被聯(lián)接的表所共同擁有的字段，把多個表聯(lián)接起來。把多個表聯(lián)接起來。將只返回滿足聯(lián)接條件的行將只返回滿足聯(lián)接條件的行。l為什么要使用內聯(lián)接為什么要使用內聯(lián)接l內聯(lián)接是內聯(lián)接是 SQL

9、Server 缺省的聯(lián)接方式缺省的聯(lián)接方式l在選擇列表中指定結果集要顯示的字段名在選擇列表中指定結果集要顯示的字段名l使用使用 WHERE 子句來限制結果集要返回的記錄子句來限制結果集要返回的記錄l在聯(lián)接條件中不要使用空值，因為空值與其他在聯(lián)接條件中不要使用空值，因為空值與其他值不會相等值不會相等使用內聯(lián)接（續(xù)）使用內聯(lián)接（續(xù)）SELECT buyer_name, sales.buyer_id, qtyFROM buyers INNER JOIN salesON buyers.buyer_id = sales.buyer_idGOsales114323151553711421003buyers

10、Adam BarrSean ChaiEva CoretsErin OMelia1234結果結果Adam BarrAdam BarrErin OMeliaEva Corets11431553711Erin OMelia41003示例示例列出購買產(chǎn)品的顧客信息列出購買產(chǎn)品的顧客信息buyer_name, sales.buyer_id, qty使用外聯(lián)接使用外聯(lián)接l左（右）外聯(lián)接可以從兩個表中返回符合聯(lián)接條左（右）外聯(lián)接可以從兩個表中返回符合聯(lián)接條件的記錄，同時也將返回左（右）邊不符合聯(lián)接件的記錄，同時也將返回左（右）邊不符合聯(lián)接條件的記錄條件的記錄l使用左（右）外聯(lián)接時，請注意使用左（右）外聯(lián)接時

11、，請注意l不滿足聯(lián)接條件的記錄將顯示空值不滿足聯(lián)接條件的記錄將顯示空值l左聯(lián)接可以顯示第一個表中所有記錄左聯(lián)接可以顯示第一個表中所有記錄l右聯(lián)接可以顯示第二個表中所有記錄右聯(lián)接可以顯示第二個表中所有記錄使用外聯(lián)接（續(xù)）使用外聯(lián)接（續(xù)）SELECT buyer_name, sales.buyer_id, qty FROM buyers LEFT OUTER JOIN sales ON buyers.buyer_id = sales.buyer_idGO示例示例sales114323151553711421003buyersAdam BarrSean ChaiEva CoretsErin OMel

12、ia1234結果結果Adam BarrAdam BarrSean ChaiEva Corets11NULL3155NULL11Erin OMelia437Erin OMelia41003列出購買產(chǎn)品的顧客信息列出購買產(chǎn)品的顧客信息buyer_name, sales.buyer_id, qty。結果集也將返回沒有購買任何產(chǎn)品的顧客結果集也將返回沒有購買任何產(chǎn)品的顧客使用交叉聯(lián)接使用交叉聯(lián)接l交叉聯(lián)接將從被聯(lián)接的表中返回所有可能的記錄交叉聯(lián)接將從被聯(lián)接的表中返回所有可能的記錄組合組合l為什么使用交叉聯(lián)接為什么使用交叉聯(lián)接 l在規(guī)范化的數(shù)據(jù)庫中，很少使用交叉聯(lián)接在規(guī)范化的數(shù)據(jù)庫中，很少使用交叉聯(lián)接l

13、可以為數(shù)據(jù)庫生成測試數(shù)據(jù)可以為數(shù)據(jù)庫生成測試數(shù)據(jù)l為清單及企業(yè)模板生成所有可能的組合數(shù)據(jù)為清單及企業(yè)模板生成所有可能的組合數(shù)據(jù)使用交叉聯(lián)接（續(xù)）使用交叉聯(lián)接（續(xù)）SELECT buyer_name, qty FROM buyers CROSS JOIN salesGO結果結果Adam BarrAdam BarrAdam BarrAdam Barr1553711 Adam Barr1003 Sean Chai15 Sean Chai5 Sean Chai37 Sean Chai11 Sean Chai1003 Eva Corets15 .sales114323151553711421003buy

14、ers1234Adam BarrSean ChaiEva CoretsErin OMelia示例示例列出列出buyers.buyer_name, sales. Qty的所有可能的值的組合的所有可能的值的組合聯(lián)接兩個以上的表聯(lián)接兩個以上的表l聯(lián)接任意數(shù)目的表都有可能聯(lián)接任意數(shù)目的表都有可能l通過使用共同擁有的字段，任何通過使用共同擁有的字段，任何一個表都可以和其他表聯(lián)接一個表都可以和其他表聯(lián)接聯(lián)接兩個以上的表（續(xù)）聯(lián)接兩個以上的表（續(xù)）SELECT buyer_name, prod_name, qty FROM buyers INNER JOIN sales ON buyers.buyer_id

15、 = sales.buyer_id INNER JOIN produce ON d_id = d_idproduce1234ApplesPearsOrangesBananas5Peachesbuyers1234Adam BarrSean ChaiEva CoretsErin OMeliasales114323154215537111003結果結果Erin OMeliaAdam BarrErin OMeliaAdam BarrEva CoretsApplesPearsPearsOrangesPeaches37151003511示例示例自聯(lián)接自聯(lián)接 （Sel

16、f-Join）l使用自聯(lián)接，可以使用自聯(lián)接，可以查詢一個表中各查詢一個表中各記錄之間的關系記錄之間的關系l使用自聯(lián)接時，應注意使用自聯(lián)接時，應注意 l引用表的兩份副本時，必須使用表的別名引用表的兩份副本時，必須使用表的別名l生成自聯(lián)接時，表中每一行都和自身比較一生成自聯(lián)接時，表中每一行都和自身比較一下，并生成重復的記錄，使用下，并生成重復的記錄，使用 WHERE WHERE 子句來子句來消除這些重復記錄消除這些重復記錄Sales 檢索出購買了相同產(chǎn)品的顧客檢索出購買了相同產(chǎn)品的顧客ID號以及其購買的號以及其購買的產(chǎn)品產(chǎn)品ID?Sales bSales aSELECT a.buyer_id AS

17、 buyer1, d_id ,b.buyer_id AS buyer2 FROM sales AS a JOIN sales AS b ON d_id = d_idsales b114323151553711421003sales a114323151553711421003SELECT a.buyer_id AS buyer1, d_id ,b.buyer_id AS buyer2 FROM sales AS a JOIN sales AS b ON d_id = d_idWHERE a.buyer_id b.buyer_idsal

18、es b114323151553711421003sales a114323151553711421003SELECT a.buyer_id AS buyer1, d_id ,b.buyer_id AS buyer2 FROM sales AS a JOIN sales AS b ON d_id = d_idWHERE a.buyer_id b.buyer_idsales b114323151553711421003sales a114323151553711421003421UNION 和和 JOIN 的區(qū)別的區(qū)別lJOIN 是合并多個表并生存是合并多個表并生存一個單獨的結果集一個單獨的結果集，該結果集將包含多個表中的字段該結果集將包含多