SQL实用简明教程-第7章-多表连接查询课件

1、第7章 多表连接查询 重点内容： SQL连接语法 连接的处理步骤 连接的类型SELECT select_listFROM Table1name CROSS | NATURAL JOIN Table2nameON Table1name.column operator Table2name.columnWHERE conditions一、连接的语法SQL中为连接提供了关键字JOIN、CROSS JOIN及NATURAL JOIN，用于连接操作。 第一行中的select_list仍然是选择的列的名称，但是需要注意的是，如果使用两个表中有相同名称的列，则必须限定是哪个表中的列； 第二行中关键字CROS

2、S与NATURAL是可选项，可以使用也可以不使用。如果选择了CROSS或NATURAL关键字，那么就不能使用ON关键字； 单独使用JOIN关键字时，需要使用ON关键字来设定连接的条件； 使用CROSS JOIN时，不能使用关键字ON，因此必须使用WHERE子句设定连接的条件。因为如果不设定连接条件，将返回表之间的每一种可能组合(即笛卡尔积)，连接结果将非常庞大； NATURAL JOIN只有在两个表有相同名称的列时才能使用，将在同名列上进行连接。因此，不必使用关键字ON或WHERE子句设定连接条件。当然，使用WHERE子句限制查找的行仍然是可以的。 二、使用连接的原因连接是关系模型的关键操作，

3、因为表之间的数据并不是互不相关的，而是存在一定的联系的，数据库中仅仅知道各表单独的数据是不足够的。这时就需要通过连接来发现数据之间的关系，找出数据之间的联系，而不仅仅局限于数据库中已有的一些关系。 使用连接还有一个好处：连接对于结果没有特别的限制，具有很大的灵活性。通常可以通过连接创建包含不同表中数据的新表或者视图。这就使得数据库的扩展具有很大的灵活性。 三、处理连接的方法1、笛卡儿积 有的时候，由于连接条件设置的不好，甚至没有设置连接条件，结果将包含太多的行。这是由于笛卡尔积造成的。从概念上说，连接首先将形成表的笛卡尔积，即形成用于连接的表中所有的行的组合。 用于连接的表越多，其笛卡尔积的结

4、果将越大，越需要很好的指定连接条件。当然，在一些特殊情况下，如一个表中只包含一个记录时，可以不指定连接条件。因为这时的笛卡尔积是另外一个表中记录的个数。 进行连接操作时，都是先形成表之间的笛卡尔积，获得笛卡尔积之后再使用指定的连接条件及WHERE子句中其它的限制条件对形成的笛卡尔积进行删除，保留符合条件的记录。正确的指定连接条件和限制条件将有助于产生良好的连接结果。当返回的结果过多时，请注意检查连接条件是否合适。 此外，由于连接过程中使用了笛卡尔积，因此连接操作会导致性能的下降。因此，在使用时最好遵循以下一些原则： （1）用于连接的列已经创建了索引。因为索引会单独保存在磁盘上，且将数据按照一定

5、顺序进行了排列，索引的使用可以加快访问的速度； （2）用于连接的列具有相同的数据类型，包括是否允许空值。如果需要系统自动进行类型转换是需要花费较多时间的，特别是在表中记录很多时，类型转换所花费的时间将会很多. 三、处理连接的方法2、连接中运算符的使用 在进行连接时，可以使用前面介绍的运算符指定连接的条件。最常见的是使用等号进行连接，也可以使用大于或小于等其它运算符进行连接操作。此外，在进行连接时，还可以使用WHERE子句限制查找的行。3、多表连接 在FROM子句中添加表名，在连接条件中设定新的连接条件即可实现多表连接。在多表连接时也可以使用JOIN关键字，不过用起来比较繁琐，不如FROM/WH

6、ERE来的简便。多表连接一个很重要的应用就是结合连接表进行使用。 三、处理连接的方法4、连接结果的处理 限制返回的列 当使用连接来连接多个表时，一般来说都不会使用“*”来选择所有的列，而是在SELECT列表中列出需要返回的列。如果使用“*”包含所有的列，那么显示时列将按照创建表时指定的顺序显示。 删除重复 返回连接结果时，不一定要显示用于连接的列。需要显示时，也应该注意不要出现重复。 使用别名 在进行连接时，也可以为表或列创建别名以方便使用或使显示结果更加清晰。特别是表中有相同名称的列时，别名的使用可以使代码更易于输入，且更易于阅读。 空值的处理 如果连接表的连接列中存在NULL时，NULL将

7、不会参与连接，即使两个表的连接列都存在NULL，连接结果也不会存在NULL。因为，空值代表的是未知、丢失或不可用的值，所以连接时不会对空值进行连接。 三、处理连接的方法5、连接的处理步骤 处理、生成查询结果有很多步骤。使用连接操作时的处理过程是比较复杂的，下面是包含连接操作时的处理步骤： (1)为连接表生成笛卡尔积。这是连接操作引起性能下降的主要原因。笛卡尔积是连接表包含的所有行之间的组合。当用于连接的表中记录较多时，形成的笛卡尔积将非常的大，形成笛卡尔积花费的时间也将更多。 (2)使用WHERE子句从形成的笛卡尔积中去除所有不符合限制条件的行。这时SELECT子句还没有进行执行，因此，连接表

8、形成的笛卡尔积中包含了用于连接的表的所有列。 (3)使用WHERE子句进行删除后，如果语句中包含了GROUP BY子句，将按照GROUP BY子句中指定的列对剩下的行进行分组。虽然，这时SELECT子句还没有执行，但是如果SELECT列表中包含了聚集函数将会对分好的组进行计算。 (4)如果语句中使用了HAVING子句，将使用其设定的条件对分组结果进行删除，不符合条件的组将会被删除。 (5) SELECT子句将被应用到余下的数据中，表达式也被执行，选出在SELECT列表中的列，不在其内的列将会被删除。如果有ORDER BY子句就将结果按照ORDER BY子句指定的列进行排序显示出来。 四、连接的

9、类型1、一般的连接 一般的连接通常通过两个表中的某个特定的列进行连接。最通常使用的是相等连接，当然也可以使用大于连接、小于连接等。 SELECT name, city, pnumber, accepterFROM Employee e, Paccepter aWHERE e.city = a.city AND pno = pnumberResults name city pnumber accepter 庞文凯 杭州 301 科华贸易公司张峰 杭州 301 科华贸易公司林雪飞 厦门 104 神州发展有限公司梁朝阳 杭州 301 科华贸易公司 林国荣 天津 105 四海贸易有限公司 王力刚 天津

10、 105 四海贸易有限公司李大平 北京 204 CESA. COMPANY魏华翔 北京 204 东胜有限公司姜艳成 北京 204 东胜有限公司9rows四、连接的类型2、自然连接 自然连接是一种特殊的连接。自然连接只有在两个表有相同名称的列且列的涵义相似时才能使用，将在同名列上进行相等连接。这也就是SQL标准中定义的NATURAL JOIN关键字。使用NATURAL JOIN关键字时，不必使用关键字ON或WHERE子句设定连接条件。当然，使用WHERE子句限制查找的行仍然是可以的。使用传统的FROM/WHERE子句，则需要在WHERE子句中指定连接列的相等条件。 SELECT pnumber,

11、 accepter, pmgridFROM Project p NATURAL JOIN Paccepter aSELECT pnumber, accepter, pmgridFROM Project p, Paccepter aWHERE p.pnumber = a.pnumber Resultspnumber accepter pmgrid 101 蓝科通讯公司 1002 102 华夏技术有限公司 1001 103 汉升咨询公司 1001 104 神州发展有限公司 1002 105 四海贸易有限公司 1004201 化天通讯公司 4003202 日胜公司 4004203 华田汽修公司 40

12、03204 东胜有限公司 4003301 科华贸易公司 4001302 新安有限公司 4001303 华田汽修公司 100112rows 四、连接的类型3、自连接 自连接用于单个表内进行比较。使用自连接时，需要注意两点：一是由于是将表与其自身相连接，因此必须对表设定别名；二是自连接时会将行与其自身相连，因此必须设定条件来防止。SELECT DISTINCT a1.accepter, a1.city, a2.accepter, a2.cityFROM Paccepter a1, Paccepter a2WHERE a1.city = a2.city AND a1.accepter a2.acce

13、pter AND a1.city IN(杭州,北京) Results accepter city accepter city 蓝科通讯公司 杭州 汉升咨询公司 杭州蓝科通讯公司 杭州 科华贸易公司 杭州汉升咨询公司 杭州 蓝科通讯公司 杭州汉升咨询公司 杭州 科华贸易公司 杭州科华贸易公司 杭州 蓝科通讯公司 杭州科华贸易公司 杭州 汉升咨询公司 杭州化天通讯公司 北京 华田汽修公司 北京化天通讯公司 北京 东胜有限公司 北京华田汽修公司 北京 东胜有限公司 北京华田汽修公司 北京 化天通讯公司 北京东胜有限公司 北京 化天通讯公司 北京东胜有限公司 北京 华田汽修公司 北京12rows 四、

14、连接的类型4、外连接 有时候用户可能希望显示某个表中所有记录，包括不符合连接条件的记录，这正是外连接的作用。使用外连接可以方便的在连接结果中包含某个表中的其它记录。SQL中提供了两个关键字：LEFT OUTER JOIN与RIGHT OUTER JOIN。LEFT OUTER JOIN关键字使外连接显示LEFT OUTER JOIN左侧的表包含的所有记录。而RIGHT OUTER JOIN关键字则使外连接显示RIGHT OUTER JOIN右侧的表所包含的所有记录。 五、对集合的支持1、UNON运算 UNOIN运算是集合运算中应用的最多的，得到了广泛的支持。UNION运算可以从多个表中组合数据

15、进行显示，但是与连接不同，UNION不是在FROM子句中添加多个表并指定连接条件实现，而是通过将多个查询的结果结合到一起实现的。 SELECT select_listFROM table_list/view_listWHERE conditionsUNION ALLSELECT select_listFROM table_list/view_listWHERE conditions五、对集合的支持2、INTERSECT运算 INTERSECT运算与UNION运算相似，UNION运算中应该注意的问题、提高结果可读性的方法都可以在INTERSECT运算中使用。INTERSECT运算将返回多个查询结果中相同的行。 SELECT cityFROM EmployeeWHERE dno = 2INTERSECTSELECT DISTINCT cityFROM PaccepterResults city 北京 上海厦门3r