MySQL:左联接与右联接的并集 [英] MySQL: Union of a Left Join with a Right Join

查看:65
本文介绍了MySQL:左联接与右联接的并集的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

假设我们有以下表格t1和t2:

Say we have the following tables t1 and t2:

t1:
id | column_1
-------------
 1 |   1
 2 |   2

t2:
id | column_2
-------------
 2 |   2
 3 |   3

,我们想找到以下结果:

and we want to find the following result:

id | column_1 | column_2
------------------------
 1 |  1       | 
 2 |  2       | 2
 3 |          | 3

这基本上是右连接与左连接的并集.以下代码有效,但感觉笨拙:

This basically is the union of a right join with a left join. The following code works but feels clumsy:

(
    SELECT t1.id, t1.column_1, t2.column_2 
    FROM t1 
    LEFT JOIN t2 ON t1.id = t2.id
)
UNION
(
    SELECT t2.id, t1.column_1, t2.column_2 
    FROM t1 
    RIGHT JOIN t2 ON t1.id = t2.id
)

有没有更好的方法来实现这一目标?

Is there a better way to achieve this?

推荐答案

select a.id, t1.column_1, t2.column_2
from (
    select id from t1
    union 
    select id from t2
) a
left outer join t1 on a.id = t1.id
left outer join t2 on a.id = t2.id

这篇关于MySQL:左联接与右联接的并集的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆