我想在MySQL中进行完全的外部连接。这可能吗?MySQL是否支持完全外部联接?
当前回答
我修复了响应,工作包括所有行(基于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
);
其他回答
您可以只转换一个完整的外部联接,例如。
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
巴勃罗·圣克鲁斯给出的答案是正确的;然而,如果有人无意中看到了这一页,并希望得到更多的澄清,这里有一个详细的分类。
示例表
假设我们有以下表格:
-- t1
id name
1 Tim
2 Marta
-- t2
id name
1 Tim
3 Katarina
内连接
内部连接,如下所示:
SELECT *
FROM `t1`
INNER JOIN `t2` ON `t1`.`id` = `t2`.`id`;
将只获取两个表中出现的记录,如下所示:
1 Tim 1 Tim
内部连接没有方向(如左或右),因为它们是显式双向的——我们需要两边都匹配。
外连接
另一方面,外部联接用于查找其他表中可能不匹配的记录。因此,您必须指定允许连接的哪一侧缺少记录。
LEFT JOIN和RIGHT JOIN是LEFT OUTER JOIN和RIGHT OUTER JON的简写;我将在下面使用它们的全名来强化外部连接与内部连接的概念。
左侧外部连接
左外连接,如下所示:
SELECT *
FROM `t1`
LEFT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`;
…将从左表中获取所有记录,无论它们在右表中是否匹配,如下所示:
1 Tim 1 Tim
2 Marta NULL NULL
右侧外部连接
右外部连接,如下所示:
SELECT *
FROM `t1`
RIGHT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`;
…将从右表中获取所有记录,无论它们在左表中是否匹配,如下所示:
1 Tim 1 Tim
NULL NULL 3 Katarina
完全外部联接
一个完整的外部联接将为我们提供两个表中的所有记录,无论它们在另一个表中是否匹配,在没有匹配的两侧都有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看作是“运行这两个查询,然后将结果叠加在一起”;其中一些行来自第一个查询,一些来自第二个查询。
需要注意的是,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。
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中提到相同的字段名。
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没有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查询优化器的工作方式。因此,负责开发人员在构建查询时必须注意这些细微差别。
推荐文章
- 为什么在MySQL中文本列不能有一个默认值?
- 使用SQL查询查找最近的纬度/经度
- 将一列的多个结果行连接为一列,按另一列分组
- 如何停止mysqld
- 检查MySQL表是否存在而不使用“select from”语法?
- 从NOW() -1天选择记录
- 在PostgreSQL中快速发现表的行数
- 更改varchar列的大小为较低的长度
- 从表中选择1是什么意思?
- SQL Server中User和Login的区别
- 数据库性能调优有哪些资源?
- 如何更改表的默认排序规则?
- 为两列的组合添加唯一的约束
- MySQL foreign_key_checks是否影响整个数据库?
- 设置NOW()为datetime数据类型的默认值?