ORACLE- join,inner join 与 left join, left outer join

本文深入解析SQL中的内连接和外连接,包括语法、结果展示及实际应用案例。

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

表A

ID  bianhao  name
1   1001     name1
2   1002     name2
3   1002     name3
4   1003     name4

表B

ID  bianhao  jibie
1   1001     class1
2   1002     class2

1.内连接:join on 等同与 inner join on

select a.*,b.*

from test a
join test2 b on a.bianhao = b.bianhao

简写
select a.*,b.*

from test a,test2 b
where a.bianhao = b.bianhao

结果

               ID       BIANHAO  NAME              ID       BIANHAO  JIBIE

1            1            1001      name1              1            1001      class1  

2            2            1002      name2              2            1002      class2  

3            3            1002      name3              2            1002      class2  

2.外连接:left join on 等同与left outer join on

select a.*,b.*

 from test a
 left join test2 b on a.bianhao =b.bianhao

结果

               ID       BIANHAO  NAME              ID       BIANHAO  JIBIE

1            1            1001      name1              1            1001      class1  

3            2            1002      name2              2            1002      class2  

2            3            1002      name3              2            1002      class2  

4            4            1003      name4           

 

简写

select a.*,b.*

 from test a,test2 b

where a.bianhao =b.bianhao(+)

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

  说明1:

A表          B表

1               1

2               1

3               2

                 2

将A表B表进行内联(内联不分主表从表):

1

1

2

2

将A表作为主表进行外联:

1

1

2

2

3

将B表作为主表进行外联:

1

1

2

2

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

  说明2:

A表          B表

1               1

1              1

联接后结果:

1

1

1

1

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

  说明3:(+)与left join的链接条件

(1)left join如果将条件写到where后面则将该条件作为了内联条件,这么写table2起不到外联效果

select *

from table1 t1

left join table2 t2 on t1.field1 = t2.field1

where t2.field2 = '001'

+++++

(2)left join如果将条件写到join后面则将该条件作为了外联条件,只有这么写才能完全将table2完全作为从表链接

select *

from table1 t1

left join table2 t2 on t1.field1 = t2.field1

and t2.field2 = '001'

+++++

(3)效果同(1)

select *

from table1 t1, table t2

where t1.field1 = t2.field1(+)

and t2.field2 = '001'

+++++

(4)效果同(2),必须在从表的所有条件上加(+)

select *

from table1 t1, table t2

where t1.field1 = t2.field1(+)

and t2.field2(+) = '001'

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

注:无论是内联还是外联如果有where条件则都是先进行连接查询结果集后再在结果集的基础上进行条件筛选的,如下的sql的查询结果是0条数据。

 selecta.*,b.*

fromtest a
 left join test2 b on a.bianhao =b.bianhao
 where b.bianhao = 1003

=================3表间的内外联关系====================

select *
  from t1,t2,t3
where t1.a = t2.a(+)
   and t2.b = t3.b(+)
t1外连t2外连t3
-------------------------------------------
select *
  from t1,t2,t3
where t1.a = t2.a(+)
   and t2.b = t3.b
并不是t1外连t2内连t3,而是t1,t2,t3内连
-------------------------------------------
select *
  from t1,
         (select t2.a a from t2,t3 where t2.b = t3.b) t4
where t1.a = t4.a(+)
t1外连t2内连t3
======================从表的外联条件以外的条件======================

外联时从表的条件不会影响主表取出的记录数,影响的是从表字段是否能取出。

----------(1)left join-----------
select t1.filed1, t2.filed2
from table1 t1
left join table2 t2
  on t1.filed1 = t2.filed1
----------(2)inner join-----------
select t1.filed1, t2.filed2
from table1 t1
left join table2 t2
  on t1.filed1 = t2.filed1
where t2.filed2 = 'abc'
----------(3)left join-----------
select t1.filed1, t2.filed2
from table1 t1
left join table2 t2
  on t1.filed1 = t2.filed1
 and t2.filed2 = 'abc'


+++++++++table+++++++++
table1
filed1 filed2
------------------
111  1001
112  1002
113  1003

table2
filed1 filed2
------------------
111  abc
112  def

+++++++++result+++++++++
(1)
filed1 filed2
------------------
111  abc
112  def
113  

(2)
filed1 filed2
------------------
111  abc

(3)
filed1 filed2
------------------
111  abc
112  
113  

Oracle SQL 中,`LEFT JOIN` 和 `LEFT OUTER JOIN` 的功能是完全相同的,它们都用于返回左表中的所有记录,即使右表中没有匹配的行。在没有匹配的情况下,右表的字段将显示为 `NULL`。这意味着 `OUTER` 关键字在 `LEFT JOIN` 的上下文中是可选的,它的存在否不会改变查询的行为。因此,`LEFT JOIN` 和 `LEFT OUTER JOIN` 可以互换使用[^2]。 ### LEFT JOINLEFT OUTER JOIN 的行为 当执行 `LEFT JOIN` 或 `LEFT OUTER JOIN` 时,数据库会根据指定的连接条件(通常是 `ON` 子句)从右表中查找匹配的行。如果找到匹配项,则返回对应的行;如果没有找到,则返回左表中的行,并且右表的所有列都设置为 `NULL`。 #### 示例 考虑两个表:`sensor_collect_data`(左表)和 `sensor_collect_region`(右表),它们通过 `region` 字段进行连接。 ```sql SELECT * FROM sensor_collect_data aa LEFT JOIN sensor_collect_region bb ON aa.region = bb.region; ``` 上述查询将返回 `sensor_collect_data` 表中的所有记录,并尝试从 `sensor_collect_region` 表中找到匹配的 `region` 值。如果找不到匹配项,则 `bb` 表中的字段将显示为 `NULL`[^2]。 同样的查询也可以写成: ```sql SELECT * FROM sensor_collect_data aa LEFT OUTER JOIN sensor_collect_region bb ON aa.region = bb.region; ``` 其结果前一个查询完全一致。 ### INNER JOIN OUTER JOIN 的区别 `LEFT JOIN` 不同,`INNER JOIN` 仅返回两个表中匹配的行。如果左表或右表中没有匹配项,则这些行不会出现在结果集中。因此,`INNER JOIN` 的行为 `LEFT JOIN` 或 `LEFT OUTER JOIN` 明显不同[^3]。 ### OUTER 关键字的作用 在 SQL 标准中,`OUTER` 关键字用于明确表示连接操作是外连接(即返回左表或右表中的所有记录,即使另一侧没有匹配项)。然而,在某些数据库系统中(如 Oracle),`OUTER` 是可选的,因为 `LEFT JOIN` 或 `RIGHT JOIN` 默认就是外连接的形式。因此,`OUTER` 关键字更多是用于增强 SQL 语句的可读性,而不是改变其行为[^1]。 ### FULL JOIN 和 FULL OUTER JOIN 类似地,`FULL JOIN` 和 `FULL OUTER JOIN` 也是等效的。它们返回左表和右表中的所有记录。如果某一行在另一张表中没有匹配项,则相应的字段将显示为 `NULL`。因此,`OUTER` 关键字同样是可选的,并且其用途主要是为了强调连接的类型。 ### 总结 - `LEFT JOIN` 和 `LEFT OUTER JOIN` 是等效的,它们都返回左表中的所有记录。 - `INNER JOIN` 仅返回两个表中匹配的记录。 - `OUTER` 关键字在 `LEFT JOIN`、`RIGHT JOIN` 和 `FULL JOIN` 中是可选的,其作用是增强 SQL 语句的可读性。 - `OUTER` 关键字并不改变连接的行为,而是用于明确表示这是一个外连接。 ---
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值