关于left join ... is null 的奇怪思路

虽然题目有点拗口难懂，不知我到底在解决什么问题。我通过例子的方法来说明，就会一目了然了。

最近在做的一个需求，其中一部分要求是：查询出有过什么什么的客户且过滤掉什么的客户，其实就是各种表的联结再加上一些过滤条件。这都不重要，重要的是，这两个过滤条件我写的很有问题。

如下：

假如有一个表tb_ftc（不是真实业务场景的表）

c_id	ch_no	c_st	...
01023	1	-1
02345	3	2
13132	2	3
23456	4	5
01023	2	17
01023	10	8
23456	1	2
13132	3	7
23456	10	5
...	...

过滤条件1：剔除c_st=2，5的c_id；

过滤条件2：取ch_no有过1和2的记录；

过滤条件3：剔除ch_no=10的c_id。

我写的第一版：

select c_id,...
from tb_ftc
where c_st not in (2,5)
and ch_no in (1,2)
and ch_no!=10;

因为查出来的结果，即c_id要和其他表进行关联，然后发现，ch_no=10的c_id还是存在的。

原因是 ch_no in (1,2)这个条件执行了，但ch_no!=10没执行。也就是说，有过ch_no=1和ch_no=2的c_id被留下了，这个c_id要和其他表关联

c_id	ch_no	c_st	...
01023	1	-1
13132	2	3
01023	2	17
23456	1	2
...	...

实际上c_id=01023&23456通过c_id和其他表关联之后，ch_no=10还是存在的

01023	10	8
23456	10	5

于是有了第二版：

当时的思路是，既然第二个过滤条件没用上，那就把ch_no！=10的记录单独作为一个表与过滤前两个条件的表进行关联，于是有了下面这样的代码

select a.c_id,a.ch_no,b.ch_no
from
(select c_id,ch_no
from tb_ftc
where c_st not in (2,5)
and ch_no in (1,2)) a join
(select c_id,ch_no
from tb_ftc
where ch_no!=10) b
on a.c_id=b.c_id;

这版写完之后逻辑感觉完全没有问题

a表

c_id	ch_no
01023	1
13132	2
01023	2
...

b表

c_id	ch_no
01023	1
02345	3
13132	2
23456	4
01023	2
23456	1
13132	3
...

join得到：

c_id	a.ch_no	b.ch_no
01023	1	1
01023	1	2
13132	2	2
13132	2	3
01023	2	1
01023	2	2
...

去重：

c_id	a.ch_no
01023	1
13132	2
01023	2

然而，用c_id=01023、13132去关联其他表，ch_no=10的这个c_id还是存在即01023。

我理解的偏差就在，以为删掉了ch_no=10的c_id,但只是删掉了tb_ftc表中的c_id，忽略了一个c_id有多个ch_no。

后来仔细想了一下，这个问题的思路应该是，只要有ch_no=10的c_id,就直接删掉，即使这个c_id有ch_no=1&2的记录。

所以，首先将ch_no=10的c_id全部挑选出来，然后再从tb_ftc表中挑选不是ch_no=10的c_id，加上另外两个条件即可。

select c_id
from tb_tfc
where c_id not in(select c_id
from tb_tfc 
where ch_no=10)
and ch_no in(1,2)
and c_st not in (2,5)

但是 not in (select 子句)通常可以由left join 来替换，联结比子查询更高效。

所以最终版本是：

select a.c_id
from tb_tfc a left join 
(select c_id
from tb_tfc 
where ch_no=10) b
on a.c_id=b.c_id
where b.c_id is null
and ch_no in(1,2)
and c_st not in (2,5)

left join 查询精髓：查询出a表有但b表没有的记录

select a.id
from tb a
left join (select id,no
from tb 
where no=1) b
on a.id=b.id
where b.id is null;

其实对于熟悉sql的人说就是个小问题，但是对于一个从没写过复杂sql的我，各种联结子查询我就晕了。。。