2024年3月20日发(作者：)
数据库 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@*********************=Bb.a
--右：
select*from@**********************=Bb.a
--内
select*from@**********************=Bb.a
--外：
select*from@*********************=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 1 15
2 b 2 50
3 c
select * from a inner join b on =
这个语法是连接查询中的内连接，它产生的结果是
两个表相匹配的记录出现在结果列表中。
根据上面的表，出现的结果是这样的
name stock
1 a 1 15
2 b 2 50
----------------------------
select * from a,b where =
这个语法是内连接的另外一种写法，其执行结果与inner join 一样
--------------------------------
select * from a left/right join b on =
这个是外连接语法中的左外连接或右外连接
如果是左外连接的话，它将显示ａ表的所有记录，
select a.*,b.* from a left join b on =
查询的结果是这样的：
name stock
1 a 1 15
2 b 2 50
3 c null null
--------------------------------------------
如果是右外连接的话，它将显示ｂ表的所有记录，
select a.*,b.* from a right join b on =
查询的结果是这样的：
name stock
1 a 1 15
2 b 2 50
--
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 关系代数
合并数据集合的理论基础是关系代数，它是由于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）
内连接是最常见的一种连接，它页被称为普通连接，而最早称之为自然连接。
下面是ANSI SQL－92标准
select *
from t_institution i
inner join t_teller t
on _no = _no
where _no = "5801"
其中inner可以省略。
等价于早期的连接语法
select *
from t_institution i, t_teller t
where _no = _no
and _no = "5801"
2.3 外连接
2.3.1 左外连接(Left Outer Jion)
select *
from t_institution i
left outer join t_teller t
on _no = _no
其中outer可以省略。
2.3.2 右外连接(Rigt Outer Jion)
select *
from t_institution i
right outer join t_teller t
on _no = _no
2.3.3 全外连接(Full Outer)
全外连接返回参与连接的两个数据集合中的全部数据，无论它们是否具有与之相匹配的行。在
功能上，它等价于对这两个数据集合分别进行左外连接和右外连接，然后再使用消去重复行的并
操作将上述两个结果集合并为一个结果集。
在现实生活中，参照完整性约束可以减少对于全外连接的使用，一般情况下左外连接就足够了。
在数据库中没有利用清晰、规范的约束来防范错误数据情况下，全外连接就变得非常有用了，你
可以使用它来清理数据库中的数据。
select *
from t_institution i
full outer join t_teller t
on _no = _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 _no = _no
and _no = “5801”
结果是：
inst_no inst_name inst_no teller_no teller_name
5801 天河区 5801 0001 tom
5801 天河区 5801 0002 david
5802 越秀区
5803 白云区
条件在where子句
select *
from t_institution i
left outer join t_teller t
on _no = _no
where _no = “5801”
结果是：
inst_no inst_name inst_no teller_no teller_name
5801 天河区 5801 0001 tom
5801 天河区 5801 0002 david
2.4 自身连接
自身连接是指同一个表自己与自己进行连接。这种一元连接通常用于从自反关系（也称作递归
关系）中抽取数据。例如人力资源数据库中雇员与老板的关系。
下面例子是在机构表中查找本机构和上级机构的信息。
select _no superior_inst, _name sup_inst_name, _no, _name
from t_institution i
join t_institution s
on or_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][/url]
3.2 注文章所有SQL在IBM Informix Dynamic Server Version 2E1测试通过
表A记录如下：
aID aNum
1 a20050111
2 a20050112
3 a20050113
4 a20050114
5 a20050115
表B记录如下:
bID bName
1 2006032401
2 2006032402
3 2006032403
4 2006032404
8 2006032408
实验如下:
join
sql语句如下:
select * from A
left join B
on =
结果如下:
aID aNum bID bName
1 a20050111 1 2006032401
2 a20050112 2 2006032402
3 a20050113 3 2006032403
4 a20050114 4 2006032404
5 a20050115 NULL NULL
（所影响的行数为 5 行）
结果说明:
left join是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准
的.
换句话说,左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例子中
为: = ).
B表记录不足的地方均为NULL.
join
sql语句如下:
select * from A
right join B
on =
结果如下:
aID aNum bID bName
1 a20050111 1 2006032401
2 a20050112 2 2006032402
3 a20050113 3 2006032403
4 a20050114 4 2006032404
NULL NULL 8 2006032408
（所影响的行数为 5 行）
结果说明:
仔细观察一下,就会发现,和left join的结果刚好相反,这次是以右表(B)为基础的,A表不足
的地方用NULL填充.
join
sql语句如下:
select * from A
innerjoin B
on =
结果如下:
aID aNum bID bName
1 a20050111 1 2006032401
2 a20050112 2 2006032402
3 a20050113 3 2006032403
4 a20050114 4 2006032404
结果说明:
很明显,这里只显示出了 = 的记录.这说明inner join并不以谁为基础,它只显
示符合条件的记录.
－－－－－－－－－－－－－－－－－［以下为网上的一点资料］－－－－－－－－－－－－
－－－－－－
••••LEFT JOIN操作用于在任何的 FROM 子句中，组合来源表的记录。使用 LEFT JOIN 运
算来创建一个左边外部联接。左边外部联接将包含了从第一个（左边）开始的两个表中的全部记
录，即使在第二个（右边）表中并没有相符值的记录。
••••语法：FROM table1 LEFT JOIN table2 ON 1 compopr 2
••••说明：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
Sort=Sort
语法格式可以概括为：
FROM 表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号
连接三个数据表的用法：
FROM (Member INNER JOIN MemberSort ON
Sort=Sort) INNER JOIN MemberLevel ON
Level=Level
语法格式可以概括为：
FROM (表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN
表3 ON 表1.字段号=表3.字段号
连接四个数据表的用法：
FROM ((Member INNER JOIN MemberSort ON
Sort=Sort) INNER JOIN MemberLevel ON
Level=Level) INNER JOIN MemberIdentity
ON Identity=Identity
语法格式可以概括为：
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
Sort=Sort) INNER JOIN MemberLevel ON
Level=Level) INNER JOIN MemberIdentity
ON Identity=Identity) INNER JOIN
Wedlock ON k=k
语法格式可以概括为：
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 ,,, FROM @TA A,@TB B
WHERE =
--内联接
SELECT ,,, FROM @TA A INNER JOIN @TB B
ON =
SELECT ,,, FROM @TA A JOIN @TB B
ON =
--左外联接
SELECT ,,, FROM @TA A LEFT JOIN @TB B
ON =
SELECT ,,, FROM @TA A LEFT OUTER JOIN @TB B
ON =
--右外联接
SELECT ,,, FROM @TA A RIGHT JOIN @TB B
ON =
SELECT ,,, FROM @TA A RIGHT OUTER JOIN @TB B
ON =
--完整外联接
SELECT ,,, FROM @TA A FULL JOIN @TB B
ON =
SELECT ,,, FROM @TA A FULL OUTER JOIN @TB B
ON =
--交叉联接
SELECT ,,, FROM @TA A CROSS JOIN @TB B
--自联接
SELECT ,,, FROM @TA A,@TA B WHERE =+1
查询分析器中执行：
--建表table1,table2：
create table table1(id int,name varchar(10))
create table table2(id int,score int)
insert into table1 select 1,'lee'
insert into table1 select 2,'zhang'
insert into table1 select 4,'wang'
insert into table2 select 1,90
insert into table2 select 2,100
insert into table2 select 3,70
如表
-------------------------------------------------
table1 table2
-------------------------------------------------
以下均在查询分析器中执行
一、外连接
1.概念：包括左向外联接、右向外联接或完整外部联接
2.左连接：left join 或 left outer join
(1)左向外联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行，而不仅仅是联接
列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有
选择列表列均为空值(null)。
(2)sql语句
select * from table1 left join table2 on =
-------------结果-------------
------------------------------
注释：包含table1的所有子句，根据指定条件返回table2相应的字段，不符合的以null显示
3.右连接：right join 或 right outer join
(1)右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中
没有匹配行，则将为左表返回空值。
(2)sql语句
select * from table1 right join table2 on =
-------------结果-------------
------------------------------
注释：包含table2的所有子句，根据指定条件返回table1相应的字段，不符合的以null显示
4.完整外部联接:full join 或 full outer join
(1)完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一
个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。
(2)sql语句
select * from table1 full (OUTER) join table2 on =
-------------结果-------------
------------------------------
注释：返回左右连接的和（见上左、右连接）
二、内连接
1.概念：内联接是用比较运算符比较要联接列的值的联接
2.内连接：join 或 inner join
语句
select * from table1 join table2 on =
等价（与下列执行效果相同）
A:select a.*,b.* from table1 a,table2 b where =
B:
select * from table1 cross join table2 where =
(注：
cross join后加条件只能用where,不能用on)
-------------结果-------------
------------------------------
注释：只返回符合条件的table1和table2的列
三、交叉连接(完全)
1.概念：没有 WHERE 子句的交叉联接将产生联接所涉及的表的笛卡尔积。第一个表的行
数乘以第二个表的行数等于笛卡尔积结果集的大小。（table1和table2交叉连接产生3*3=9条记
录）
2.交叉连接：cross join (不带条件)
语句
select * from table1 cross join table2
-------------结果-------------
------------------------------
注释：返回3*3=9条记录，即笛卡尔积
4.等价（与下列执行效果相同）
A:select * from table1,table2
select * from table2, table1 则相反,结果如下：
发布评论