left join使用精华:)

转载 2007年09月21日 16:10:00

create table tt(
 id int identity(1,1),
 name varchar(10)
)

insert tt
select 'a' union all
select 'b' union all
select 'c'

create table tt1(
 id int identity(1,1),
 name varchar(10)
)

insert tt1
select 'a' union all
select 'b' union all
select 'c'

--结果:
select * from tt left join tt1 on tt.id=tt1.id   --提取id相等的

id   name   id   name
--------------------------
1   a    1   a
2   b    2   b
3   c    3   c


--结果1:
select * from tt  left join tt1  on tt.id=tt1.id
where tt.id=1   --用的是where,可以看出用where 和 and 的区别了:)

id   name   id   name
--------------------------
1   a     1     a

--结果2:left join 就是查询tt表中所有记录,如果有返回对应值,没有为NULL
select * from tt  left join tt1 on tt.id=tt1.id
and tt.id=1   --用的是and,且是主表tt.id=1

id   name   id   name
--------------------------
1   a    1   a    
2   b    NULL NULL
3   c    NULL NULL

--结果3:这个返回跟上面一样的结果
select * from tt  left join tt1 on tt.id=tt1.id
and tt1.id=1   --用的是and,且是从表tt1.id=1

id   name   id   name
--------------------------
1   a    1   a    
2   b    NULL NULL
3   c    NULL NULL

--结果4:
select * from tt  left join tt1 on tt.id=1      --关键是去掉了id相等这个条件

id   name   id   name
--------------------------
1   a       1     a
1   a       2     b
1   a       3     c
2   b       NULL NULL
3   c       NULL NULL

--对结果4,很多人不了解,看zjcxc(邹建) 的解释
left join 嘛, 无非是左边表为基础, 扫描右边表匹配的记录

先是左边表的第1条记录
1    a  
按条件 a.id=1, 来扫描右边表的记录
对于右边表的每条记录, 显然 a.id=1 这个条件都是成立的, 所以第1条记录匹配后的结果是:

1      a       1        a
1      a       2        b
1      a       3        c

---------------------------------------------
然后再扫描第2条记录
2     b
对于条件 a.id=1, 在边表中没有与之匹配的记录, 所以右边表为NULL
因此第2条记录匹配的结果为
2      b       NULL        NULL

----------------------------------------------
第3条记录与第2条记录一样, 匹配的结果是
3      c       NULL        NULL

---------------------------------------
因此最终结果是5条记录
1      a       1        a
1      a       2        b
1      a       3        c
2      b       null     null
3      c       null     null


---再看:
select * from tt  left join tt1 on tt1.id=1   --是从表tt1.id=1

id   name   id   name
--------------------------
1   a    1   a
2   b    1   a
3   c    1   a

--解释:上面的结果是这样取的,先取主表1   a,联立从表tt2,正好
其第一条数据id=1,满足条件,所以输出:
1   a    1   a
然后再是从表第二条数据 2 b,id!=1,出输出,同理3   c也不满足条件,
再主表 2  b,联立从表第一条数据1  a,满足条件,输出,
以后依次类推...............


--最主要的就是在连接里的 and 和 where 是有本质区别。
1.
 select * from a left join b on a.id=b.id where a.id=1
2.
 select * from a left join b on a.id=b.id and a.id=1

where里的条件是对连接后的结果集进行筛选,而on里的条件是关联时进行比较用的

--再看一下更加详细的解释:
1、首先列出笛卡尔乘积
1 a 1 a
1 a 2 b
1 a 3 c
2 b 1 a
2 b 2 b
2 b 3 c
3 c 1 a
3 c 2 b
3 c 3 c

2、对每个左边的纪录,看有没有符合on条件的,如果有,保留符合条件的,去掉不符合条件的,如果没有,
   保留左边和右边是null的一条纪录
1 a 1 a
1 a 2 b
1 a 3 c      --左边1的都符合都保留
2 b null null  --左边2的都不符合,变成一条右边null的纪录
3 c null null   --左边3的都不符合,变成一条右边null的纪录


3、再去掉where条件不符合的,就是结果(这里的例子没有where条件,所以结果不变,大家自己可以加条件测试下)
1 a 1 a
1 a 2 b
1 a 3 c
2 b null null
3 c null null

对于left join的on条件:
用左边的记录去匹配右边的每条记录,匹配条件就是on里的条件,若右边有满足条件的记录,则取出来,
若没有匹配的记录,则给个null值,对于其后的where条件,
是对整个left join后产生的结果集进行筛选,所以条件放在on里和where里是完全不同的意思,切记!!!

 

相关文章推荐

Left join优化规则的研究

  • 2008年11月19日 18:01
  • 34KB
  • 下载

SQL left join

  • 2014年08月18日 14:19
  • 104KB
  • 下载

SQL语句中"(+)"与"left join...on"之间的使用方法

早些天在做报表导出时遇到了一件极为头痛的事情,一直纠结于怎么按照报表的样式把数据取出来,冥思苦想无果之后只能请教同事,结果同事看了我的SQL语句之后稍微做了一下修改,最后,画龙点睛的在最末尾加了一个"...
  • zlb824
  • zlb824
  • 2011年09月01日 19:16
  • 1234

MySQL在右表数据不唯一的情况下使用left join的方法

这篇文章主要介绍了MySQL在右表数据不唯一的情况下使用left join的方法,针对右表符合条件表达式的记录数大于1条时left join所显示的结果需求来讲,需要的朋友可以参考下 ...

不使用left-join等多表关联查询,只用单表查询和Java程序,简便实现“多表查询”效果

上次我们提到,不使用left-loin关联查询,可能是为了提高效率或者配置缓存,也可以简化一下sql语句的编写。只写单表查询,sql真得太简单了。问题是,查询多个表的数据还是非常需要的。   因...
  • mggwct
  • mggwct
  • 2017年03月31日 21:32
  • 684

不同的左外连接left join方式(+)及 LeftJoin 和 *=使用注意

先说一些今天处理问题的时候发现了左外连接的新的写法方式啊,不知道大家见过没,不过这个是很好理解的select r.*,c.* from t_tsjb_result r ...

使用left join比直接使用where速度快的原因

多表使用left join只是把主表里的所有数据查询出来,其他表只查询表中的符合条件的某一条记录,所以速度非常快;而多表使用where内联,是把所有表的数据全查出来,然后进行比对,所以速度非常慢。 ...

MySQL 使用profile分析慢sql,group left join效率高于子查询

使用profile来分析慢sql有一个查询比较慢的sql语句,用了子查询,大概需要0.8秒左右,这个消耗时间比较长,严重影响了性能,所以需要进行优化。单独查询单表或者子查询记录都很快,开启profil...
  • mchdba
  • mchdba
  • 2017年01月12日 17:44
  • 1214

使用Mybatis进行连表查询、left join

mybatis实现 left join并把多余的字段映射到实体中 此处需要注意Entity不能直接加上多余的字段,不然在会报错,因此数据库不存在这个多余的字段,所以需要重新new一个新的Entity继...

【mysql】mysql中left join使用on 与where筛选的差异

有这样的一个问题mysql查询使用mysql中left(right)join筛选条件在on与where查询出的数据是否有差异。    可能只看着两个关键字看不出任何的问题。那我们使用实际的例子来说到...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:left join使用精华:)
举报原因:
原因补充:

(最多只允许输入30个字)