sql 各种连接方式的区别

数据库 inner join ，left join，right join 的区别

inner join：内连接，结果只包含满足条件的列。

left join：左外连接，结果包含满足条件的行及左侧表中的全部行。

right join ：右外连接，结果包含满足条件的行及右侧表中的全部行。SQL语句(inner join,left out join,right out join)

left join :左连接，返回左表中所有的记录以及右表中连接字段相等的记录。right join :右连接，返回右表中所有的记录以及左表中连接字段相等的记录。

inner join: 内连接，又叫等值连接，只返回两个表中连接字段相等的行。

full join:外连接，返回两个表中的行：left join + right join

cross join:结果是笛卡尔积，就是第一个表的行数乘以第二个表的行数。

declare @a table(a int,b int)

declare @b table(a int,b int)

insert @a values(1,1)

insert @a values(2,2)

insert @b values(1,1)

insert @b values(3,3)

select * from @a

select * from @b

--左:

select * from @a Aa left join @b Bb on Aa.a=Bb.a

--右：

select * from @a Aa right join @b Bb on Aa.a=Bb.a

--内

select * from @a Aa inner join @b Bb on Aa.a=Bb.a

--外：

select * from @a Aa full join @b Bb on Aa.a=Bb.a

--交叉连接

select * from @a cross join @b

left 以左边为准右边有则显示无则NULL

right反之

inner只取都不null的，相当于用from a,b where ？=？(连接是on ? = ?) left join 和left outer join 的区别

通俗的讲：

A left join

B 的连接的记录数与A表的记录数同

A right join

B 的连接的记录数与B表的记录数同

A left join

B 等价B right join A

举个例子：

假设ａ表和ｂ表的数据是这样的。

a b

id name id stock

1 a 115

2 b 250

3 c

select*from a inner join b on a.id=b.id

这个语法是连接查询中的内连接，它产生的结果是

两个表相匹配的记录出现在结果列表中。

根据上面的表，出现的结果是这样的

a.id name

b.id stock

1 a 115

2 b 250

----------------------------

select*from a,b where a.id=b.id

这个语法是内连接的另外一种写法，其执行结果与inner join一样

--------------------------------

select*from a left/right join b on a.id=b.id

这个是外连接语法中的左外连接或右外连接

如果是左外连接的话，它将显示ａ表的所有记录，

select a.*,b.*from a left join b on a.id=b.id

查询的结果是这样的：

a.id name

b.id stock

1 a 115

2 b 250

3 c null null

--------------------------------------------

如果是右外连接的话，它将显示ｂ表的所有记录，

select a.*,b.*from a right join b on a.id=b.id

查询的结果是这样的：

a.id name

b.id stock

1 a 115

2 b 250

--

select a.*,b.*from a left join b on a.k = b.k

select a.*,b.*from a left outer join b on a.k =b.k

----------上面两种一样left join是left outer join的简写

select a.*,b.*from a left inner join b on a.k = b.k

没有这种写法，错误的语句.

--

在你要使用多个left join的时候

比如说10个

我们把10个全都写成left join的形式

然后再SQL让他自动运行一下，它会把最后一次出现的left join变成left outer join

所以依此推理，最后一个left join会以left outer join的形式存在

当然，不管变不变对结果的显示没有任何影响

希望我的实验能对你有所帮助

--

使用关系代数合并数据

1关系代数

合并数据集合的理论基础是关系代数，它是由E.F.Codd于1970年提出的。

在关系代数的形式化语言中：

用表、或者数据集合表示关系或者实体。ρ

用行表示元组。ρ

用列表示属性。ρ

关系代数包含以下8个关系运算符

选取――返回满足指定条件的行。ρ

投影――从数据集合中返回指定的列。ρ

笛卡尔积――是关系的乘法，它将分别来自两个数据集合中的行以所有可能的方式进行组合。ρ

并――关系的加法和减法，它可以在行的方向上合并两个表中的数据，就像把一个表垒在另一个表之上一样。ρ

交――返回两个数据集合所共有的行。ρ

差――返回只属于一个数据集合的行。ρ

连接――在水平方向上合并两个表，其方法是：将两个表中在共同数据项上相互匹配的那些行合并起来。ρ

除――返回两个数据集之间的精确匹配。ρ

此外，作为一种实现现代关系代数运算的方法，SQL还提供了：

子查询――类似于连接，但更灵活；在外部查询中，方式可以使用表达式、列表或者数据集合的地方都可以使用子查询的结果。ρ

本章将主要讲述多种类型的连接、简单的和相关的子查询、几种类型的并、关系除以及其他的内容。

2使用连接

2.1连接类型

在关系代数中，连接运算是由一个笛卡尔积运算和一个选取运算构成的。首先用笛卡尔积完成对两个数据集合的乘运算，然后对生成的结果集合进行选取运算，确保只把分别来自两个数据集合并且具有重叠部分的行合并在一起。连接的全部意义在于在水平方向上合并两个数据集合（通

常是表），并产生一个新的结果集合，其方法是将一个数据源中的行于另一个数据源中和它匹配的行组合成一个新元组。

SQL提供了多种类型的连接方式，它们之间的区别在于：从相互交叠的不同数据集合中选择用于连接的行时所采用的方法不同。

连接类型定义

内连接只连接匹配的行

左外连接包含左边表的全部行（不管右边的表中是否存在与它们匹配的行），以及右边表中全部匹配的行

右外连接包含右边表的全部行（不管左边的表中是否存在与它们匹配的行），以及左边表中全部匹配的行

全外连接包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行。

(H)(theta)连接使用等值以外的条件来匹配左、右两个表中的行

交叉连接生成笛卡尔积－它不使用任何匹配或者选取条件，而是直接将一个数据源中的每个行与另一个数据源的每个行都一一匹配

在INFORMIX中连接表的查询

如果FROM子句指定了多于一个表引用，则查询会连接来自多个表的行。连接条件指定各列之间（每个表至少一列）进行连接的关系。因为正在比较连接条件中的列，所以它们必须具有一致的数据类型。

SELECT语句的FROM子句可以指定以下几种类型的连接

FROM子句关键字相应的结果集

CROSS JOIN笛卡尔乘积（所有可能的行对）

INNER JOIN仅对满足连接条件的CROSS中的列

LEFT OUTER JOIN一个表满足条件的行，和另一个表的所有行

RIGHT OUTER JOIN与LEFT相同，但两个表的角色互换

FULL OUTER JOIN LEFT OUTER和RIGHT OUTER中所有行的超集

2.2内连接（Inner Join）

内连接是最常见的一种连接，它页被称为普通连接，而E.FCodd最早称之为自然连接。

下面是ANSI SQL－92标准

select*

from t_institution i

inner join t_teller t

on i.inst_no = t.inst_no

where i.inst_no = "5801"

其中inner可以省略。

等价于早期的连接语法

select*

from t_institution i, t_teller t

where i.inst_no = t.inst_no

and i.inst_no = "5801"

2.3外连接

2.3.1左外连接(Left Outer Jion)

select*

from t_institution i

left outer join t_teller t

on i.inst_no = t.inst_no

其中outer可以省略。

2.3.2右外连接(Rigt Outer Jion)

select*

from t_institution i

right outer join t_teller t

on i.inst_no = t.inst_no

2.3.3全外连接(Full Outer)

全外连接返回参与连接的两个数据集合中的全部数据，无论它们是否具有与之相匹配的行。在功能上，它等价于对这两个数据集合分别进行左外连接和右外连接，然后再使用消去重复行的并操作将上述两个结果集合并为一个结果集。

在现实生活中，参照完整性约束可以减少对于全外连接的使用，一般情况下左外连接就足够了。在数据库中没有利用清晰、规范的约束来防范错误数据情况下，全外连接就变得非常有用了，你可以使用它来清理数据库中的数据。

select*

from t_institution i

full outer join t_teller t

on i.inst_no = t.inst_no

2.3.4外连接与条件配合使用

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

条件在join子句

select*

from t_institution i

left outer join t_teller t

on i.inst_no = t.inst_no

and i.inst_no =“5801”

结果是：

inst_no inst_name inst_no teller_no teller_name

5801天河区58010001 tom

5801天河区58010002 david

5802越秀区

5803白云区

条件在where子句

select*

from t_institution i

left outer join t_teller t

on i.inst_no = t.inst_no

where i.inst_no =“5801”

结果是：

inst_no inst_name inst_no teller_no teller_name

5801天河区58010001 tom

5801天河区58010002 david

2.4自身连接

自身连接是指同一个表自己与自己进行连接。这种一元连接通常用于从自反关系（也称作递归关系）中抽取数据。例如人力资源数据库中雇员与老板的关系。

下面例子是在机构表中查找本机构和上级机构的信息。

select s.inst_no superior_inst, s.inst_name sup_inst_name, i.inst_no, i.inst_name

from t_institution i

join t_institution s

on i.superior_inst = s.inst_no

结果是：

superior_inst sup_inst_name inst_no inst_name

800广州市5801天河区

800广州市5802越秀区

800广州市5803白云区

2.5交叉(无限制) 连接

交叉连接用于对两个源表进行纯关系代数的乘运算。它不使用连接条件来限制结果集合，而是将分别来自两个数据源中的行以所有可能的方式进行组合。数据集合中一的每个行都要与数据集合二中的每一个行分别组成一个新的行。例如，如果第一个数据源中有5个行，而第二个数据源中有4个行，那么在它们之间进行交叉连接就会产生20个行。人们将这种类型的结果集称为笛卡尔乘积。

大多数交叉连接都是由于错误操作而造成的；但是它们却非常适合向数据库中填充例子数据，或者预先创建一些空行以便为程序执行期间所要填充的数据保留空间。

select*

from t_institution i

cross join t_teller t

在交叉连接中没有on条件子句

3 APPENDIX

3.1 A 参考资料与资源

《Microsoft SQL Server ρ2000 Bile》Paul Nielsen

Paul Nielsen的Web站点ρ

[url]https://www.360docs.net/doc/8d15013707.html,[/url]

3.2注文章所有SQL在IBM Informix Dynamic Server Version 9.40.TC2E1测试通过表A记录如下：

aID aNum

1 a20050111

2 a20050112

3 a20050113

4 a20050114

5 a20050115

表B记录如下:

bID bName

12006032401

22006032402

32006032403

42006032404

82006032408

实验如下:

1.left join

sql语句如下:

select*from A

left join B

on A.aID = B.bID

结果如下:

aID aNum bID bName

1 a20050111 12006032401

2 a20050112 22006032402

3 a20050113 32006032403

4 a20050114 42006032404

5 a20050115 NULL NULL

（所影响的行数为5行）

结果说明:

left join是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的.

换句话说,左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例子中为: A.aID = B.bID).

B表记录不足的地方均为NULL.

2.right join

sql语句如下:

select*from A

right join B

on A.aID = B.bID

结果如下:

aID aNum bID bName

1 a20050111 12006032401

2 a20050112 22006032402

3 a20050113 32006032403

4 a20050114 42006032404

NULL NULL82006032408

（所影响的行数为5行）

结果说明:

仔细观察一下,就会发现,和left join的结果刚好相反,这次是以右表(B)为基础的,A表不足的地方用NULL填充.

3.inner join

sql语句如下:

select*from A

innerjoin B

on A.aID = B.bID

结果如下:

aID aNum bID bName

1 a20050111 12006032401

2 a20050112 22006032402

3 a20050113 32006032403

4 a20050114 42006032404

结果说明:

很明显,这里只显示出了 A.aID = B.bID的记录.这说明inner join并不以谁为基础,它只显示符合条件的记录.

－－－－－－－－－－－－－－－－－［以下为网上的一点资料］－－－－－－－－－－－－－－－－－－

????LEFT JOIN操作用于在任何的FROM子句中，组合来源表的记录。使用LEFT JOIN运算来创建一个左边外部联接。左边外部联接将包含了从第一个（左边）开始的两个表中的全部记录，即使在第二个（右边）表中并没有相符值的记录。

????语法：FROM table1 LEFT JOIN table2 ON table1.field1 compopr table2.field2

????说明：table1, table2参数用于指定要将记录组合的表的名称。

??????????field1, field2参数指定被联接的字段的名称。且这些字段必须有相同的数据类型及包含相同类型的数据，但它们不需要有相同的名称。

??????????compopr参数指定关系比较运算符："="， "<"， ">"， "<="， ">=" 或 "<>"。

??????????如果在INNER JOIN操作中要联接包含Memo 数据类型或 OLE Object 数据类型数据的字段，将会发生错误。

内连接

INNER JOIN:只显示多表之间与关联条件相匹配的列.

外连接:

LEFT JOIN :以左表为基础,显示左表中的所有列,不管是否与关联条件相匹配,而右表中的数据只显示与关联条件相匹配的列,不匹配的列以NULL字符填充.

RIGHT JOIN:以右表为基础,显示右表中的所有列,不管是否与关联条件相匹配,而左表中的数据只显示与关联条件相匹配的列,不匹配的列以NULL字符填充.

FULL JOIN :显示多个表中的所有的列,不匹配关联条件的列以NULL字符填充.

内连接

INNER JOIN:只显示多表之间与关联条件相匹配的列.

外连接:

LEFT JOIN :以左表为基础,显示左表中的所有列,不管是否与关联条件相匹配,而右表中的数据只显示与关联条件相匹配的列,不匹配的列以NULL字符填充.

RIGHT JOIN:以右表为基础,显示右表中的所有列,不管是否与关联条件相匹配,而左表中的数据只显示与关联条件相匹配的列,不匹配的列以NULL字符填充.

FULL JOIN :显示多个表中的所有的列,不匹配关联条件的列以NULL字符填充.

WHERE子句中使用的连接语句，在数据库语言中，被称为隐性连接。INNER JOIN……ON子句产生的连接称为显性连接。（其他JOIN参数也是显性连接）

WHERE 和INNER JOIN产生的连接关系，没有本质区别，结果也一样。但是！隐性连接随着数据库语言的规范和发展，已经逐渐被淘汰，比较新的数据库语言基本上已经抛弃了隐性连接，全部采用显性连接了。

一般要使得数据库查询语句性能好点遵循一下原则:

在做表与表的连接查询时，大表在前，小表在后

不使用表别名，通过字段前缀区分不同表中的字段

查询条件中的限制条件要写在表连接条件前

尽量使用索引的字段做为查询条件

语法格式：

其实INNER JOIN ……ON的语法格式可以概括为：

FROM (((表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号) INNER JOIN 表4 ON Member.字段号=表4.字段号) INNER JOIN 表X ON Member.字段号=表X.字段号

您只要套用该格式就可以了。

现成格式范例：

虽然我说得已经比较明白了，但为照顾初学者，我还是以本会员注册系统为例，提供一些现成的语法格式范例，大家只要修改其中的数据表名称和字段名称即可。

连接两个数据表的用法：

FROM Member INNER JOIN MemberSort ON

Member.MemberSort=MemberSort.MemberSort

语法格式可以概括为：

FROM 表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号

连接三个数据表的用法：

FROM (Member INNER JOIN MemberSort ON

Member.MemberSort=MemberSort.MemberSort) INNER JOIN MemberLevel ON Member.MemberLevel=MemberLevel.MemberLevel

语法格式可以概括为：

FROM (表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号

连接四个数据表的用法：

FROM ((Member INNER JOIN MemberSort ON

Member.MemberSort=MemberSort.MemberSort) INNER JOIN MemberLevel ON Member.MemberLevel=MemberLevel.MemberLevel) INNER JOIN MemberIdentity ON Member.MemberIdentity=MemberIdentity.MemberIdentity

语法格式可以概括为：

FROM ((表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号) INNER JOIN 表4 ON Member.字段号=表4.字段号

连接五个数据表的用法：

FROM (((Member INNER JOIN MemberSort ON

Member.MemberSort=MemberSort.MemberSort) INNER JOIN MemberLevel ON Member.MemberLevel=MemberLevel.MemberLevel) INNER JOIN MemberIdentity ON Member.MemberIdentity=MemberIdentity.MemberIdentity) INNER JOIN Wedlock ON Member.Wedlock=Wedlock.Wedlock

语法格式可以概括为：

FROM (((表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号) INNER JOIN 表4 ON Member.字段号=表4.字段号) INNER JOIN 表5 ON Member.字段号=表5.字段号

外联接。外联接可以是左向外联接、右向外联接或完整外部联接。

在 FROM 子句中指定外联接时，可以由下列几组关键字中的一组指定：

LEFT JOIN 或 LEFT OUTER JOIN。

左向外联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。

RIGHT JOIN 或 RIGHT OUTER JOIN。

右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。

FULL JOIN 或 FULL OUTER JOIN。

完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。

仅当至少有一个同属于两表的行符合联接条件时，内联接才返回行。内联接消除与另一个表中的任何行不匹配的行。而外联接会返回 FROM 子句中提到的至少一个表或视图的所有行，只要这些行符合任何 WHERE 或 HAVING 搜索条件。将检索通过左向外联接引用的左表的所有行，以及通过右向外联接引用的右表的所有行。完整外部联接中两个表的所有行都将返回。

1.连接分为三种：内连接、外连接、交叉连接

2.

3.内连接(INNER JOIN)：

4.分为三种：等值连接、自然连接、不等连接

5.

6.外连接(OUTER JOIN)：

7.分为三种：

8.左外连接(LEFT OUTER JOIN或LEFT JOIN)

9.右外连接(RIGHT OUTER JOIN或RIGHT JOIN)

10. 全外连接(FULL OUTER JOIN或FULL JOIN)

11.

12.交叉连接(CROSS JOIN)：

13. 没有WHERE 子句，它返回连接表中所有数据行的笛卡尔积

->使用标准的SQL语法,匹配条件出现在on子句中,搜索条件出现在where子句,这样使得查询更容易理解

->不太推荐使用等值查询

SQL 左外连接，右外连接，全连接，内连接

连接条件可在FROM或WHERE子句中指定，建议在FROM子句中指定连接条件。WHERE和HAVING子句也可以包含搜索条件，以进一步筛选连接条件所选的行。

连接可分为以下几类：

内连接。（典型的连接运算，使用像 = 或 <>之类的比较运算符）。包括相等连接和自然连接。

内连接使用比较运算符根据每个表共有的列的值匹配两个表中的行。例如，检索 students 和 courses 表中学生标识号相同的所有行。

外连接。外连接可以是左向外连接、右向外连接或完整外部连接。

在FROM子句中指定外连接时，可以由下列几组关键字中的一组指定：

LEFT JOIN 或 LEFT OUTER JOIN。

左向外连接的结果集包括LEFT OUTER子句中指定的左表的所有行，而不仅仅是连接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。

RIGHT JOIN 或 RIGHT OUTER JOIN。

右向外连接是左向外连接的反向连接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。

FULL JOIN 或 FULL OUTER JOIN。

完整外部连接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。

交叉连接。交叉连接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉连接也称作笛卡尔积。

例如，下面的内连接检索与某个出版商居住在相同州和城市的作者：

注意上面 FULL join的查询结果

SQL code

DECLARE

@TA TABLE (IDA INT,VA VARCHAR(10))

DECLARE

@TB TABLE (IDB INT,VB VARCHAR(10))

INSERT INTO @TA

SELECT

1,'AA' UNION SELECT

2,'BC' UNION SELECT

3,'CCC'

INSERT INTO @TB

SELECT

1,'2' UNION SELECT

3,'58' UNION SELECT

4,'67'

--内联接简单写法

SELECT A.IDA,A.VA,B.IDB,B.VB FROM @TA A,@TB B