左查询翻车记

一、问题

开发过程中遇到一个左查询的场景，差点翻车了。

问题是这样的：一共有三张表t1(通知表）,t2（用户通知关联表）,t3（用户表）。如果给用户下发过通知，关联表中增加一条记录。需求是一条SQL查询返回用户维度的全部通知详情（包括t2中已关联的和未关联的），因为t2关联表中还有其他字段(比如消息是否已读），不能只返回t1表内容。未关联的通知t2表中字段返回null。

注意尽量避免有复杂的查询比如union，子查询，避免应用层数据库驱动或者ORM框架比如JPA不支持。

-- 建表并插入记录
CREATE TABLE t1 
SELECT 1 id, 'notice 1' notice_title UNION ALL
SELECT 2, 'notice 2' UNION ALL
SELECT 3, 'notice 3';


create table t2
select 1 user_id, 1 notice_id, 0 is_read union all
select 2,2,0;

create table t3
select 1 user_id, 'Tom' user_name union all
select 2,'Jerry';

从需求看这是一个left join的典型场景。SQL如下

SELECT * from t1 
left join t2 
on t1.id = t2.notice_id
where t2.user_id = 1 or t2.user_id is null;

结果不符合预期！需求是返回user_id = 1下发过的未下发的所有记录。notice_id = 2的通知应该也要返回，为啥左查询结果中没包括呢？

二、分析

这需要了解一下左查询的过程。

1. 左查询第一步包括左表的全部行和右表关联匹配的行，右表没有匹配的话字段为NULL。匹配过程是遍历全部左表行，按join on中的条件关联右表行，匹配连成一行，不匹配的右边填NULL。
2. 连接后再按where中条件过滤第一步的查询结果。

让我们先去掉where查询条件看一下第一步的查询结果。

SELECT * from t1 
left join t2 
on t1.id = t2.notice_id;

再加上where过滤条件where t2.user_id = 1 or t2.user_id is null上面的查询结果就好理解了。

理解的关键是on的连接过程第一步。下面查询大家可以猜测一下结果是什么？

-- on 条件不匹配右表的任何行，再过滤结果
SELECT * from t1 
left join t2 
on 1 <> 1
where t2.user_id = 1 or t2.user_id is null;

-- on 条件匹配右表所有行，再过滤结果
SELECT * from t1 
left join t2 
on 1 = 1
where t2.user_id = 1 or t2.user_id is null;

-- on 条件匹配notice_id = 1的行，再过滤结果
SELECT * from t1 
left join t2 
on t1.id = 1
where t2.user_id = 1 or t2.user_id is null;

三、可行方案

注意下面列举的方案都不能完全满足“一条SQL尽量不包括union和子查询”的需求，只是探讨一些可能的方案。如果有更好的方案欢迎留言。

1. union查询 + in 子查询

可以得到结果。但是union查询应用层框架不一定都支持

SELECT * from 
t1 join t2 
on t1.id = t2.notice_id
where user_id = 1
UNION 
select t1.*, null, null, null 
from t1 
where t1.id not in 
(select t2.notice_id from t2 where user_id = 1)

2. 子查询/派生表

方案简洁甚至不用where过滤。遗憾的是应用层数据库驱动或者ORM框架不一定支持

SELECT * from 
t1 left join (
	select * from t2 
	where t2.user_id = 1
) t_temp on t1.id = t_temp.notice_id

3. 应用层方案

3.1 应用层分别调用2个SQL，类似方案1的union。问题是查询结果多时需要分页，应用层分页是个新的问题

3.2 应用层定时补全t2表的连接关系（t1和t3的连接关系）。通过t2表的is_read字段来区分业务状态比如已下发未读、已读等