使用连接《SQLServer程序设计语言》

1、本部分主要所用的表结构要所用的表结构SQL Server 2005 数据库程序设计语言数据库程序设计语言第第8章章 数据操作数据操作8.2 使用连接使用连接1、使用表的别名、使用表的别名2、命名列、命名列3、组合多个表中的数据、组合多个表中的数据4、推荐操作、推荐操作 在查询中，同时涉及两个或两在查询中，同时涉及两个或两个以上的表，要根据表中数据的情个以上的表，要根据表中数据的情况作连接。况作连接。 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表表连接操作的执行过程连接操作的执行过程首先在表首先在表1中找到第一个元组，然后从头开始扫描中找到第一个元组，然后从头开始扫描表表2，逐一查找满足条件的元组，找到后就将表，逐一查找满足条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中的第一个元组与该元组拼接起来，形成结果表中一个元组。中一个元组。表表2全部查找完后，再找表全部查找完后，再找表1中第二个元组，然后中第二个元组，然后再从头开始扫描表再

3、从头开始扫描表2，逐一查找满足条件的元组，逐一查找满足条件的元组，找到后就将表找到后就将表1中的第二个元组与该元组拼接起来，中的第二个元组与该元组拼接起来，形成结果表中一个元组。形成结果表中一个元组。重复上述操作，直到表重复上述操作，直到表1中的全部元组都处理完毕中的全部元组都处理完毕 8.2 使用连接使用连接1、使用表的别名、使用表的别名2、命名列、命名列3、组合多个表中的数据、组合多个表中的数据4、推荐操作、推荐操作使用表的别名使用表的别名指派表的别名时，可以使用也可以不使用指派表的别名时，可以使用也可以不使用 AS 关键字关键字： table_name AS table_aliastab

4、le_name table_alias 如果为表指派了别名，那么在该如果为表指派了别名，那么在该 Transact-SQL 语句中语句中对该表的所有显式引用都必须使用别名，而不能使对该表的所有显式引用都必须使用别名，而不能使用表名。用表名。有时复杂的有时复杂的 JOIN 语句和子查询必须使用表的别名。语句和子查询必须使用表的别名。 例如，联接一个表和它自身时，必须使用别名例如，联接一个表和它自身时，必须使用别名 使用表的别名使用表的别名在下例中，为在下例中，为 publishers 指派了别名指派了别名 p。USE pubsSELECT p.pub_id, p.pub_nameFROM pub

5、lishers AS p下列下列 SELECT 语句将产生语法错误，请问为什么？：语句将产生语法错误，请问为什么？： SELECT Employees.EmployeeID,emp.FirstName, emp.LastName FROM Northwind.dbo.Employees AS emp8.2 使用连接使用连接1、使用表的别名、使用表的别名2、命名列、命名列3、组合多个表中的数据、组合多个表中的数据4、推荐操作、推荐操作命名列命名列在查询中使用多表联接时，必须注意列的命在查询中使用多表联接时，必须注意列的命名名任何数据库对象的全名包括四个标识符：任何数据库对象的全名包括四个标识符：

6、服务器名服务器名.数据库名数据库名.架构名称架构名称.对象名对象名 在多表联接中，如果表拥有相同的字段名，在多表联接中，如果表拥有相同的字段名，则在指定字段时，必须包含表名则在指定字段时，必须包含表名例如：例如：school.dbo.student.sno8.2 使用连接使用连接1、使用表的别名、使用表的别名2、命名列、命名列3、组合多个表中的数据、组合多个表中的数据4、推荐操作、推荐操作3、组合多个表中的数据、组合多个表中的数据联接概述联接概述使用内联接使用内联接使用外联接使用外联接使用交叉联接使用交叉联接联接两个以上的表联接两个以上的表自联接自联接 （Self-Join）联接概述联接概述从

7、多个表中选择指定的字段从多个表中选择指定的字段关键字关键字 JOIN :指定要联接的表，以及这些表联指定要联接的表，以及这些表联接的方式接的方式关键字关键字 ON: 指定联接条件指定联接条件部分语法：部分语法： := on | CROSS JOIN 联接概述（续）联接概述（续）Join_type的格式：的格式： INNER | LEFT|RIGHT|FULL OUTER JOINANSI JOIN 操作符操作符 INNER JOIN LEFT OUTER JOIN、RIGHT OUTER JOIN、FULL OUTER JOIN、 GROSS JOIN查询两个或多个表并生成单个结果集查询两个或

8、多个表并生成单个结果集所有联接的表必须所有联接的表必须共同拥有共同拥有某些字段，这些字某些字段，这些字段必须有段必须有相同的或兼容相同的或兼容的数据类型的数据类型如果联接的表有相同字段，则引用这些字段时如果联接的表有相同字段，则引用这些字段时必须指定表名必须指定表名尽量在联接中尽量在联接中限制限制表的表的个数个数联接概述（续）联接概述（续）使用内联接使用内联接l内联接通过比较被联接的表所共同拥有的字段，内联接通过比较被联接的表所共同拥有的字段，把多个表联接起来。把多个表联接起来。将只返回满足联接条件的行将只返回满足联接条件的行。l为什么要使用内联接为什么要使用内联接l内联接是内联接是 SQL

9、Server 缺省的联接方式缺省的联接方式l在选择列表中指定结果集要显示的字段名在选择列表中指定结果集要显示的字段名l使用使用 WHERE 子句来限制结果集要返回的记录子句来限制结果集要返回的记录l在联接条件中不要使用空值，因为空值与其他在联接条件中不要使用空值，因为空值与其他值不会相等值不会相等使用内联接（续）使用内联接（续）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示例示例列出购买产品的顾客信息列出购买产品的顾客信息buyer_name, sales.buyer_id, qty使用外联接使用外联接l左（右）外联接可以从两个表中返回符合联接条左（右）外联接可以从两个表中返回符合联接条件的记录，同时也将返回左（右）边不符合联接件的记录，同时也将返回左（右）边不符合联接条件的记录条件的记录l使用左（右）外联接时，请注意使用左（右）外联接时

11、，请注意l不满足联接条件的记录将显示空值不满足联接条件的记录将显示空值l左联接可以显示第一个表中所有记录左联接可以显示第一个表中所有记录l右联接可以显示第二个表中所有记录右联接可以显示第二个表中所有记录使用外联接（续）使用外联接（续）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列出购买产品的顾客信息列出购买产品的顾客信息buyer_name, sales.buyer_id, qty。结果集也将返回没有购买任何产品的顾客结果集也将返回没有购买任何产品的顾客使用交叉联接使用交叉联接l交叉联接将从被联接的表中返回所有可能的记录交叉联接将从被联接的表中返回所有可能的记录组合组合l为什么使用交叉联接为什么使用交叉联接 l在规范化的数据库中，很少使用交叉联接在规范化的数据库中，很少使用交叉联接l

13、可以为数据库生成测试数据可以为数据库生成测试数据l为清单及企业模板生成所有可能的组合数据为清单及企业模板生成所有可能的组合数据使用交叉联接（续）使用交叉联接（续）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的所有可能的值的组合的所有可能的值的组合联接两个以上的表联接两个以上的表l联接任意数目的表都有可能联接任意数目的表都有可能l通过使用共同拥有的字段，任何通过使用共同拥有的字段，任何一个表都可以和其他表联接一个表都可以和其他表联接联接两个以上的表（续）联接两个以上的表（续）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示例示例自联接自联接 （Sel

16、f-Join）l使用自联接，可以使用自联接，可以查询一个表中各查询一个表中各记录之间的关系记录之间的关系l使用自联接时，应注意使用自联接时，应注意 l引用表的两份副本时，必须使用表的别名引用表的两份副本时，必须使用表的别名l生成自联接时，表中每一行都和自身比较一生成自联接时，表中每一行都和自身比较一下，并生成重复的记录，使用下，并生成重复的记录，使用 WHERE WHERE 子句来子句来消除这些重复记录消除这些重复记录Sales 检索出购买了相同产品的顾客检索出购买了相同产品的顾客ID号以及其购买的号以及其购买的产品产品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 的区别的区别lJOIN 是合并多个表并生存是合并多个表并生存一个单独的结果集一个单独的结果集，该结果集将包含多个表中的字段该结果集将包含多