sql 各种连接方式的区别

1、数据库innerjoin，leftjoin,rightjoin的区另Uinnerjoin：内连接，结果只包含满足条件的列。leftjoin：左外连接，结果包含满足条件的行及左侧表中的全部行。rightjoin：右外连接，结果包含满足条件的行及右侧表中的全部行。SQL语句(innerjoin,leftoutjoin,rightoutjoin)leftjoin:左连接，返回左表中所有的记录以及右表中连接字段相等的记录。rightjoin:右连接，返回右表中所有的记录以及左表中连接字段相等的记录。innerjoin:内连接，又叫等值连接，只返回两个表中连接字段相等的行。fulljoin:外连接，返回

2、两个表中的行：leftjoin+rightjoincrossjoin:结果是笛卡尔积，就是第一个表的行数乘以第二个表的行数。declareatable(aint,bint)declarebtable(aint,bint)insertavalues(1,1)insertavalues(2,2)insertbvalues(1,1)insertbvalues(3,3)select*fromaselect*fromb-左:select*from*=Bb.a-右：seiect*from*=Bba-内seiect*from*=Bba-外：seiect*from*=Bba-交叉连接select*fromac

3、rossjoinbleft以左边为准右边有则显示无则NULLright反之inner只取都不null的，相当于用froma,bwhere?=?（连接是on?=?）leftjoin和leftouterjoin的区别通俗的讲：AleftjoinB的连接的记录数与A表的记录数同ArightjoinB的连接的记录数与B表的记录数同AleftjoinB等价BrightjoinA举个例子：假设a表和b表的数据是这样的。abidnameidstock1a1152b2503cselect*fromainnerjoinbona.id=b.id这个语法是连接查询中的内连接，它产生的结果是两个表相匹配的记录出现在结

4、果列表中。根据上面的表，出现的结果是这样的a.idnameb.idstock1a1152b250select*froma,bwherea.id=b.id这个语法是内连接的另外一种写法，其执行结果与innerjoin样select*fromaleft/rightjoinbona.id=b.id这个是外连接语法中的左外连接或右外连接如果是左外连接的话，它将显示a表的所有记录，selecta.*,b.*fromaleftjoinbona.id=b.id查询的结果是这样的：a.idnameb.idstock1a1152b2503cnullnull如果是右外连接的话，它将显示b表的所有记录，select

5、a.*,b.*fromarightjoinbona.id=b.id查询的结果是这样的：a.idnameb.idstock1a1152b250selecta.*,b.*fromaleftjoinbona.k=b.kselecta.*,b.*fromaleftouterjoinbona.k=b.k上面两种一样leftjoin是leftouterjoin的简写selecta.*,b.*fromaleftinnerjoinbona.k=b.k没有这种写法，错误的语句在你要使用多个leftjoin的时候比如说10个我们把10个全都写成leftjoin的形式然后再SQL让他自动运行一下，它会把最后一次出现

6、的leftjoin变成leftouterjoin所以依此推理，最后一个leftjoin会以leftouterjoin的形式存在当然，不管变不变对结果的显示没有任何影响希望我的实验能对你有所帮助使用关系代数合并数据1关系代数合并数据集合的理论基础是关系代数，它是由E.F.Codd于1970年提出的。在关系代数的形式化语言中：用表、或者数据集合表示关系或者实体。P用行表示兀组。P用列表示属性。P关系代数包含以下8个关系运算符选取一一返回满足指定条件的行。p投影一一从数据集合中返回指定的列。p笛卡尔积一一是关系的乘法，它将分别来自两个数据集合中的行以所有可能的方式进行组合。p并一一关系的加法和减法，

7、它可以在行的方向上合并两个表中的数据，就像把一个表垒在另一个表之上一样。p交一一返回两个数据集合所共有的行。p差一一返回只属于一个数据集合的行。p连接在水平方向上合并两个表，其方法是：将两个表中在共同数据项上相互匹配的那些行合并起来。p除一一返回两个数据集之间的精确匹配。p此外，作为一种实现现代关系代数运算的方法，SQL还提供了：子查询一一类似于连接，但更灵活；在外部查询中，方式可以使用表达式、列表或者数据集合的地方都可以使用子查询的结果。p本章将主要讲述多种类型的连接、简单的和相关的子查询、几种类型的并、关系除以及其他的内容。2使用连接2.1连接类型在关系代数中，连接运算是由一个笛卡尔积运算

8、和一个选取运算构成的。首先用笛卡尔积完成对两个数据集合的乘运算，然后对生成的结果集合进行选取运算，确保只把分别来自两个数据集合并且具有重叠部分的行合并在一起。连接的全部意义在于在水平方向上合并两个数据集合（通常是表），并产生一个新的结果集合，其方法是将一个数据源中的行于另一个数据源中和它匹配的行组合成一个新元组。SQL提供了多种类型的连接方式，它们之间的区别在于：从相互交叠的不同数据集合中选择用于连接的行时所采用的方法不同。连接类型定义内连接只连接匹配的行左外连接包含左边表的全部行（不管右边的表中是否存在与它们匹配的行），以及右边表中全部匹配的行右外连接包含右边表的全部行（不管左边的表中是否存

9、在与它们匹配的行），以及左边表中全部匹配的行全外连接包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行。（H）（theta）连接使用等值以外的条件来匹配左、右两个表中的行交叉连接生成笛卡尔积一它不使用任何匹配或者选取条件，而是直接将一个数据源中的每个行与另一个数据源的每个行都一一匹配在INFORMIX中连接表的查询如果FROM子句指定了多于一个表引用，则查询会连接来自多个表的行。连接条件指定各列之间（每个表至少一列）进行连接的关系。因为正在比较连接条件中的列，所以它们必须具有一致的数据类型。SELECT语句的FROM子句可以指定以下几种类型的连接FROM子句关键字相应的结果集C

10、ROSSJOIN笛卡尔乘积（所有可能的行对）INNERJOIN仅对满足连接条件的CROSS中的列LEFTOUTERJOIN一个表满足条件的行，和另一个表的所有行RIGHTOUTERJOIN与LEFT相同，但两个表的角色互换FULLOUTERJOINLEFTOUTER和RIGHTOUTER中所有行的超集内连接(InnerJoin内连接是最常见的一种连接，它页被称为普通连接，而E.FCodd最早称之为自然连接。下面是ANSISQL-92标准select*fromt_institutioniinnerjoint_tellertoni.inst_no=t.inst_nowherei.inst_no=5

11、801其中inner可以省略。等价于早期的连接语法select*fromt_institutioni,t_tellertwherei.inst_no=t.inst_noandi.inst_no=58012.3外连接左外连接(LeftOuterJion)select*fromt_institutionileftouterjoint_tellertoni.inst_no=t.inst_no其中outer可以省略。右外连接(RigtOuterJion)select*fromt_institutionirightouterjoint_tellertoni.inst_no=t.inst_no全外连接(F

12、ullOuter)全外连接返回参与连接的两个数据集合中的全部数据，无论它们是否具有与之相匹配的行。在功能上，它等价于对这两个数据集合分别进行左外连接和右外连接，然后再使用消去重复行的并操作将上述两个结果集合并为一个结果集。在现实生活中，参照完整性约束可以减少对于全外连接的使用，一般情况下左外连接就足够了。在数据库中没有利用清晰、规范的约束来防范错误数据情况下，全外连接就变得非常有用了，你可以使用它来清理数据库中的数据。select*fromt_institutionifullouterjoint_tellertoni.inst_no=t.inst_no外连接与条件配合使用当在内连接查询中加入条

13、件是，无论是将它加入到join子句，还是加入到where子句，其效果是完全一样的，但对于外连接情况就不同了。当把条件加入到join子句时，SQLServer、Informix会返回外连接表的全部行，然后使用指定的条件返回第二个表的行。如果将条件放到where子句中，SQLServer将会首先进行连接操作，然后使用where子句对连接后的行进行筛选。下面的两个查询展示了条件放置位子对执行结果的影响：条件在join子句select*fromt_institutionileftouterjoint_tellertoni.inst_no=t.inst_noandi.instno=5801inst_no

14、inst_nameinst_noteller_notell5801天河区58010001tom5801天河区58010002david5802越秀区5803白云区结果是:条件在where子句nameselect*fromt_institutionileftouterjoint_tellertoni.inst_no=t.inst_nowherei.inst_no=5801结果是:instnoinstname5801天河区5801天河区instnotellernotellername58010001tom58010002david2.4自身连接自身连接是指同一个表自己与自己进行连接。这种一元连接通

15、常用于从自反关系（也称作递归关系）中抽取数据。例如人力资源数据库中雇员与老板的关系。下面例子是在机构表中查找本机构和上级机构的信息。selects.inst_nosuperiornst,s.inst_namesup_inst_name,i.inst_no,i.inst_namefromt_institutionijoint_institutionsoni.superior_inst=s.inst_no结果是:superiornstsup_inst_nameinst_noinst_name800广州市5801天河区800广州市5802越秀区800广州市5803白云区2.5交叉（无限制）连接交叉连

16、接用于对两个源表进行纯关系代数的乘运算。它不使用连接条件来限制结果集合，而是将分别来自两个数据源中的行以所有可能的方式进行组合。数据集合中一的每个行都要与数据集合二中的每一个行分别组成一个新的行。例如，如果第一个数据源中有5个行，而第二个数据源中有4个行，那么在它们之间进行交叉连接就会产生20个行。人们将这种类型的结果集称为笛卡尔乘积。大多数交叉连接都是由于错误操作而造成的；但是它们却非常适合向数据库中填充例子数据，或者预先创建一些空行以便为程序执行期间所要填充的数据保留空间。select*fromt_institutionicrossjoint_tellert在交叉连接中没有on条件子句AP

17、PENDIX3.1A参考资料与资源MicrosoftSQLServerp2000BilePaulNielsenPaulNielsen的Web站点purl HYPERLINK http:/www.is www.is/url3.2注文章所有SQL在IBMInformixDynamicServerVersion9.40.TC2E1测试通过表A记录如下:aIDaNuma20050111a20050112a20050113a20050114a20050115表B记录如下：bIDbName200603240120060324022006032403200603240482006032408实验如下：l.l

18、eftjoinsql语句如下：select*fromAleftjoinBonA.alD=B.blD结果如下：aIDaNumbIDbName1a20050111120060324012a20050112220060324023a20050113320060324034a20050114420060324045a20050115NULLNULL(所影响的行数为5行)结果说明：leftjoin是以A表的记录为基础的,A可以看成左表,B可以看成右表,leftjoin是以左表为准的.换句话说，左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例子中为：A.alD=B.blD).B

19、表记录不足的地方均为NULL.2.rightjoinsql语句如下：select*fromArightjoinBonA.aID=B.bID结果如下:aIDaNumbIDbName1a20050111120060324012a20050112220060324023a20050113320060324034a2005011442006032404NULLNULL82006032408(所影响的行数为5行)结果说明：仔细观察一下，就会发现，和leftjoin的结果刚好相反,这次是以右表(B)为基础的,A表不足的地方用NULL填充.3.innerjoinsql语句如下：select*fromAinn

20、erjoinBonA.alD=B.blD结果如下：aIDaNumbIDbName1a20050111120060324012a20050112220060324023a20050113320060324034a2005011442006032404结果说明：很明显，这里只显示出了A.alD=B.blD的记录.这说明innerjoin并不以谁为基础,它只显示符合条件的记录.以下为网上的一点资料LEFTJOIN操作用于在任何的FROM子句中，组合来源表的记录。使用LEFTJOIN运算来创建一个左边外部联接。左边外部联接将包含了从第一个（左边）开始的两个表中的全部记录，即使在第二个（右边）表中并没有

21、相符值的记录。语法：FROMtable1LEFTJOINtable2ONtable1.field1compoprtable2.field2说明：table1,table2参数用于指定要将记录组合的表的名称。fieldl,field2参数指定被联接的字段的名称。且这些字段必须有相同的数据类型及包含相同类型的数据，但它们不需要有相同的名称。compopr参数指定关系比较运算符：=,=或。如果在INNERJOIN操作中要联接包含Memo数据类型或OLEObject数据类型数据的字段，将会发生错误。内连接INNERJOIN:只显示多表之间与关联条件相匹配的列.外连接:LEFTJOIN:以左表为基础,显

22、示左表中的所有列,不管是否与关联条件相匹配,而右表中的数据只显示与关联条件相匹配的列，不匹配的列以NULL字符填充.RIGHTJOIN:以右表为基础，显示右表中的所有列，不管是否与关联条件相匹配,而左表中的数据只显示与关联条件相匹配的列，不匹配的列以NULL字符填充.FULLJOIN:显示多个表中的所有的列，不匹配关联条件的列以NULL字符填充.内连接INNERJOIN:只显示多表之间与关联条件相匹配的列.外连接:LEFTJOIN:以左表为基础，显示左表中的所有列，不管是否与关联条件相匹配，而右表中的数据只显示与关联条件相匹配的列，不匹配的列以NULL字符填充.RIGHTJOIN:以右表为基础

23、，显示右表中的所有列,不管是否与关联条件相匹配，而左表中的数据只显示与关联条件相匹配的列，不匹配的列以NULL字符填充.FULLJOIN:显示多个表中的所有的列，不匹配关联条件的列以NULL字符填充.WHERE子句中使用的连接语句，在数据库语言中，被称为隐性连接。INNERJOINON子句产生的连接称为显性连接。（其他JOIN参数也是显性连接）WHERE和INNERJOIN产生的连接关系，没有本质区别，结果也一样。但是！隐性连接随着数据库语言的规范和发展，已经逐渐被淘汰，比较新的数据库语言基本上已经抛弃了隐性连接，全部采用显性连接了。一般要使得数据库查询语句性能好点遵循一下原则:在做表与表的连

24、接查询时，大表在前，小表在后不使用表别名，通过字段前缀区分不同表中的字段查询条件中的限制条件要写在表连接条件前尽量使用索引的字段做为查询条件语法格式：其实INNERJOINON的语法格式可以概括为：FROM（表1INNERJOIN表2ON表1.字段号二表2.字段号）INNERJOIN表3ON表1.字段号二表3.字段号）INNERJOIN表4ONMember.字段号二表4.字段号）INNERJOIN表XONMember.字段号二表X.字段号您只要套用该格式就可以了。现成格式范例：虽然我说得已经比较明白了，但为照顾初学者，我还是以本会员注册系统为例，提供一些现成的语法格式范例，大家只要修改其中的数

25、据表名称和字段名称即可。连接两个数据表的用法：FROMMemberINNERJOINMemberSortONMember.MemberSort=MemberSort.MemberSort语法格式可以概括为：FROM表1INNERJOIN表2ON表1.字段号=表2.字段号连接三个数据表的用法：FROM(MemberINNERJOINMemberSortONMember.MemberSort=MemberSort.MemberSort)INNERJOINMemberLevelONMember.MemberLevel=MemberLevel.MemberLevel语法格式可以概括为：FROM(表1I

26、NNERJOIN表2ON表1.字段号二表2.字段号)INNERJOIN表3ON表1.字段号=表3.字段号连接四个数据表的用法：FROM(MemberINNERJOINMemberSortONMember.MemberSort=MemberSort.MemberSort)INNERJOINMemberLevelONMember.MemberLevel=MemberLevel.MemberLevel)INNERJOINMemberIdentityONMember.MemberIdentity=MemberIdentity.MemberIdentity语法格式可以概括为：FROM(表1INNERJO

27、IN表2ON表1.字段号二表2.字段号)INNERJOIN表3ON表1.字段号二表3.字段号)INNERJOIN表4ONMember.字段号二表4.字段号连接五个数据表的用法：FROM(MemberINNERJOINMemberSortONMember.MemberSort=MemberSort.MemberSort)INNERJOINMemberLevelONMember.MemberLevel=MemberLevel.MemberLevel)INNERJOINMemberIdentityONMember.MemberIdentity=MemberIdentity.MemberIdentit

28、y)INNERJOINWedlockONMember.Wedlock=Wedlock.Wedlock语法格式可以概括为：FROM(表1INNERJOIN表2ON表1.字段号二表2.字段号)INNERJOIN表3ON表1.字段号二表3.字段号)INNERJOIN表4ONMember.字段号二表4.字段号)INNERJOIN表5ONMember.字段号二表5.字段号外联接。外联接可以是左向外联接、右向外联接或完整外部联接。在FROM子句中指定外联接时，可以由下列几组关键字中的一组指定LEFTJOIN或LEFTOUTERJOIN。左向外联接的结果集包括LEFTOUTER子句中指定的左表的所有行，而不

29、仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。RIGHTJOIN或RIGHTOUTERJOIN。右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。FULLJOIN或FULLOUTERJOIN。完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。仅当至少有一个同属于两表的行符合联接条件时，内联接才返回行。内联接消除与另一个表中的任何行不匹配的行。而外联接会返回FROM子句中提

30、到的至少一个表或视图的所有行，只要这些行符合任何WHERE或HAVING搜索条件。将检索通过左向外联接引用的左表的所有行，以及通过右向外联接引用的右表的所有行。完整外部联接中两个表的所有行都将返回。连接分为三种：内连接、外连接、交叉连接2.内连接(INNERJOIN)：分为三种：等值连接、自然连接、不等连接5.外连接(OUTERJOIN):分为三种：左外连接(LEFTOUTERJOIN或LEFTJOIN)右外连接(RIGHTOUTERJOIN或RIGHTJOIN)全外连接(FULLOUTERJOIN或FULLJOIN)11.12交叉连接(CROSSJOIN):13.没有WHERE子句，它返回连

31、接表中所有数据行的笛卡尔积-使用标准的SQL语法，匹配条件出现在on子句中，搜索条件出现在where子句,这样使得查询更容易理解-不太推荐使用等值查询SQL左外连接，右外连接，全连接，内连接连接条件可在FROM或WHERE子句中指定，建议在FROM子句中指定连接条件。WHERE和HAVING子句也可以包含搜索条件，以进一步筛选连接条件所选的行。连接可分为以下几类：内连接。（典型的连接运算，使用像=或之类的比较运算符）。包括相等连接和自然连接。内连接使用比较运算符根据每个表共有的列的值匹配两个表中的行。例如，检索students和courses表中学生标识号相同的所有行。外连接。外连接可以是左向

32、外连接、右向外连接或完整外部连接。在FROM子句中指定外连接时，可以由下列几组关键字中的一组指定：LEFTJOIN或LEFTOUTERJOIN。左向外连接的结果集包括LEFTOUTER子句中指定的左表的所有行，而不仅仅是连接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。RIGHTJOIN或RIGHTOUTERJOIN。右向外连接是左向外连接的反向连接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。FULLJOIN或FULLOUTERJOIN。完整外部连接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则

33、另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。交叉连接。交叉连接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉连接也称作笛卡尔积。例如，下面的内连接检索与某个出版商居住在相同州和城市的作者：USEpub&selecta-auarne,乱刖name,p.pubnameFROMauthorsASaINNEROpublishersASpONa.cf=p.cityANDa.state-p.&tateORDERBYa.au_lnameASC;a.au_fnameASCFROM子句中的表或视圈可通过内连接或完整外部连接按任園殖扌靛旦是，用左或右向外连接指定

34、未或视團时表或视團旳顷序很重爲有关淀用左诟向外连接朋I俵的宦梦信息，请参见便用外连瓠例子：诽idname時idjobpa.rent_id1张312312李因23423王武3344m.id同parent_id存在并系seletta*.bfromainnerjoinbonaid-Dparenl_id结果是1张312312李匹2342左连接selecta*.b*fromaleftjoinioraid=t.parentjdselectat*fromaleft|oinbonaJd=b.parentjd123423山12nJR武弧李+12_3右连接seieda.rcfromarighijoinoona.i

35、(J=Dpsrenija结果是1张32李四null芫全连接selectaT,b.*troiralulljoinb&na.iJ=b.paiemt_id结軀1张？2李四231342334null注意上面FULLjoin的查询结果SQLcodeDECLARETATABLE(IDAINT,VAVARCHAR(10)DECLARETBTABLE(IDBINT,VBVARCHAR(10)INSERTINTOTASELECTAAUNIONSELECTBCUNIONSELECTCCCINSERTINTOTBSELECT2UNIONSELECT58UNIONSELECT67内联接简单写法SELECTAIDA,A

36、VA,B.IDB,B.VBFROMTAA,TBBWHEREA.IDA=B.IDB-内联接SELECTA.IDA,A.VA,B.IDB,B.VBFROMTAAINNERJOINTBBONA.IDA=B.IDBSELECTA.IDA,A.VA,B.IDB,B.VBFROMTAAJOINTBBONA.IDA=B.IDB-左外联接SELECTA.IDA,A.VA,B.IDB,B.VBFROMTAALEFTJOINTBBONA.IDA=B.IDBSELECTA.IDA,A.VA,B.IDB,B.VBFROMTAALEFTOUTERJOINTBBONA.IDA=B.IDB-右外联接SELECTA.IDA,

37、A.VA,B.IDB,B.VBFROMTAARIGHTJOINTBBONA.IDA=B.IDBSELECTA.IDA,A.VA,B.IDB,B.VBFROMTAARIGHTOUTERJOINTBBONA.IDA=B.IDB-完整外联接SELECTA.IDA,A.VA,B.IDB,B.VBFROMTAAFULLJOINTBBONA.IDA=B.IDBSELECTA.IDA,A.VA,B.IDB,B.VBFROMTAAFULLOUTERJOINTBBONA.IDA=B.IDB-交叉联接SELECTA.IDA,A.VA,B.IDB,B.VBFROMTAACROSSJOINTBB-自联接SELECTA

38、.IDA,A.VA,B.IDA,B.VAFROMTAA,TABWHEREA.IDA=B.IDA+1查询分析器中执行：一建表tablel,table2:createtabletable1(idint,namevarchar(10)createtabletable2(idint,scoreint)insertintotablelselectl,leeinsertintotablelselect2,zhanginsertintotablelselect4,wanginsertintotable2selectl,90insertintotable2select2,l00insertintotable2

39、select3,70如表tableltable2idnameidscone1lee1502zhang21DD4wang370以下均在查询分析器中执行一、外连接概念：包括左向外联接、右向外联接或完整外部联接左连接:leftjoin或leftouterjoin(1)左向外联接的结果集包括LEFTOUTER子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值(null)。sql语句select*fromtable1leftjointable2ontable1.id=table2.id结果idnameidscone：1；lee1so2zhang21DD4vwangNULLNULL注释：包含table1的所有子句，根据指定条件返回table2相应的字段，不符合的以null显