-- t1 left join t2
SELECT t1.value, t2.value
FROM t1 LEFT JOIN t2 ON t1.value = t2.value
UNION ALL -- include duplicates
-- t1 right exclude join t2 (records found only in t2)
SELECT t1.value, t2.value
FROM t1 RIGHT JOIN t2 ON t1.value = t2.value
WHERE t2.value IS NULL
1 Tim 1 Tim
2 Marta NULL NULL
NULL NULL 3 Katarina
然而,正如Pablo Santa Cruz指出的那样,MySQL并不支持这一点 . 我们可以通过执行左连接和右连接的UNION来模拟它,如下所示:
SELECT *
FROM `t1`
LEFT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`
UNION
SELECT *
FROM `t1`
RIGHT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`;
您可以将 UNION 视为含义"run both of these queries, then stack the results on top of each other";一些行将来自第一个查询,一些来自第二个查询 .
应该注意的是,MySQL中的 UNION 将消除完全重复:Tim会出现在这两个查询中,但 UNION 的结果只列出他一次 . 我的数据库大师同事觉得不应该依赖这种行为 . 所以为了更明确一点,我们可以在第二个查询中添加一个 WHERE 子句:
SELECT *
FROM `t1`
LEFT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`
UNION
SELECT *
FROM `t1`
RIGHT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`
WHERE `t1`.`id` IS NULL;
另一方面,如果由于某种原因你看到重复,你可以使用 UNION ALL .
1
使用 union 查询将删除重复项,这与 full outer join 的行为不同,后者永远不会删除任何重复项:
[Table: t1] [Table: t2]
value value
------- -------
1 1
2 2
4 2
4 5
select
t1.value, t2.value
from t1
left outer join t2
on t1.value = t2.value
union all -- Using `union all` instead of `union`
select
t1.value, t2.value
from t2
left outer join t1
on t1.value = t2.value
where
t1.value IS NULL
-- (t1 ∩ t2): all in both t1 and t2
select t1.value, t2.value
from t1 join t2 on t1.value = t2.value
union all -- And plus
-- all in t1 that not exists in t2
select t1.value, null
from t1
where not exists( select 1 from t2 where t2.value = t1.value)
union all -- and plus
-- all in t2 that not exists in t1
select null, t2.value
from t2
where not exists( select 1 from t1 where t2.value = t1.value)
SELECT *
FROM leftTable lt
LEFT JOIN rightTable rt ON lt.id = rt.lrid
UNION
SELECT lt.*, rl.* -- To match column set
FROM rightTable rt
LEFT JOIN leftTable lt ON lt.id = rt.lrid
SELECT * FROM t1 FULL OUTER JOIN t2 ON t1.Name = t2.Name;
正确的等价物是:
SELECT t1.*, t2.*
FROM (SELECT name FROM t1 UNION -- This is intentionally UNION to remove duplicates
SELECT name FROM t2
) n LEFT JOIN
t1
ON t1.name = n.name LEFT JOIN
t2
ON t2.name = n.name;
SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
WHERE t1.col1 = 'someValue'
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
WHERE t1.col1 = 'someValue'
由查询优化器转换为以下内容 -
SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
WHERE t1.col1 = 'someValue'
UNION
SELECT * FROM t2
LEFT JOIN t1 ON t2.id = t1.id
WHERE t1.col1 = 'someValue'
因此,表的顺序已更改,但谓词仍应用于t1,但t1现在位于'ON'子句中 . 如果t1.col1定义为 NOT NULL 列,则此查询将被拒绝 .
(SELECT
*
FROM
table1 t1
LEFT JOIN
table2 t2 ON t1.id = t2.id
WHERE
t2.id IS NULL)
UNION ALL
(SELECT
*
FROM
table1 t1
RIGHT JOIN
table2 t2 ON t1.id = t2.id
WHERE
t1.id IS NULL);
0
SELECT
a.name,
b.title
FROM
author AS a
LEFT JOIN
book AS b
ON a.id = b.author_id
UNION
SELECT
a.name,
b.title
FROM
author AS a
RIGHT JOIN
book AS b
ON a.id = b.author_id
SELECT t1.*, t2.*
FROM table1 t1
INNER JOIN table2 t2
ON 1=1;
4
这也是可能的,但您必须在select中提及相同的字段名称 .
SELECT t1.name, t2.name FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT t1.name, t2.name FROM t2
LEFT JOIN t1 ON t1.id = t2.id
-2
我修复了响应,并且包括所有行(基于Pavle Lekic的响应)
(
SELECT a.* FROM tablea a
LEFT JOIN tableb b ON a.`key` = b.key
WHERE b.`key` is null
)
UNION ALL
(
SELECT a.* FROM tablea a
LEFT JOIN tableb b ON a.`key` = b.key
where a.`key` = b.`key`
)
UNION ALL
(
SELECT b.* FROM tablea a
right JOIN tableb b ON b.`key` = a.key
WHERE a.`key` is null
);
-1
Answer:
SELECT * FROM t1 FULL OUTER JOIN t2 ON t1.id = t2.id;
可以重新创建如下:
SELECT t1.*, t2.*
FROM (SELECT * FROM t1 UNION SELECT name FROM t2) tmp
LEFT JOIN t1 ON t1.id = tmp.id
LEFT JOIN t2 ON t2.id = tmp.id;
WITH cte_t1 AS
(
SELECT 1 AS id1
UNION ALL SELECT 2
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 6
),
cte_t2 AS
(
SELECT 3 AS id2
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 6
)
SELECT * FROM cte_t1 t1 FULL OUTER JOIN cte_t2 t2 ON t1.id1 = t2.id2;
This gives us this answer:
id1 id2
1 NULL
2 NULL
NULL 3
NULL 4
5 5
6 6
6 6
6 6
6 6
UNION解决方案:
SELECT * FROM cte_t1 t1 LEFT OUTER JOIN cte_t2 t2 ON t1.id1 = t2.id2
UNION
SELECT * FROM cte_t1 t1 RIGHT OUTER JOIN cte_t2 t2 ON t1.id1 = t2.id2
给出了错误的答案:
id1 id2
NULL 3
NULL 4
1 NULL
2 NULL
5 5
6 6
UNION ALL解决方案:
SELECT * FROM cte_t1 t1 LEFT OUTER join cte_t2 t2 ON t1.id1 = t2.id2
UNION ALL
SELECT * FROM cte_t1 t1 RIGHT OUTER JOIN cte_t2 t2 ON t1.id1 = t2.id2
SQL标准说 full join on 是 inner join on 行 union all 不匹配的左表行,由空值扩展 union all 右表行由空值扩展 . 即 left join on 中的行 inner join on 行 union all 但 right join on 中没有 inner join onunion all 行但不是 inner join on .
即 left join on 行 union allright join on 行不在 inner join on 中 . 或者,如果您知道 inner join on 结果在特定的右表列中不能为空,则“ right join on 不在 inner join on 中的行”是 right join on 中的行, on 条件由 and 扩展为 is null 列 .
14 回答
修改了shA.t的查询以获得更清晰:
您没有在MySQL上完全加入,但您可以确定emulate them .
对于从this SO question转录的代码SAMPLE,您有:
有两个表t1,t2:
上面的查询适用于FULL OUTER JOIN操作不会产生任何重复行的特殊情况 . 上面的查询依赖于
UNION
set运算符来删除查询模式引入的重复行 . 我们可以通过对第二个查询使用反连接模式来避免引入重复行,然后使用UNION ALL集合运算符来组合这两个集合 . 在更一般的情况下,FULL OUTER JOIN将返回重复的行,我们可以这样做:Pablo Santa Cruz给出的答案是正确的;但是,如果有人偶然发现了这个页面并想要进一步澄清,这里有一个详细的细分 .
示例表
假设我们有以下表格:
内部联接
内连接,如下所示:
只会让我们看到两个表中出现的记录,如下所示:
内连接没有方向(如左或右),因为它们是明确双向的 - 我们需要在两侧都匹配 .
外部联接
另一方面,外连接用于查找在另一个表中可能没有匹配的记录 . 因此,您必须指定允许连接的 which side 具有丢失的记录 .
LEFT JOIN
和RIGHT JOIN
是LEFT OUTER JOIN
和RIGHT OUTER JOIN
的简写;我将使用下面的全名来强化外连接与内连接的概念 .左外连接
左外连接,如下所示:
...将从左表中获取所有记录,无论它们是否在右表中匹配,如下所示:
右外连接
右外连接,如下所示:
...将从右表中获取所有记录,无论左表中是否有匹配,如下所示:
完全外部加入
完全外连接将为我们提供来自两个表的所有记录,无论它们是否在另一个表中具有匹配,在没有匹配的情况下两侧都有NULL . 结果如下所示:
然而,正如Pablo Santa Cruz指出的那样,MySQL并不支持这一点 . 我们可以通过执行左连接和右连接的UNION来模拟它,如下所示:
您可以将
UNION
视为含义"run both of these queries, then stack the results on top of each other";一些行将来自第一个查询,一些来自第二个查询 .应该注意的是,MySQL中的
UNION
将消除完全重复:Tim会出现在这两个查询中,但UNION
的结果只列出他一次 . 我的数据库大师同事觉得不应该依赖这种行为 . 所以为了更明确一点,我们可以在第二个查询中添加一个WHERE
子句:另一方面,如果由于某种原因你看到重复,你可以使用
UNION ALL
.使用
union
查询将删除重复项,这与full outer join
的行为不同,后者永远不会删除任何重复项:这是
full outer join
的预期结果:这是使用
left
和right Join
与union
的结果:[SQL Fiddle]
我建议的查询是:
上述查询的结果与预期结果相同:
[SQL Fiddle]
我决定添加另一个来自
full outer join
可视化和数学的解决方案,它不是更好,但更可读:[SQL Fiddle]
在SQLite中你应该这样做:
上述答案中没有一个实际上是正确的,因为当存在重复值时它们不遵循语义 .
对于诸如(来自此duplicate)的查询:
正确的等价物是:
如果您需要使用
NULL
值(也可能是必需的),则使用NULL
-safe比较运算符<=>
而不是=
.MySql没有FULL-OUTER-JOIN语法 . 您必须通过执行LEFT JOIN和RIGHT JOIN来模拟如下 -
但是MySql也没有RIGHT JOIN语法 . 根据MySql的outer join simplification,通过在查询中的
FROM
和ON
子句中切换t1和t2,可以将右连接转换为等效的左连接 . 因此,MySql查询优化器将原始查询转换为以下内容 -现在,按原样编写原始查询没有什么害处,但是如果你有像WHERE子句这样的谓词,它是before-join谓词或
ON
子句上的AND谓词,这是一个during-join谓词,那么你可能想要看看魔鬼;这是详细的 .MySql查询优化器会定期检查谓词是否为空拒绝 .
现在,如果你已经完成了RIGHT JOIN,但是在t1的列上使用了WHERE谓词,那么你可能会遇到一个被拒绝的情况 .
例如,以下查询 -
由查询优化器转换为以下内容 -
因此,表的顺序已更改,但谓词仍应用于t1,但t1现在位于'ON'子句中 . 如果t1.col1定义为
NOT NULL
列,则此查询将被拒绝 .任何被拒绝的外连接(左,右,全)都会被MySql转换为内连接 .
因此,您可能期望的结果可能与MySql返回的结果完全不同 . 您可能认为它是MySql的RIGHT JOIN的一个错误,但那不对 . 它就是MySql查询优化器的工作原理 . 因此,开发人员在构建查询时必须注意这些细微差别 .
您可以执行以下操作:
你怎么说Cross join解决方案?
这也是可能的,但您必须在select中提及相同的字段名称 .
我修复了响应,并且包括所有行(基于Pavle Lekic的响应)
Answer:
可以重新创建如下:
使用UNION或UNION ALL答案不会涵盖基表具有重复条目的边缘情况 .
Explanation:
有一个UNION或UNION ALL无法覆盖的边缘情况 . 我们无法在mysql上测试它,因为它不支持FULL OUTER JOIN,但我们可以在支持它的数据库上说明这一点:
UNION解决方案:
给出了错误的答案:
UNION ALL解决方案:
也是不正确的 .
鉴于此查询:
给出以下内容:
订单不同,但在其他方面与正确答案匹配 .
SQL标准说
full join on
是inner join on
行union all
不匹配的左表行,由空值扩展union all
右表行由空值扩展 . 即left join on
中的行inner join on
行union all
但right join on
中没有inner join on
union all
行但不是inner join on
.即
left join on
行union all
right join on
行不在inner join on
中 . 或者,如果您知道inner join on
结果在特定的右表列中不能为空,则“right join on
不在inner join on
中的行”是right join on
中的行,on
条件由and
扩展为is null
列 .即类似
right join on
union all
适当的left join on
行 .从What is the difference between “INNER JOIN” and “OUTER JOIN”?: