我想在MySQL中进行完全的外部连接。这可能吗?MySQL是否支持完全外部联接?
当前回答
前面的答案实际上都不正确,因为当存在重复值时,它们不遵循语义。
对于查询,例如(来自此副本):
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安全比较运算符<=>而不是=。
其他回答
前面的答案实际上都不正确,因为当存在重复值时,它们不遵循语义。
对于查询,例如(来自此副本):
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安全比较运算符<=>而不是=。
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
*
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);
为了更清晰,我修改了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
这也是可能的,但您必须在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
推荐文章
- 为什么在MySQL中文本列不能有一个默认值?
- 使用SQL查询查找最近的纬度/经度
- 将一列的多个结果行连接为一列,按另一列分组
- 如何停止mysqld
- 检查MySQL表是否存在而不使用“select from”语法?
- 从NOW() -1天选择记录
- 在PostgreSQL中快速发现表的行数
- 更改varchar列的大小为较低的长度
- 从表中选择1是什么意思?
- SQL Server中User和Login的区别
- 数据库性能调优有哪些资源?
- 如何更改表的默认排序规则?
- 为两列的组合添加唯一的约束
- MySQL foreign_key_checks是否影响整个数据库?
- 设置NOW()为datetime数据类型的默认值?