一、问题
开发过程中遇到一个左查询的场景，差点翻车了。
问题是这样的：一共有三张表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;
查询结果中缺少notice_id = 2
结果不符合预期！需求是返回user_id = 1下发过的未下发的所有记录。notice_id = 2的通知应该也要返回，为啥左查询结果中没包括呢？
二、分析
这需要了解一下左查询的过程。
- 左查询第一步包括左表的全部行和右表关联匹配的行，右表没有匹配的话字段为NULL。匹配过程是遍历全部左表行，按
join on中的条件关联右表行，匹配连成一行，不匹配的右边填NULL。 - 连接后再按
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;
on 1 = 1
on 1 <> 1
t1.id = 1
三、可行方案
注意下面列举的方案都不能完全满足“一条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字段来区分业务状态比如已下发未读、已读等
