mysql中内外连接_MySQL中内外连接的混合

你可以通过做一个

FULL JOIN

对于MySQL不支持但可以使用两个查询模拟的事实表,

LEFT JOIN

然后结合

UNION

. 在这两个查询中,我们检查是否有人存在于

person

表

WHERE

子句(两次,以限制尽快处理的行数):

SELECT

COALESCE(f.p1, f.p2) as person_id,

COALESCE(f.d1, f.d2) as day,

m1 as metric1,

m2 as metric2

FROM (

SELECT f1.person_id as p1,f1.day as d1,f1.metric as m1,f2.person_id as p2,f2.day as d2,f2.metric as m2

FROM fact_1 f1

LEFT JOIN fact_2 f2 ON f1.person_id = f2.person_id and f1.day = f2.day

WHERE EXISTS (SELECT 1 FROM person p WHERE p.id = f1.person_id)

UNION

SELECT f1.person_id as p1,f1.day as d1,f1.metric as m1,f2.person_id as p2,f2.day as d2,f2.metric as m2

FROM fact_2 f2

LEFT JOIN fact_1 f1 ON f1.person_id = f2.person_id and f1.day = f2.day

WHERE EXISTS (SELECT 1 FROM person p WHERE p.id = f2.person_id)

) f

ORDER BY person_id, day

结果如下:

person_id day metric1 metric2

---------------------------------------

A 1 x null

A 2 y b

A 3 null a

如果你确定

person_id

事实上,表是正确的(您在外键约束中强制它,或者以某种方式检查它),您可以跳过

WHERE EXISTS

检查以提高性能。

考虑在上创建索引

fact_1(person_id, day)

和

fact_2(person_id, day)

.