sql编写踩坑总结-join篇

1、left join时结果行数大于左表行数

1.1、背景

预期左边关联右表，结果行数一定等于左表。

1.2、现象

关联后结果行数大于左表

1.3 原因分析

左表key不重复，但右表的key可能存在重复，便会出现这种情况。

例如
t1表:
c1 c2
v11 v12
v21 v22
v31 v32

t2表：
c1 c2
v11 v12
v11 v22
v21 v22

注意：t2表的关联key c1列有重复值

SELECT 
t1.c1,t1.c2,t2.c1,t2.c2
from 
t1
LEFT JOIN
t2 
on t1.c1=t2.c1
1
2
3
4
5
6
7

结果如下：

v11 v12 v11 v12
v11 v12 v11 v22
v21 v22 v21 v22
v31 v32 null null

因为右表关联的key列有重复值，导致结果行数增多。

1.4、解决方法

（1） 对右表关联之前按关联key去重，只保留1条记录
适用场景：右表存在大量重复key, 导致明显的数据膨胀
（2）对关联后的左表，再次进行去重
适用场景：右表只是可能存在重复key, 且右表不便于根据某种规则（比如最近时间）选取不重复key的记录, 而左表便于按照某种规则去重

2、join后where条件过滤不生效

2.1 背景

A表left join B表后，想过滤掉结果表中k列不为某值的数据,保留其余数据

2.2 现象

A join B on A.c1 = B.c1
where B.k != ‘xxx’
实际结果比预期少很多，比如A表 1000行，关联B表后，满足k列等于xxx的记录为100行，预期保留900行。
但实际只有500行。

2.3 原因分析

left jion后，结果表中k列存在为null的情况，而 null != ‘xxx’ 结果为null 在where条件中，会导致改行记录被过滤

例如：

SELECT 
t1.c1,t1.c2,t2.c1,t2.c2
from 
t1
LEFT JOIN
t2 
on t1.c1=t2.c1
1
2
3
4
5
6
7

结果如下：
v11 v12 v11 v12
v11 v12 v11 v22
v21 v22 v21 v22
v31 v32 null null

加入where条件后：

SELECT 
t1.c1,t1.c2,t2.c1,t2.c2
from 
t1
LEFT JOIN
t2 
on t1.c1=t2.c1
where t2.c1 !='v11'
1
2
3
4
5
6
7
8

预期会有2行，实际只有1行，
v21 v22 v21 v22

因为 null != 'xxx' 结果为 null

2.4、解决方法

加上t2.c1 is null 显式保留数据

SELECT 
t1.c1,t1.c2,t2.c1,t2.c2
from 
t1
LEFT JOIN
t2 
on t1.c1=t2.c1
where t2.c1 is null or t2.c1 !='v11'
1
2
3
4
5
6
7
8