left join 和 left outer join (可解决多个表left join的问题)

本文详细解析了SQL中的各种连接类型,包括内连接、左外连接、右外连接等,并通过实例对比了不同连接方式的结果差异。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

http://blog.youkuaiyun.com/laolaowhn/archive/2007/09/07/1776219.aspx

通俗的讲:  
  A   
left   join   B   的连接的记录数与A表的记录数同  
  A   
right   join   B   的连接的记录数与B表的记录数同    
  A   
left   join   B   等价B   right   join   A  
   
   
  
table   A:  
  Field_K,   Field_A  
  
1                       a  
  
3                       b  
  
4                       c  
   
  
table   B:  
  Field_K,   Field_B  
  
1                       x  
  
2                       y  
  
4                       z  
   
  
select   a.Field_K,   a.Field_A,   b.Field_K,   b.Field_B  
  
from   a   left   join   b   on   a.Field_K=b.Field_K  
   
  Field_K         Field_A         Field_K         Field_B          
  
----------   ----------   ----------   ----------    
  1                     a                     1                     x                    
  
3                     b                     NULL               NULL  
  
4                     c                     4                     z                    
   
  
select   a.Field_K,   a.Field_A,   b.Field_K,   b.Field_B  
  
from   a   right   join   b   on   a.Field_K=b.Field_K  
   
  Field_K         Field_A         Field_K         Field_B          
  
----------   ----------   ----------   ----------    
  1                     a                     1                     x                    
  
NULL               NULL               2                     y                    
  
4                     c                     4                     z     

  --

举个例子:  
  假设a表和b表的数据是这样的。  
  a                         b    
  id     name  id     stock   
  
1  a             1         15  
  
2         b             2         50  
  
3         c                  
   
  
select   *   from   a   inner   join   b   on   a.id=b.id  
  这个语法是连接查询中的内连接,它产生的结果是  
  两个表相匹配的记录出现在结果列表中。  
  根据上面的表,出现的结果是这样的  
  a.id     name     b.id     stock  
  
1       a             1         15  
  
2             b             2         50  
  
----------------------------  
  select   *   from   a,b   where   a.id=b.id  
  这个语法是内连接的另外一种写法,其执行结果与inner   
join   一样  
   
  
--------------------------------    
   
  
select   *   from   a   left/right   join   b   on   a.id=b.id  
  这个是外连接语法中的左外连接或右外连接  
  如果是左外连接的话,它将显示a表的所有记录,  
  
select   a.*,b.*   from   a   left   join   b   on   a.id=b.id  
  查询的结果是这样的:  
  a.id     name     b.id     stock  
  
1         a         1             15  
  
2               b         2             50  
  
3               c       null         null   
  
--------------------------------------------  
  如果是右外连接的话,它将显示b表的所有记录,  
  
select   a.*,b.*   from   a   right   join   b   on   a.id=b.id  
  查询的结果是这样的:  
  a.id     name     b.id     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 关系代数
合并数据集合的理论基础是关系代数,它是由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       天河区               5801       0001       tom
5801       天河区               5801       0002       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       天河区               5801       0001       tom
5801       天河区               5801       0002       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]www.isnotnull.com[/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
1            2006032401
2           2006032402
3           2006032403
4           2006032404
8           2006032408


实验如下:
1.left join

sql语句如下:
select * from A
left join B
on A.aID = B.bID

结果如下:
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)只会显示符合搜索条件的记录(例子中为: 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         1               2006032401
2            a20050112         2              2006032402
3            a20050113         3              2006032403
4            a20050114         4              2006032404
NULL    NULL                   8              2006032408
(所影响的行数为 
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         1               2006032401
2            a20050112         2              2006032402
3            a20050113         3              2006032403
4            a20050114         4              2006032404

结果说明:
        很明显,这里只显示出了 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 数据类型数据的字段,将会发生错误。
<think>嗯,用户之前已经问过LEFT OUTER JOINLEFT JOIN的区别,现在又再次提问,可能还是想确认是否真的相同,或者有没有特殊情况。首先,我需要回顾之前的回答,确保信息一致。之前的回答强调两者在功能上是完全相同的,只是语法上的差异,用户提供的引用里也提到它们在功能上相同,但不同环境可能有偏好不同。 用户这次的问题可能是在确认之前的结论,或者想更深入理解。需要检查是否有遗漏的情况,比如不同数据库系统是否有例外。例如,某些数据库是否可能对这两个关键字有不同的处理,但根据已知信息,主流数据库如MySQL、Oracle、SQL Server都视它们为相同。Hive中的LEFT SEMI JOIN是不同的,但用户的问题是关于LEFT OUTERLEFT JOIN,所以需要明确区分。 另外,用户可能在实际使用中遇到困惑,比如看到不同文档中有的用OUTER,有的不用,想知道是否会影响查询结果。这时候需要强调OUTER是可选的,不影响执行计划。同时,用户可能关心优化或性能是否有差异,但根据标准,这两者在执行时不会有区别,所以性能也不会受影响。 还要注意用户可能的应用场景,比如他们是否在写跨数据库SQL,需要确保兼容性。这时候可以建议统一使用LEFT JOIN,因为更简洁,且所有数据库都支持。此外,用户可能想了解如何选择何时使用哪个,可能需要给出最佳实践,比如在强调外连接特性时使用OUTER,否则省略。 需要确保回答中再次明确两者的等价性,并通过示例对比来加强理解。同时,引用之前的引用资料,但要注意用户提供的引用12都指出它们是相同的,而引用3提到Hive的LEFT SEMI JOIN不同,但这里不相关。因此,需要避免混淆,明确指出LEFT OUTER JOINLEFT JOIN在Hive中也是等价的,而LEFT SEMI JOIN是另一种操作。 最后,用户可能需要示例来验证,所以在回答中加入具体的SQL示例结果,证明两者输出一致。此外,用户可能想知道如何查找相关文档或进一步学习资源,但问题中没有提到,所以可能不需要,但可以建议参考官方文档以确认。</think>### LEFT OUTER JOINLEFT JOIN 的等价性详解 #### **核心结论** 在 SQL 标准及所有主流数据库(MySQL、Oracle、SQL Server 等)中,$LEFT\ OUTER\ JOIN$ 与 $LEFT\ JOIN$ **完全等价**,二者仅语法形式不同,执行逻辑与结果完全一致[^1][^2][^5]。 以下从多个维度详细说明其等价性及用法: --- #### **1. 语法等价性** - **标准定义** 根据 ANSI SQL 规范,`OUTER` 是可选关键字,$LEFT\ JOIN$ 是 $LEFT\ OUTER\ JOIN$ 的简写形式[^1]。例如: ```sql -- 两种写法等价 SELECT * FROM tableA LEFT JOIN tableB ON ... ; SELECT * FROM tableA LEFT OUTER JOIN tableB ON ... ; ``` - **数据库实现一致性** 所有主流数据库均遵循此标准,无功能差异。例如: - **MySQL 官方文档**:明确指出 `LEFT JOIN` `LEFT OUTER JOIN` 是同一操作的不同名称[^5]。 - **SQL Server**:执行计划完全一致,查询优化器不会因关键字差异产生不同行为[^2]。 --- #### **2. 功能特性** - **保留主全部数据** 无论是 $LEFT\ JOIN$ 还是 $LEFT\ OUTER\ JOIN$,均以左(主)为基准,返回所有左记录,右无匹配时填充 `NULL`[^1][^2]。例如: ```sql -- 返回所有员工及其部门(无部门的员工显示为 NULL) SELECT e.name, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_id = d.id; ``` - **与 INNER JOIN 对比** $INNER\ JOIN$ 仅返回两匹配的记录,而左连接保留主完整数据,这是本质区别[^1]。 --- #### **3. 使用场景与建议** 1. **简写优先原则** 推荐使用 $LEFT\ JOIN$,代码更简洁且无歧义[^4]。例如: ```sql -- 简化写法(推荐) SELECT u.*, o.order_date FROM users u LEFT JOIN orders o ON u.id = o.user_id; ``` 2. **强调“外连接”特性时** 在技术文档或教学中,可使用完整形式 $LEFT\ OUTER\ JOIN$ 以明确操作类型[^3]。 3. **查找缺失关联项** ```sql -- 找出未下过订单的用户 SELECT u.* FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE o.user_id IS NULL; ``` --- #### **4. 实验验证(以 MySQL 为例)** **测试数据**: ```sql CREATE TABLE Students (id INT, name VARCHAR(10)); CREATE TABLE Scores (student_id INT, score INT); INSERT INTO Students VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie'); INSERT INTO Scores VALUES (1, 90), (3, 85); ``` **执行查询**: ```sql -- 使用 LEFT JOIN SELECT s.name, sc.score FROM Students s LEFT JOIN Scores sc ON s.id = sc.student_id; -- 使用 LEFT OUTER JOIN SELECT s.name, sc.score FROM Students s LEFT OUTER JOIN Scores sc ON s.id = sc.student_id; ``` **结果完全一致**: | name | score | |---------|-------| | Alice | 90 | | Bob | NULL | | Charlie | 85 | --- #### **5. 常见误区澄清** - **Hive 中的 LEFT SEMI JOIN** 与左连接不同,$LEFT\ SEMI\ JOIN$ 仅返回左中与右匹配的记录(类似 `IN` 子查询),且不包含右字段[^3]。例如: ```sql -- Hive 示例:仅返回有成绩的学生 SELECT s.* FROM Students s LEFT SEMI JOIN Scores sc ON s.id = sc.student_id; ``` --- #### **总结** - **语法等价**:$LEFT\ JOIN$ 是 $LEFT\ OUTER\ JOIN$ 的简写形式,功能完全一致。 - **使用建议**:优先使用简写以提高代码可读性,特殊场景可强调完整语法。 - **性能无差异**:数据库优化器对两种写法生成相同的执行计划。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值