MySQL left join左查询翻车记

一、问题

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

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

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

1
-- 建表并插入记录CREATE TABLE t1 SELECT 1 id, 'notice 1' notice_title UNION ALLSELECT 2, 'notice 2' UNION ALLSELECT 3, 'notice 3';create table t2select 1 user_id, 1 notice_id, 0 is_read union allselect 2,2,0;create table t3select 1 user_id, 'Tom' user_name union allselect 2,'Jerry';
1
-- 建表并插入记录CREATE TABLE t1 SELECT 1 id, 'notice 1' notice_title UNION ALLSELECT 2, 'notice 2' UNION ALLSELECT 3, 'notice 3';create table t2select 1 user_id, 1 notice_id, 0 is_read union allselect 2,2,0;create table t3select 1 user_id, 'Tom' user_name union allselect 2,'Jerry';

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

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

image

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

二、分析

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

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

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

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

image

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

1
where
1
where t2.user_id = 1 or t2.user_id is null

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

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

image

image

image

三、可行方案

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

1. union查询 + in 子查询

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

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

image

2. 子查询/派生表

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

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

image

3. 应用层方案

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

1
union

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