SQL 查询语句（inner join 与 left join）之间的区别

最新推荐文章于 2024-06-28 14:59:34 发布

置顶 @溪竹

最新推荐文章于 2024-06-28 14:59:34 发布

阅读量7.9k

点赞数 15

本文链接：https://blog.csdn.net/weixin_42181824/article/details/82082637

版权

SQL查询语句：

1. 查询中用到的关键词主要包含六个，书写顺序为 select--from--where--group by--having--order by

2. 但是他们的执行顺序为 from--where--group by--having--select--order by

3. 多表查询的执行顺序为 from--join--on--where--group by--聚合函数--having--select--order by

4. 筛选功能实现: on--where--having (on是用在join之后,多表联合查询;where最终表的筛选;having是分组之后的中间表的筛选)

inner join 与 left join 之间的区别：

需求是从数据库查数据，在前端以柱形图的形式展现出来，查到的数据按行业分组，显示每个行业的户数及户数占比，涉及到的字段有A表的用户数、总用户数和B表的行业名称。本来是不管查不查的到数据，在X轴都应该显示行业名称的，结果是X、Y轴都没有任何数据显示。问题就是我用错了联结方式。

一、sql的left join 、right join 、inner join之间的区别

　　left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录，没有显示null
　　right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录，没有显示null
　　inner join(等值连接) 只返回两个表中联结字段相等的行

举例如下：
--------------------------------------------
表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并不以谁为基础,它只显示符合条件的记录.

工作里数据分析常写的SQL语句总结，希望对你用帮助：

```sql整体的逻辑：
（1）先确定一个基准表a
 (2)再根据你自己的需要去连接其他表，得到你想查询的数据
（3）熟悉各种连接方式及分组，最常用的是左连接和各种聚合函数

select a.id,c.qudao,sum(b.money),count(distinct c.touziid)

from a --基准表

left join 
(
	select b.money
    from b
)bb on a.id = b.user_id

left join c on ...

where 注册时间='2018-08-26'

group by a.id, c.qudao

having sum(b.money)>=100万



select uu.id,(ifnull(ph.bid_money_first_stage,0)+ifnull(ph.bid_money_second_stage,0)+ifnull(ph.bid_money_third_stage,0)),
ph.bid_money_fourth_stage,ph.bid_money_fifth_stage,re.s_money,kk.s_money,

case when csv.id6=2 and csv.id1='专业渠道' then 'a专业渠道' 
else '其他' end 

from 表1 uu 

left join 
( 
	select
 user_id,bid_money_first_stage,bid_money_second_stage,bid_money_third_stage,bid_money_fourth_stage,bid_money_fifth_stage 
	from 表2
)ph on uu.id=ph.user_id 

left join 
( 
	select user_id,sum(money) as s_money
	from 表3 
	where date='2018-05-31'
	group by user_id 
)re on uu.id=re.user_id 

left join 
(
	select nn.user_id,sum(nn.sss_money) as s_money
	from
	(	
		select aa.user_id,sum(aa.mm) as sss_money 
		from 
		( 
			select loan_user_id as user_id,sum(money) as mm 
			from 表4 
			where from_unixtime(time+8*3600,'yyyy-MM-dd') between '2018-06-01' and '2018-06-10' and status=0 
			and type in (1,2,3,7) group by loan_user_id 
		)aa 
		group by aa.user_id having sum(aa.mm)>0 

		union all 

		select bb.user_id,sum(bb.mm) as sss_money 
		from 
		( 
			select id as user_id,sum(money) as mm 
			from 表5 where date='2018-05-31' 
			group by id 

			union all
            
			select user_id,sum(money) as mm 
			from 表6 
			where date='2018-05-31' group by user_id 
		)bb 
		group by bb.user_id
	) nn
	group by nn.user_id
) kk on uu.id=kk.user_id

left join 表7 csv on (case when (uu.current_refer_group_id is not null and uu.current_refer_group_id!=0) then uu.current_refer_group_id=csv.id3 else uu.site_id=csv.id7 end) 

where re.s_money>0 and kk.s_money>1000 
```

```sql

笛卡尔积问题:

select uu.current_refer_user_id,
case 
	when uu.current_refer_group_id in (12,436) then 'a'
	when uu.current_refer_group_id = 11 then 'b'
	else 'c' end,
count(distinct uu.id),count(distinct dx.user_id),sum(dx.mm),sum(nh.nianhua),sum(ye.mm),sum(dlr.sss_money),sum(charge.mm),sum(uc.ss_money),sum(dx1.money)

from 

(
	select id,current_refer_user_id,current_refer_group_id
	from 表1
	where current_refer_group_id in (1,2)
) uu

left join
(
	select user_id, sum(money) as mm
	from 表2
	where FROM_UNIXTIME(create_time,'yyyy-MM-dd') between '2018-03-01' and '2018-03-31'
	group by user_id
)dx on uu.id = dx.user_id

left join
(
	select aa.user_id,sum(aa.nianhua) as nianhua 
	from
	(			
		select user_id, sum(nianhua) as nianhua
		from 表3
		where FROM_UNIXTIME(create_time,'yyyy-MM-dd') between '2018-03-01' and '2018-03-31'
		group by user_id
		
		union all
		
		select user_id,sum(nianhua) as nianhua
		from 表4
		where logdate between '2018-03-01' and '2018-03-31'
		group by user_id

		union all
		select user_id,sum(nianhua) as nianhua
		from 表5
		where date between '2018-03-01' and '2018-03-31'
		group by user_id		
	)aa 
	group by aa.user_id
)nh on dx.user_id = nh.user_id

left join
(
	select nn.uuid,sum(nn.s_money) as mm
	from 
	(  
		select id as uuid,sum(money) as s_money
		from 表6
		where date = '2018-02-28'
		group by id

		union all

		select user_id as uuid,sum(money) as s_money
		from 表7
		where date = '2018-02-28'
		group by user_id
	) nn
	group by nn.uuid 
) ye on uu.id = ye.uuid

left join
(
	select aa.user_id,sum(aa.mm) as sss_money
	from
	(
		select loan_user_id as user_id,sum(money) as mm
		from 表8
		where from_unixtime(real_time+8*3600,'yyyy-MM-dd') between '2018-03-01' and '2018-03-31'
		and status=1 and type in (1,2,3,7)
		group by loan_user_id

		union all
      
		select user_id,sum(money) as mm
		from 表9
		where from_unixtime(create_time,'yyyy-MM-dd') between '2018-03-01' and '2018-03-31'
		and withdraw_status=1
		group by user_id

		union all 

		select user_id,sum(money/100) as mm
		from 表10
		where from_unixtime(finish_time,'yyyy-MM-dd') between '2018-03-01' and '2018-03-31'
		and status=4
		group by user_id
	)aa
	group by aa.user_id
) dlr on uu.id = dlr.user_id

left join
(
	select aa.user_id,sum(aa.s_money) as mm
	from 
	(
		select user_id,sum(money) as s_money
		from 表11
		where from_unixtime(pay_time+8*3600,'yyyy-MM-dd') between '2018-03-01' and '2018-03-31'
		and is_paid=1
		group by user_id 

		union all

		select user_id,sum(amount/100) as s_money
		from 表12
		where pay_status=1 and from_unixtime(create_time,'yyyy-MM-dd') between '2018-03-01' and '2018-03-31'
		group by user_id 
	)aa
	group by aa.user_id 
)charge on uu.id = charge.user_id

left join
(
	select aa.user_id,sum(aa.mm) as ss_money
	from 
	(
		select user_id,sum(money) as mm
		from 表13
		where from_unixtime(withdraw_time+8*3600,'yyyy-MM-dd') between '2018-03-01' and '2018-03-31'
		and withdraw_status=1
		group by user_id
		
		union all

		select user_id,sum(amount/100) as mm 
		from 表14
		where type=0 and withdraw_status=1 
		and from_unixtime(create_time,'yyyy-MM-dd') between '2018-03-01' and '2018-03-31'
		group by user_id 	
	) aa
	group by aa.user_id 
)uc on uu.id = uc.user_id

left join
(
	select owner_user_id,deal_load_id
	from 表15
	where status=2 and from_unixtime(update_time,'yyyy-MM-dd') between '2018-03-01' and '2018-03-31'
) od on uu.id = od.owner_user_id

left join
(
	select user_id, deal_load_id,money
	from 表16
	where FROM_UNIXTIME(create_time,'yyyy-MM-dd') between '2018-03-01' and '2018-03-31'
)dx1 on dx1.deal_load_id = od.deal_load_id and dx1.user_id=od.owner_user_id

group by uu.current_refer_user_id,
case 
	when uu.current_refer_group_id in (12,436) then 'a'
	when uu.current_refer_group_id = 11 then 'b'
	else 'c' end


正确的:
select uu.current_refer_user_id,
case 
	when uu.current_refer_group_id in (12,436) then 'a'
	when uu.current_refer_group_id = 11 then 'b'
	else 'c' end,
count(distinct uu.id),count(distinct dx.user_id),sum(dx.mm),sum(nh.nianhua),sum(ye.mm),sum(dlr.sss_money),sum(charge.mm),sum(uc.ss_money)

from 
...
group by uu.current_refer_user_id,
case 
	when uu.current_refer_group_id in (12,436) then 'c'
	when uu.current_refer_group_id = 11 then 'b'
	else 'a' end

+

select uu.current_refer_user_id,
case 
	when uu.current_refer_group_id in (12,436) then 'a'
	when uu.current_refer_group_id = 11 then 'b'
	else 'c' end,
sum(dx.money),sum(nh.nianhua)

from
(
	select id,current_refer_user_id,current_refer_group_id
	from DC_firstp2p_user 
	where current_refer_group_id in (1,2,3)
) uu

left join
(
	select id,owner_user_id,deal_load_id
	from o2o_discount	
) od on uu.id = od.owner_user_id

inner join
(
	select user_id, deal_load_id,money
	from DC_all_deal_load_xin
	where FROM_UNIXTIME(create_time,'yyyy-MM-dd') between '2018-05-01' and '2018-05-29'
	and invest_rank >=1
)dx on dx.deal_load_id = od.deal_load_id and dx.user_id=od.owner_user_id

group by uu.current_refer_user_id
case 
	when uu.current_refer_group_id in (12,436) then 'a'
	when uu.current_refer_group_id = 11 then 'b'
	else 'c' end
```

```sql

--实现同样效果的多重写法
--1
select uu.current_refer_user_id,sum(dx.mm),
case 
	when uu.current_refer_group_id in (12,436) then 'a'
	when uu.current_refer_group_id = 11 then 'b'
	else 'c' end
from 
(
	select id,current_refer_user_id,current_refer_group_id
	from 表1
	where current_refer_group_id in (1,2,3)
) uu

left join
(
	select user_id, sum(money) as mm
	from 表2
	where FROM_UNIXTIME(create_time,'yyyy-MM-dd') between '2018-03-01' and '2018-03-31'
	group by user_id
)dx on uu.id = dx.user_id

group by uu.current_refer_user_id,
case 
	when uu.current_refer_group_id in (12,436) then 'a'
	when uu.current_refer_group_id = 11 then 'b'
	else 'c' end

--2
select aa.id,aa.current_refer_user_id,aa.qudao,dx1.money,dx1.product_class,dx1.deal_load_id
from
(
	select uu.id,uu.current_refer_user_id,
	case 
		when uu.current_refer_group_id in (436,12) then 'a'
		when uu.current_refer_group_id in (11) then 'b'
		when uu.current_refer_group_id in (382,383,384,100) then 'c'
		else '其他' end as qudao
	from DC_firstp2p_user uu 
	where uu.current_refer_group_id in(1,2,3)
) aa

left join
(
	select user_id,deal_load_id,money,product_class
	from 表1
	where from_unixtime(create_time,'yyyy-MM-dd') between '2018-03-01' and '2018-03-31'
)dx1 on aa.id=dx1.user_id

--3
select uu.current_refer_user_id,
case 
	when uu.current_refer_group_id in (12,436) then 'a'
	when uu.current_refer_group_id = 11 then 'b'
	else 'c' end,
dx.mm

from 
(
	select id,current_refer_user_id,current_refer_group_id
	from 表1  
	where current_refer_group_id in (1,2,3)
) uu

left join
(
	select user_id, sum(money) as mm
	from 表2
	where FROM_UNIXTIME(create_time,'yyyy-MM-dd') between '2018-03-01' and '2018-03-31'
	group by user_id
)dx on uu.id = dx.user_id

--4
select uu.current_refer_user_id,
case 
	when uu.current_refer_group_id in (12,436) then 'a'
	when uu.current_refer_group_id = 11 then 'b'
	else 'c' end,
sum(dx.money)
from 
(
	select id,current_refer_user_id,current_refer_group_id
	from 表1
	where current_refer_group_id in (1,2,3)
) uu

left join
(
	select user_id, money
	from表2
	where FROM_UNIXTIME(create_time,'yyyy-MM-dd') between '2018-03-01' and '2018-03-31'
)dx on uu.id = dx.user_id

group by uu.current_refer_user_id,
case 
	when uu.current_refer_group_id in (12,436) then 'a'
	when uu.current_refer_group_id = 11 then 'b'
	else 'c' end
```

@溪竹

关注

15
点赞
踩
44

收藏

觉得还不错? 一键收藏
0
评论
SQL 查询语句（inner join 与 left join）之间的区别

SQL查询语句：1. 查询中用到的关键词主要包含六个，书写顺序为 select--from--where--group by--having--order by 2. 但是他们的执行顺序为 from--where--group by--having--select--order by 3. 多表查询的执行顺序为 from--join--on--where--group by--聚合函...
复制链接

扫一扫