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

第7章多表连接查询重点内容：SQL连接语法连接的处理步骤连接的类型第7章多表连接查询1SELECTselect_listFROMTable1name[CROSS|NATURAL]JOINTable2name[ON[Table1name.]columnoperator[Table2name.]column][WHEREconditions]一、连接的语法

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

连接是关系模型的关键操作，因为表之间的数据并不是互不相关的，而是存在一定的联系的，数据库中仅仅知道各表单独的数据是不足够的。这时就需要通过连接来发现数据之间的关系，找出数据之间的联系，而不仅仅局限于数据库中已有的一些关系。使用连接还有一个好处：连接对于结果没有特别的限制，具有很大的灵活性。通常可以通过连接创建包含不同表中数据的新表或者视图。这就使得数据库的扩展具有很大的灵活性。二、使用连接的原因3三、处理连接的方法1、笛卡儿积

有的时候，由于连接条件设置的不好，甚至没有设置连接条件，结果将包含太多的行。这是由于笛卡尔积造成的。从概念上说，连接首先将形成表的笛卡尔积，即形成用于连接的表中所有的行的组合。用于连接的表越多，其笛卡尔积的结果将越大，越需要很好的指定连接条件。当然，在一些特殊情况下，如一个表中只包含一个记录时，可以不指定连接条件。因为这时的笛卡尔积是另外一个表中记录的个数。进行连接操作时，都是先形成表之间的笛卡尔积，获得笛卡尔积之后再使用指定的连接条件及WHERE子句中其它的限制条件对形成的笛卡尔积进行删除，保留符合条件的记录。正确的指定连接条件和限制条件将有助于产生良好的连接结果。当返回的结果过多时，请注意检查连接条件是否合适。此外，由于连接过程中使用了笛卡尔积，因此连接操作会导致性能的下降。因此，在使用时最好遵循以下一些原则：（1）用于连接的列已经创建了索引。因为索引会单独保存在磁盘上，且将数据按照一定顺序进行了排列，索引的使用可以加快访问的速度；（2）用于连接的列具有相同的数据类型，包括是否允许空值。如果需要系统自动进行类型转换是需要花费较多时间的，特别是在表中记录很多时，类型转换所花费的时间将会很多.

三、处理连接的方法4三、处理连接的方法2、连接中运算符的使用

在进行连接时，可以使用前面介绍的运算符指定连接的条件。最常见的是使用等号进行连接，也可以使用大于或小于等其它运算符进行连接操作。此外，在进行连接时，还可以使用WHERE子句限制查找的行。3、多表连接在FROM子句中添加表名，在连接条件中设定新的连接条件即可实现多表连接。在多表连接时也可以使用JOIN关键字，不过用起来比较繁琐，不如FROM/WHERE来的简便。多表连接一个很重要的应用就是结合连接表进行使用。

三、处理连接的方法5三、处理连接的方法4、连接结果的处理限制返回的列

当使用连接来连接多个表时，一般来说都不会使用“*”来选择所有的列，而是在SELECT列表中列出需要返回的列。如果使用“*”包含所有的列，那么显示时列将按照创建表时指定的顺序显示。

删除重复

返回连接结果时，不一定要显示用于连接的列。需要显示时，也应该注意不要出现重复。

使用别名在进行连接时，也可以为表或列创建别名以方便使用或使显示结果更加清晰。特别是表中有相同名称的列时，别名的使用可以使代码更易于输入，且更易于阅读。

空值的处理如果连接表的连接列中存在NULL时，NULL将不会参与连接，即使两个表的连接列都存在NULL，连接结果也不会存在NULL。因为，空值代表的是未知、丢失或不可用的值，所以连接时不会对空值进行连接。

三、处理连接的方法限制返回的列当使用连接来连接多个表时，一6三、处理连接的方法5、连接的处理步骤 处理、生成查询结果有很多步骤。使用连接操作时的处理过程是比较复杂的，下面是包含连接操作时的处理步骤：(1)为连接表生成笛卡尔积。这是连接操作引起性能下降的主要原因。笛卡尔积是连接表包含的所有行之间的组合。当用于连接的表中记录较多时，形成的笛卡尔积将非常的大，形成笛卡尔积花费的时间也将更多。(2)使用WHERE子句从形成的笛卡尔积中去除所有不符合限制条件的行。这时SELECT子句还没有进行执行，因此，连接表形成的笛卡尔积中包含了用于连接的表的所有列。(3)使用WHERE子句进行删除后，如果语句中包含了GROUPBY子句，将按照GROUPBY子句中指定的列对剩下的行进行分组。虽然，这时SELECT子句还没有执行，但是如果SELECT列表中包含了聚集函数将会对分好的组进行计算。(4)如果语句中使用了HAVING子句，将使用其设定的条件对分组结果进行删除，不符合条件的组将会被删除。(5)SELECT子句将被应用到余下的数据中，表达式也被执行，选出在SELECT列表中的列，不在其内的列将会被删除。如果有ORDERBY子句就将结果按照ORDERBY子句指定的列进行排序显示出来。

三、处理连接的方法7四、连接的类型1、一般的连接

一般的连接通常通过两个表中的某个特定的列进行连接。最通常使用的是相等连接，当然也可以使用大于连接、小于连接等。SELECTname,city,pnumber,accepterFROMEmployeee,PaccepteraWHEREe.city=a.cityANDpno=pnumber

Resultsnamecitypnumberaccepter——————————————————庞文凯杭州301科华贸易公司张峰杭州301科华贸易公司林雪飞厦门104神州发展有限公司梁朝阳杭州301科华贸易公司林国荣天津105四海贸易有限公司王力刚天津105四海贸易有限公司李大平北京204CESA.COMPANY魏华翔北京204东胜有限公司姜艳成北京204东胜有限公司[9rows]四、连接的类型SELECTname,city,pnum8四、连接的类型2、自然连接

自然连接是一种特殊的连接。自然连接只有在两个表有相同名称的列且列的涵义相似时才能使用，将在同名列上进行相等连接。这也就是SQL标准中定义的NATURALJOIN关键字。使用NATURALJOIN关键字时，不必使用关键字ON或WHERE子句设定连接条件。当然，使用WHERE子句限制查找的行仍然是可以的。使用传统的FROM/WHERE子句，则需要在WHERE子句中指定连接列的相等条件。

SELECTpnumber,accepter,pmgridFROMProjectpNATURALJOINPacceptera

SELECTpnumber,accepter,pmgridFROMProjectp,PaccepteraWHEREp.pnumber=a.pnumberResultspnumberaccepterpmgrid———————————————101蓝科通讯公司1002102华夏技术有限公司1001103汉升咨询公司1001104神州发展有限公司1002105四海贸易有限公司1004201化天通讯公司4003202日胜公司4004203华田汽修公司4003204东胜有限公司4003301科华贸易公司4001302新安有限公司4001303华田汽修公司1001[12rows]四、连接的类型SELECTpnumber,accepte9四、连接的类型3、自连接

自连接用于单个表内进行比较。使用自连接时，需要注意两点：一是由于是将表与其自身相连接，因此必须对表设定别名；二是自连接时会将行与其自身相连，因此必须设定条件来防止。SELECTDISTINCTa1.accepter,a1.city,a2.accepter,a2.cityFROMPacceptera1,Pacceptera2WHEREa1.city=a2.cityANDa1.accepter<>a2.accepterANDa1.cityIN(’杭州’,’北京’)

Resultsacceptercityacceptercity———————————————————蓝科通讯公司杭州汉升咨询公司杭州蓝科通讯公司杭州科华贸易公司杭州汉升咨询公司杭州蓝科通讯公司杭州汉升咨询公司杭州科华贸易公司杭州科华贸易公司杭州蓝科通讯公司杭州科华贸易公司杭州汉升咨询公司杭州化天通讯公司北京华田汽修公司北京化天通讯公司北京东胜有限公司北京华田汽修公司北京东胜有限公司北京华田汽修公司北京化天通讯公司北京东胜有限公司北京化天通讯公司北京东胜有限公司北京华田汽修公司北京[12rows]四、连接的类型SELECTDISTINCTa1.acce10四、连接的类型4、外连接

有时候用户可能希望显示某个表中所有记录，包括不符合连接条件的记录，这正是外连接的作用。使用外连接可以方便的在连接结果中包含某个表中的其它记录。SQL中提供了两个关键字：LEFTOUTERJOIN与RIGHTOUTERJOIN。LEFTOUTERJOIN关键字使外连接显示LEFTOUTERJOIN左侧的表包含的所有记录。而RIGHTOUTERJOIN关键字则使外连接显示RIGHTOUTERJOIN右侧的表所包含的所有记录。四、连接的类型11五、对集合的支持1、UNON运算

UNOIN运算是集合运算中应用的最多的，得到了广泛的支持。UNION运算可以从多个表中组合数据进行显示，但是与连接不同，UNION不是在FROM子句中添加多个表并指定连接条件实现，而是通过将多个查询的结果结合到一起实现的。SELECTselect_listFROMtable_list/view_list[WHEREconditions]UNION[ALL]SELECTselect_listFROMtable_list/view_list[WHEREconditions]五、对集合的支持SELECTselect_list12五、对集合的支持2、INTERSECT运算

INTERSECT运算与UNION运算相似，UNION运算中应该注意的问题、提高结果可读性的方法都可以在INTERSECT运算中使用。INTERSECT运算将返回多个查询结果中相同的行。SELECTcityFROMEmployeeWHEREdno=2INTERSECTSELECTDISTINCTcityFROMPaccepter

Resultscity————北京上海厦门[3rows]五、对集合的支持SELECTcityResults13五、对集合的支持3、MINUS运算

MINUS返回在一个查询结果中存在，且不在另一个查询结果中存在的所有行，即两个查询的差集。如果在第二个查询结果中也存在相同的行，MINUS返回的结果中将不包含这些行。SELECTcityFROMPaccepterMINUSSELECTcityFROMEmployeeWHEREdno=2

Resultscity————北京杭州天津[3rows]五、对集合的支持SELECTcityResults14本章小结：掌握SQL中连接的语法和处理连接的方法掌握连接的各种类型掌握SQL所支持的集合运算本章小结：15第7章多表连接查询重点内容：SQL连接语法连接的处理步骤连接的类型第7章多表连接查询16SELECTselect_listFROMTable1name[CROSS|NATURAL]JOINTable2name[ON[Table1name.]columnoperator[Table2name.]column][WHEREconditions]一、连接的语法

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

连接是关系模型的关键操作，因为表之间的数据并不是互不相关的，而是存在一定的联系的，数据库中仅仅知道各表单独的数据是不足够的。这时就需要通过连接来发现数据之间的关系，找出数据之间的联系，而不仅仅局限于数据库中已有的一些关系。使用连接还有一个好处：连接对于结果没有特别的限制，具有很大的灵活性。通常可以通过连接创建包含不同表中数据的新表或者视图。这就使得数据库的扩展具有很大的灵活性。二、使用连接的原因18三、处理连接的方法1、笛卡儿积

有的时候，由于连接条件设置的不好，甚至没有设置连接条件，结果将包含太多的行。这是由于笛卡尔积造成的。从概念上说，连接首先将形成表的笛卡尔积，即形成用于连接的表中所有的行的组合。用于连接的表越多，其笛卡尔积的结果将越大，越需要很好的指定连接条件。当然，在一些特殊情况下，如一个表中只包含一个记录时，可以不指定连接条件。因为这时的笛卡尔积是另外一个表中记录的个数。进行连接操作时，都是先形成表之间的笛卡尔积，获得笛卡尔积之后再使用指定的连接条件及WHERE子句中其它的限制条件对形成的笛卡尔积进行删除，保留符合条件的记录。正确的指定连接条件和限制条件将有助于产生良好的连接结果。当返回的结果过多时，请注意检查连接条件是否合适。此外，由于连接过程中使用了笛卡尔积，因此连接操作会导致性能的下降。因此，在使用时最好遵循以下一些原则：（1）用于连接的列已经创建了索引。因为索引会单独保存在磁盘上，且将数据按照一定顺序进行了排列，索引的使用可以加快访问的速度；（2）用于连接的列具有相同的数据类型，包括是否允许空值。如果需要系统自动进行类型转换是需要花费较多时间的，特别是在表中记录很多时，类型转换所花费的时间将会很多.

三、处理连接的方法19三、处理连接的方法2、连接中运算符的使用

在进行连接时，可以使用前面介绍的运算符指定连接的条件。最常见的是使用等号进行连接，也可以使用大于或小于等其它运算符进行连接操作。此外，在进行连接时，还可以使用WHERE子句限制查找的行。3、多表连接在FROM子句中添加表名，在连接条件中设定新的连接条件即可实现多表连接。在多表连接时也可以使用JOIN关键字，不过用起来比较繁琐，不如FROM/WHERE来的简便。多表连接一个很重要的应用就是结合连接表进行使用。

三、处理连接的方法20三、处理连接的方法4、连接结果的处理限制返回的列

当使用连接来连接多个表时，一般来说都不会使用“*”来选择所有的列，而是在SELECT列表中列出需要返回的列。如果使用“*”包含所有的列，那么显示时列将按照创建表时指定的顺序显示。

删除重复

返回连接结果时，不一定要显示用于连接的列。需要显示时，也应该注意不要出现重复。

使用别名在进行连接时，也可以为表或列创建别名以方便使用或使显示结果更加清晰。特别是表中有相同名称的列时，别名的使用可以使代码更易于输入，且更易于阅读。

空值的处理如果连接表的连接列中存在NULL时，NULL将不会参与连接，即使两个表的连接列都存在NULL，连接结果也不会存在NULL。因为，空值代表的是未知、丢失或不可用的值，所以连接时不会对空值进行连接。

三、处理连接的方法限制返回的列当使用连接来连接多个表时，一21三、处理连接的方法5、连接的处理步骤 处理、生成查询结果有很多步骤。使用连接操作时的处理过程是比较复杂的，下面是包含连接操作时的处理步骤：(1)为连接表生成笛卡尔积。这是连接操作引起性能下降的主要原因。笛卡尔积是连接表包含的所有行之间的组合。当用于连接的表中记录较多时，形成的笛卡尔积将非常的大，形成笛卡尔积花费的时间也将更多。(2)使用WHERE子句从形成的笛卡尔积中去除所有不符合限制条件的行。这时SELECT子句还没有进行执行，因此，连接表形成的笛卡尔积中包含了用于连接的表的所有列。(3)使用WHERE子句进行删除后，如果语句中包含了GROUPBY子句，将按照GROUPBY子句中指定的列对剩下的行进行分组。虽然，这时SELECT子句还没有执行，但是如果SELECT列表中包含了聚集函数将会对分好的组进行计算。(4)如果语句中使用了HAVING子句，将使用其设定的条件对分组结果进行删除，不符合条件的组将会被删除。(5)SELECT子句将被应用到余下的数据中，表达式也被执行，选出在SELECT列表中的列，不在其内的列将会被删除。如果有ORDERBY子句就将结果按照ORDERBY子句指定的列进行排序显示出来。

三、处理连接的方法22四、连接的类型1、一般的连接

一般的连接通常通过两个表中的某个特定的列进行连接。最通常使用的是相等连接，当然也可以使用大于连接、小于连接等。SELECTname,city,pnumber,accepterFROMEmployeee,PaccepteraWHEREe.city=a.cityANDpno=pnumber

Resultsnamecitypnumberaccepter——————————————————庞文凯杭州301科华贸易公司张峰杭州301科华贸易公司林雪飞厦门104神州发展有限公司梁朝阳杭州301科华贸易公司林国荣天津105四海贸易有限公司王力刚天津105四海贸易有限公司李大平北京204CESA.COMPANY魏华翔北京204东胜有限公司姜艳成北京204东胜有限公司[9rows]四、连接的类型SELECTname,city,pnum23四、连接的类型2、自然连接

自然连接是一种特殊的连接。自然连接只有在两个表有相同名称的列且列的涵义相似时才能使用，将在同名列上进行相等连接。这也就是SQL标准中定义的NATURALJOIN关键字。使用NATURALJOIN关键字时，不必使用关键字ON或WHERE子句设定连接条件。当然，使用WHERE子句限制查找的行仍然是可以的。使用传统的FROM/WHERE子句，则需要在WHERE子句中指定连接列的相等条件。

SELECTpnumber,accepter,pmgridFROMProjectpNATURALJOINPacceptera

SELECTpnumber,accepter,pmgridFROMProjectp,PaccepteraWHEREp.pnumber=a.pnumberResultspnumberaccepterpmgrid———————————————101蓝科通讯公司1002102华夏技术有限公司1001103汉升咨询公司1001104神州发展有限公司1002105四海贸易有限公司1004201化天通讯公司4003202日胜公司4004203华田汽修公司4003204东胜有限公司4003301科华贸易公司4001302新安有限公司4001303华田汽修公司1001[12rows]四、连接的类型SELECTpnumber,accepte24四、连接的类型3、自连接

自连接用于单个表内进行比较。使用自连接时，需要注意两点：一是由于是将表与其自身相连接，因此必须对表设定别名；二是自连接时会将行与其自身相连，因此必须设定条件来防止。SELECTDISTINCTa1.accepter,a1.city,a2.accepter,a2.cityFROMPacceptera1,Pacceptera2WHEREa1.city=a2.cityANDa1.accepter<>a2.accepterANDa1.cityIN(’杭州’,’北京’)

Resultsacceptercityacceptercity———————————————————蓝科通讯公司杭州汉升咨询公司杭州蓝科通讯公司杭州科华贸易公司杭州汉升咨询公司杭州蓝科通讯公司杭州汉升咨询公司杭州科华贸易公司杭州科华贸易公司杭州蓝科通讯公司杭州科华贸易公司杭州汉升咨询公司杭州化天通讯公司北京华田汽修公司北京化天通讯公司北京东胜有限公司北京华田汽修公司北京东胜有限公司北京华田汽修公司北京化天通讯公司北京东胜有限公司北京化天通讯公司北京东胜有限公司北京