left join

最新推荐文章于 2024-07-23 23:39:10 发布

hushunhuadao

最新推荐文章于 2024-07-23 23:39:10 发布

阅读量821

点赞数 1

分类专栏： oracle

oracle 专栏收录该内容

8 篇文章 1 订阅

订阅专栏

left join 就是以左边的表为基础，扫描右边的表，on .,,and ,,,是条件，如果and后面的约束左边的field则右边表都符合条件。

左连接是left join 嘛,无非是左边表为基础, 扫描右边表匹配的记录
--按条件 a.uname='mike', 来扫描右边表的记录
--对于右边表的每条记录, 显然 a.uname='mike' 这个条件都是成立.

http://blog.csdn.net/xingfeng0501/article/details/7816703

http://blog.csdn.net/qq_27918787/article/details/52297309

https://www.cnblogs.com/tinachang021/archive/2008/09/25/1298949.html

数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。

在使用left jion时，on和where条件的区别如下：

1、 on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。

2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

假设有两张表：

表1 tab1：

id size

1 10

2 20

3 30

表2 tab2：

size name

10 AAA

20 BBB

20 CCC

两条SQL:
1、select * formtab1 left join tab2 on (tab1.size = tab2.size) where tab2.name=’AAA’
2、select * formtab1 left join tab2 on (tab1.size = tab2.size and tab2.name=’AAA’)

第一条SQL的过程：

1、中间表
on条件:
tab1.size = tab2.size

tab1.id tab1.size tab2.size tab2.name

1 10 10 AAA

2 20 20 BBB

2 20 20 CCC

3 30 (null) (null)

2、再对中间表过滤
where 条件：
tab2.name=’AAA’

tab1.id tab1.size tab2.size tab2.name

1 10 10 AAA

第二条SQL的过程：

1、中间表
on条件:
tab1.size = tab2.size and tab2.name=’AAA’
(条件不为真也会返回左表中的记录)

tab1.id tab1.size tab2.size tab2.name

1 10 10 AAA

2 20 (null) (null)

3 30 (null) (null)

其实以上结果的关键原因就是left join,right join,full join的特殊性，不管on上的条件是否为真都会返回left或right表中的记录，full则具有left和right的特性的并集。而inner jion没这个特殊性，则条件放在on中和where中，返回的结果集是相同的。

一、概念：

–把left join左边的表的记录全部找出来。系统会先用表A和表B做个笛卡儿积，然后以表A为基表，去掉笛卡儿积中表A部分为NULL的记录。最后形成你的结果。

–进行左连接时，就有涉及到主表、辅表，这时主表条件写在WHERE之后，辅表条件写在ON后面。

二、实例

1、表a结构和数据

table a(id, type):

id     type 

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

1      1         

2      1          

3      2

2、表b结构和数据

table b(id, class):

id    class 

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

1      1

2      2

3、SQL语句调用

--sql语句1：

select a.*, b.* from a left join b on a.id = b.id and a.type = 1;

执行结果为:

a.id    a.type    b.id    b.class

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

1        1            1        1

2        1            2        2

3        2

--sql语句2：

select a.*, b.* from a left join b on a.id = b.id where a.type = 1;

执行结果为:

a.id    a.type    b.id    b.class

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

1        1            1        1

2        1            2        2

--sql语句3：

select a.*, b.* from a left join b on a.id = b.id and b.class = 1;

执行结果为：

a.id    a.type    b.id    b.class

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

1        1            1        1

2        1            

3        2

4、总结：

–sql语句1可见，left join 中左表的全部记录将全部被查询显示，on 后面的条件对它不起作用，除非再后面再加上where来进行筛选，这就是sql语句2了。

–由sql语句3可见，on后面的条件中，右表的限制条件将会起作用。

20180815总结，

left join,涉及主表，副表

left join on 后面的条件（无论是对主表的条件还是副表的条件）不会过滤主表的返回数，（全部返回）

而如果是where 后面再加主表的限制条件，则会过滤主表的返回数，（不是全部返回了）

left join on 后面的条件如果加在副表上，则会过滤副表的返回条数，主表不影响。如果on 后面的条件加在了主表上，则不会影响主表的返回条数（照样全部返回）

因此，where 后面加主表的限制条件，on 后面加副表的限制条件，则最后的返回结果集中将不再是主表全部返回，满足表连接条件的副表全部返回。

如果没有where +主表限制，及on 后面只有连接条件，没有副表的限制条件则最后的结果集就是我最初学到的left join , 主表全部返回，副表满足连接条件的全部返回。否则，主表的返回条数可以限制，副表的返回结果也可以限制，最后的表就是2张表的都不全，符合我们实际复杂的需求

关于数据库中使用 left join on ...and ...和 left join on ....where ...区别,和使用group by 要注意的情况

create table UserAccount
(
   UID int,
   UName varchar(50),
   JoinDate datetime
)

insert into UserAccount values(1,'Jimmy','2006-10-3')
insert into UserAccount values(2,'Jack','2006-10-25')
insert into UserAccount values(3,'Mike','2006-11-13')
insert into UserAccount values(4,'Tom','2006-9-5')

create table ChargeHistory(UId int,ChargeDate datetime,Amount int)

insert into ChargeHistory values(1,'2007-3-1',50)
insert into ChargeHistory values(1,'2007-2-15', 30)
insert into ChargeHistory values(2,'2007-2-1' ,40)
insert into ChargeHistory values(3,'2007-1-15' ,60)
insert into ChargeHistory values(2,'2007-1-15' ,50)
insert into ChargeHistory values(3,'2007-1-1' ,100)
insert into ChargeHistory values(1,'2006-12-20' ,60)

select * from UserAccount
select * from ChargeHistory

1
Jimmy 2006-10-03 00:00:00.000 2
Jack 2006-10-25 00:00:00.000 3
Mike 2006-11-13 00:00:00.000 4
Tom 2006-09-05 00:00:00.000

1
2007-03-01 00:00:00.000 50 1
2007-02-15 00:00:00.000 30 2
2007-02-01 00:00:00.000 40 3
2007-01-15 00:00:00.000 60 2
2007-01-15 00:00:00.000 50 3
2007-01-01 00:00:00.000 100 1
2006-12-20 00:00:00.000 60

假设有一个游戏帐户充值网站

系统内有两个表

UserAccount

ChargeHistory

1. 建表并填充数据

2. 完成如下操作:

A. 查询用户名为mike的充值记录,包含如下列

UName,UId,JoinDate,ChargeDate,Amount

B. 查询用户Id为1共充值多少金额.包含

UName,UId,JoinDate,Amount

C. 统计每个用户的充值总额,结果如下

select A.UID,A.UName,A.JoinDate,B.ChargeDate,B.Amount from UserAccount A
left join ChargeHistory B on A.UID=B.UID and a.uname='mike' 这里的结果是:不是我们想要的结果因为:

--通过以下结果我们可以看出:左连接是left join 嘛,无非是左边表为基础, 扫描右边表匹配的记录
--按条件 a.uname='mike', 来扫描右边表的记录
--对于右边表的每条记录, 显然 a.uname='mike' 这个条件都是成立.

Jimmy 2006-10-03 00:00:00.000 NULL NULL 2
Jack 2006-10-25 00:00:00.000 NULL NULL 3
Mike 2006-11-13 00:00:00.000 2007-01-15 00:00:00.000 60 3
Mike 2006-11-13 00:00:00.000 2007-01-01 00:00:00.000 100 4
Tom 2006-09-05 00:00:00.000 NULL NULL

select A.UID,A.UName,A.JoinDate,B.ChargeDate,B.Amount from UserAccount A
left join ChargeHistory B on A.UID=B.UID where AME='M.UNAike'所以这里要使用where .where是left join 后筛选结果.

结果:

Mike 2006-11-13 00:00:00.000 2007-01-15 00:00:00.000 60 3
Mike 2006-11-13 00:00:00.000 2007-01-01 00:00:00.000 100 4

--但是inner join 是选择符合条件的数据出来
select A.UID,A.UName,A.JoinDate,B.ChargeDate,B.Amount from UserAccount A
inner join ChargeHistory B on A.UID=B.UID and a.uname='mike'

--group by 的操作:
--有函数存在 :sum,count,min,avg,max
--slecect多少个字段,group by 多少个字段
select A.UID,A.UName,A.JoinDate,sum(B.amount) from UserAccount A left join ChargeHistory B ON A.uID=B.UID WHERE A.UID=1
group by a.uid,A.UName,A.JoinDate

--这里还要注意 having的用法:是用来在group by 后再筛选数据的
select A.UID,A.UName,A.JoinDate,sum(B.amount) from UserAccount A left join ChargeHistory B ON A.uID=B.UID
group by a.uid,A.UName,A.JoinDate
having A.UID=1