我想在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 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中提到相同的字段名。

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

我修复了响应,工作包括所有行(基于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
    );

SQL标准表示,上的完全联接是行上的内部联接,所有不匹配的左表行由null扩展,所有右表行由nulls扩展。也就是说,行上的内部联接将左联接中的所有行联合在一起,而不是联合在一起的内部联接。

也就是说,行上的左联接和不在上的内联接中的行上的所有右联接。或者,如果您知道结果的内联接在某个特定的右表列中不能为空,那么“行上的右联接不在上内联接中”是右联接中的列,条件扩展为,并且该列为空。

也就是说,在并集上进行右连接,在行上进行所有适当的左连接。

来自“INNER JOIN”和“OUTER JOIN”之间的区别是什么?:

(SQL标准2006 SQL/Foundation 7.7语法规则1,通用规则1 b,3 c&d,5 b)