我想在MySQL中进行完全的外部连接。这可能吗?MySQL是否支持完全外部联接?
当前回答
您在MySQL中没有完全联接,但您可以模拟它们。
对于从堆栈溢出问题中转录的代码示例,您有:
对于两个表t1、t2:
SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
上面的查询适用于完全外部联接操作不会产生任何重复行的特殊情况。上面的查询依赖于UNION集合运算符来删除查询模式引入的重复行。通过对第二个查询使用反连接模式,然后使用UNION ALL集合运算符组合这两个集合,可以避免引入重复的行。在更一般的情况下,如果完全外部联接将返回重复的行,我们可以这样做:
SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION ALL
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
WHERE t1.id IS NULL
其他回答
您在MySQL中没有完全联接,但您可以模拟它们。
对于从堆栈溢出问题中转录的代码示例,您有:
对于两个表t1、t2:
SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
上面的查询适用于完全外部联接操作不会产生任何重复行的特殊情况。上面的查询依赖于UNION集合运算符来删除查询模式引入的重复行。通过对第二个查询使用反连接模式,然后使用UNION ALL集合运算符组合这两个集合,可以避免引入重复的行。在更一般的情况下,如果完全外部联接将返回重复的行,我们可以这样做:
SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION ALL
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
WHERE t1.id IS NULL
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
为了更清晰,我修改了shA.t的查询:
-- 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 t1.value IS NULL
Use:
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;
使用UNION或UNION ALL答案不能涵盖基表具有重复条目的边缘情况。
说明:
UNION或UNION ALL无法覆盖边缘情况。我们无法在MySQL上测试这一点,因为它不支持完整的外部连接,但我们可以在一个支持它的数据库上说明这一点:
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;
这给了我们这样的答案:
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
也不正确。
id1 id2
1 NULL
2 NULL
5 5
6 6
6 6
6 6
6 6
NULL 3
NULL 4
5 5
6 6
6 6
6 6
6 6
鉴于此查询:
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;
给出以下内容:
id1 id2
1 NULL
2 NULL
NULL 3
NULL 4
5 5
6 6
6 6
6 6
6 6
顺序不同,但在其他方面与正确答案匹配。
前面的答案实际上都不正确,因为当存在重复值时,它们不遵循语义。
对于查询,例如(来自此副本):
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;
如果需要使用NULL值(这可能也是必要的),则使用NULL安全比较运算符<=>而不是=。
推荐文章
- 将值从同一表中的一列复制到另一列
- GROUP BY with MAX(DATE)
- 删除id与其他表不匹配的sql行
- 等价的限制和偏移SQL Server?
- MySQL CPU使用率高
- 是什么导致这个ActiveRecord::ReadOnlyRecord错误?
- INT和VARCHAR主键之间有真正的性能差异吗?
- 拒绝访问;您需要(至少一个)SUPER特权来执行此操作
- 为什么我不能在DELETE语句中使用别名?
- 在SQL Server Management Studio中保存带有标题的结果
- 从存储引擎得到错误28
- "where 1=1"语句
- 如何选择一个记录和更新它,与一个单一的查询集在Django?
- 多语句表值函数vs内联表值函数
- 如何从Oracle的表中获取列名?