我想在MySQL中进行完全的外部连接。这可能吗?MySQL是否支持完全外部联接?
当前回答
在SQLite中,您应该执行以下操作:
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
其他回答
MySQL没有FULL-OUTER-JOIN语法。您必须通过如下方式执行LEFT JOIN和RIGHT JOIN来模拟它:
SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
但MySQL也没有RIGHT JOIN语法。根据MySQL的外部连接简化,通过切换查询中FROM和ON子句中的t1和t2,将右连接转换为等效的左连接。因此,MySQL查询优化器将原始查询转换为以下内容-
SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT * FROM t2
LEFT JOIN t1 ON t2.id = t1.id
现在,按原样编写原始查询是没有害处的,但是如果您有WHERE子句之类的谓词,这是一个预连接谓词,或者on子句上的AND谓词,它是一个在连接期间的谓词,那么您可能需要看看魔鬼;这是详细的。
MySQL查询优化器定期检查谓词是否被null拒绝。
现在,如果您已经执行了RIGHT JOIN,但在t1开始的列上使用了WHERE谓词,那么您可能会遇到空拒绝的情况。
例如,查询
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列,则此查询将被null拒绝。
任何被null拒绝的外部联接(左、右、全)都会被MySQL转换为内部联接。
因此,您可能期望的结果可能与MySQL返回的结果完全不同。您可能会认为MySQL的RIGHT JOIN存在缺陷,但这是不对的。这就是MySQL查询优化器的工作方式。因此,负责开发人员在构建查询时必须注意这些细微差别。
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
SQL标准表示,上的完全联接是行上的内部联接,所有不匹配的左表行由null扩展,所有右表行由nulls扩展。也就是说,行上的内部联接将左联接中的所有行联合在一起,而不是联合在一起的内部联接。
也就是说,行上的左联接和不在上的内联接中的行上的所有右联接。或者,如果您知道结果的内联接在某个特定的右表列中不能为空,那么“行上的右联接不在上内联接中”是右联接中的列,条件扩展为,并且该列为空。
也就是说,在并集上进行右连接,在行上进行所有适当的左连接。
来自“INNER JOIN”和“OUTER JOIN”之间的区别是什么?:
(SQL标准2006 SQL/Foundation 7.7语法规则1,通用规则1 b,3 c&d,5 b)
您可以只转换一个完整的外部联接,例如。
SELECT fields
FROM firsttable
FULL OUTER JOIN secondtable ON joincondition
进入:
SELECT fields
FROM firsttable
LEFT JOIN secondtable ON joincondition
UNION ALL
SELECT fields (replacing any fields from firsttable with NULL)
FROM secondtable
WHERE NOT EXISTS (SELECT 1 FROM firsttable WHERE joincondition)
或者,如果firsttable中至少有一列(比如foo)不为NULL,则可以执行以下操作:
SELECT fields
FROM firsttable
LEFT JOIN secondtable ON joincondition
UNION ALL
SELECT fields
FROM firsttable
RIGHT JOIN secondtable ON joincondition
WHERE firsttable.foo IS 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安全比较运算符<=>而不是=。
推荐文章
- 为什么在MySQL中文本列不能有一个默认值?
- 使用SQL查询查找最近的纬度/经度
- 将一列的多个结果行连接为一列,按另一列分组
- 如何停止mysqld
- 检查MySQL表是否存在而不使用“select from”语法?
- 从NOW() -1天选择记录
- 在PostgreSQL中快速发现表的行数
- 更改varchar列的大小为较低的长度
- 从表中选择1是什么意思?
- SQL Server中User和Login的区别
- 数据库性能调优有哪些资源?
- 如何更改表的默认排序规则?
- 为两列的组合添加唯一的约束
- MySQL foreign_key_checks是否影响整个数据库?
- 设置NOW()为datetime数据类型的默认值?