8wDlpd.png
8wDFp9.png
8wDEOx.png
8wDMfH.png
8wDKte.png

如何在 MySQL 中执行 FULL OUTER JOIN?

Ben Trewern 1月前

68 0

我想在 MySQL 中执行完全外连接。这可能吗?MySQL 支持完全外连接吗?

我想在 MySQL 中执行 完全外连接 。这可能吗? MySQL 支持 完全外连接

帖子版权声明 1、本帖标题:如何在 MySQL 中执行 FULL OUTER JOIN?
    本站网址:http://xjnalaquan.com/
2、本网站的资源部分来源于网络,如有侵权,请联系站长进行删除处理。
3、会员发帖仅代表会员个人观点,并不代表本站赞同其观点和对其真实性负责。
4、本站一律禁止以任何方式发布或转载任何违法的相关信息,访客发现请向站长举报
5、站长邮箱:yeweds@126.com 除非注明,本帖由Ben Trewern在本站《mysql》版块原创发布, 转载请注明出处!
最新回复 (0)
  • AVS 1月前 0 只看Ta
    引用 2

    请注意这里的答案。SQL 标准说,完全连接是行上的内连接,联合所有不匹配的左表行,扩展为空值,联合所有右表行,扩展为空值。这里的大多数答案都是错误的(见评论),而那些没有错的答案并没有处理一般情况。即使有很多(不合理的)赞成票。(见我的回答。)

  • 当您尝试通过非主键/分组列进行连接时会怎样?例如,我有一个查询,查询每个州的销售额\'state\',\'sells\',另一个查询每个州的费用\'state\',\'expenses\',两个查询都使用 group by(\'state\')。当我在两个查询之间进行左连接和右连接时,我得到了几行有销售额但没有费用的行,还有几行有费用但没有销售额的行,直到这一点为止,但我还得到了一些既有销售额又有费用的行,以及一个重复的\'state\'列...问题不大,但感觉不对劲...

  • @JairoLozano 查询不需要约束。尽管当约束包含额外的查询时,会返回原本不会返回的所需答案。约束不会影响完整连接对给定参数的返回结果。您描述的问题是您编写的查询是错误的查询。(大概是常见的错误,人们想要一些连接,每个连接可能涉及不同的键,一些子查询,每个子查询可能涉及连接和/或聚合,但他们错误地尝试进行所有连接,然后进行所有聚合或对先前的聚合进行聚合。)

  • Pete 1月前 0 只看Ta
    引用 5

    所有使用 UNION 而不是 UNION ALL 的答案都是错误的。所有带有子查询或 3 个联合选择的答案都是低效的。正确的答案将对左连接的所有部分进行联合,并从第二个表中选择,并在第一个表上进行 where not exist (或等效的外连接 + where =NULL 条件)

  • 没有 完整的连接 ,但你可以 模拟它们 .

    从此 Stack Overflow 问题 中转录的 代码 示例 ,您有:

    有两个表 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
    
  • 实际上你写的内容并不正确。因为当你执行 UNION 时,你会删除重复项,而有时当你连接两个不同的表时应该会有重复项。

  • @The Impaler:这里有一些矛盾。得票最高的答案以“Pablo Santa Cruz 给出的答案是正确的”开头。也许可以更具体地说明哪些答案和哪些评论支持这一说法?

  • @NickstandswithUkraine 这只会增加混乱。我删除了警告。请随意添加更好的警告,这样天真的用户就不会使用第一个解决方案。也许应该将其完全删除。

  • @TheImpaler 我确实同意第二个代码块之前的解释....写得不好而且令人困惑,我只是不认为 [第一个块在第一个] 是必然称整个答案错误的原因。我认为更好的解决方案是重新排序,以便第二部分在前面,而(当前)第一个更像是一个附录,用于不需要重复的行。但我并不是一个真正的 SQL 专家,所以应该把它留给那些知道自己在写什么的人。

  • 答案 Pablo Santa Cruz 是正确的;但是,如果有人偶然发现这个页面并想要获得更多说明,这里有一个详细的分类。

    示例表

    假设我们有下表:

    -- 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 and RIGHT OUTER JOIN ;下面我将使用它们的全名来强化外连接与内连接的概念。

    左外连接

    左外连接,如下所示:

    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 “运行这两个查询,然后将结果堆叠在一起”;其中一些行来自第一个查询,而一些来自第二个查询。

    需要注意的是, UNION MySQL 中的 会消除精确重复项: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 .

  • 对于 MySQL,如果没有重叠,您确实应该避免使用 UNION 而不是 UNION ALL(请参阅上面 Pavle 的评论)。如果您可以在此处的答案中添加更多信息,我认为这将是此问题的首选答案,因为它更为详尽。

  • 来自“数据库大师同事”的建议是正确的。就关系模型而言(所有理论工作均由 Ted Codd 和 Chris Date 完成),最后一种形式的查询模拟了 FULL OUTER JOIN,因为它结合了两个不同的集合,第二个查询不会引入 FULL OUTER JOIN 不会产生的“重复项”(第一个查询已返回的行)。以这种方式执行查询并使用 UNION 删除这些重复项并没有什么问题。但要真正复制 FULL OUTER JOIN,我们需要其中一个查询是反连接的。

  • @IstiaqueAhmed:目标是模拟 FULL OUTER JOIN 操作。我们需要在第二个查询中使用这个条件,以便它只返回没有匹配项的行(反连接模式)。如果没有这个条件,查询就是外连接……它返回匹配的行以及不匹配的行。并且匹配的行已由第一个查询返回。如果第二个查询(再次)返回相同的行,则我们复制了行,并且我们的结果将不等同于 FULL OUTER JOIN。

  • @IstiaqueAhmed:UNION 操作确实会删除这些重复项;但它也会删除所有重复行,包括 FULL OUTER JOIN 返回的重复行。要模拟 FULL JOIN b,正确的模式是 (a LEFT JOIN b) UNION ALL (b ANTI JOIN a)。

  • 使用 联合 从不删除任何重复项 完全外连接 的行为不同

    [Table: t1]        [Table: t2]
    value              value
    -----------        -------
    1                  1
    2                  2
    4                  2
    4                  5
    

    完全外连接 的预期结果

    value | value
    ------+-------
    1     | 1
    2     | 2
    2     | 2
    Null  | 5
    4     | Null
    4     | Null
    

    右连接 以及 并集 的结果

    value | value
    ------+-------
    Null  | 5
    1     | 1
    2     | 2
    4     | Null
    

    SQL提琴

    我建议的查询是:

    select
        t1.value, t2.value
    from t1
    left outer join t2
      on t1.value = t2.value
    union all      -- Using `union all` instead of `union`
    select
        t1.value, t2.value
    from t2
    left outer join t1
      on t1.value = t2.value
    where
        t1.value IS NULL
    

    上述查询的结果与预期结果相同:

    value | value
    ------+-------
    1     | 1
    2     | 2
    2     | 2
    4     | NULL
    4     | NULL
    NULL  | 5
    

    SQL提琴


    @Steve Chambers :[来自评论,非常感谢!]

    注意: 这可能是最好的解决方案,既能提高效率,又能生成与 FULL OUTER JOIN . 此博客文章 ,也很好地解释了这一点 - 引用方法 2: "This handles duplicate rows correctly and doesn’t include anything it shouldn’t. It’s necessary to use UNION ALL instead of plain UNION , which would eliminate the duplicates I want to keep. This may be significantly more efficient on large result sets, since there’s no need to sort and remove duplicates."


    我决定添加另一个来自 全外连接 可视化和数学的解决方案。它并不比上面的更好,但更易读:

    完全外连接意味着 (t1 ∪ t2) :全部在 t1 或在 t2 (t1 ∪ t2) = (t1 ∩ t2) + t1_only + t2_only :全部在两者中 t1 并且 t2 加上所有 t1 不在的 t2 并且加上所有 t2 不在的 t1

    -- (t1 ∩ t2): all in both t1 and t2
    select t1.value, t2.value
    from t1 join t2 on t1.value = t2.value
    union all  -- And plus
    -- all in t1 that not exists in t2
    select t1.value, null
    from t1
    where not exists( select 1 from t2 where t2.value = t1.value)
    union all  -- and plus
    -- all in t2 that not exists in t1
    select null, t2.value
    from t2
    where not exists( select 1 from t1 where t2.value = t1.value)
    

    SQL提琴

  • 这种方法似乎是最好的解决方案,既能提高效率,又能生成与 FULL OUTER JOIN 相同的结果。这篇博文也很好地解释了这一点 - 引用方法 2:“这可以正确处理重复行,并且不会包含任何不应该包含的内容。有必要使用 UNION ALL 而不是普通的 UNION,这将消除我想要保留的重复项。对于大型结果集,这可能效率更高,因为不需要排序和删除重复项。\'

  • 遗憾的是,如果您尝试使用临时表执行此操作,则这在 MySQL 或旧版本的 MariaDB 中不起作用。自 2005 年以来的 MySQL 错误。MariaDB 已在 10.2.1 中修复。

  • 前面的答案实际上都不正确,因为它们不遵循重复值时的语义。

    此重复的 查询

    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 -safe 比较运算符, <=> 而不是 = .

  • 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 的 外连接简化 的 t1 和 t2,将右连接转换为等效的左连接 FROM and ON 。因此,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 子句这样的谓词,它是一个 连接前 谓词,或者一个子句上的 AND 谓词 ON ,它是一个 连接期间 谓词,那么您可能需要看一下魔鬼;这在细节中。

    MySQL 查询优化器会定期检查谓词是否 为空拒绝 .

    Null-Rejected Definition and Examples

    现在,如果您已经完成了 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 列,则此查询将被 拒绝为空 .

    被空拒绝的 外连接(左、右、全) 转换为内连接。

    因此,您期望的结果可能与 MySQL 返回的结果完全不同。您可能认为这是 MySQL 的 RIGHT JOIN 的一个错误,但事实并非如此。这只是 MySQL 查询优化器的工作方式。因此,负责的开发人员在构建查询时必须注意这些细微差别。

返回
作者最近主题: