FULL OUTER JOIN 真的是一件坏事吗?

发布于 2024-07-29 01:27:50 字数 1970 浏览 4 评论 0原文

总的来说,我相信“FULL OUTER JOIN 被认为是有害的”,换句话说。

背景:

http://weblogs.sqlteam。 com/jeffs/archive/2007/04/19/Full-Outer-Joins.aspx

但我确实有一个特定的情况,它会非常方便

鉴于:

CREATE VIEW Calcs(c1, c2, c3, fordate, ...other columns) AS
   /* Complicated set of equations, etc. */

并且:

CREATE TABLE Overrides(c1, c2, c3, fordate)

我需要调整上面的视图以遵循以下逻辑:

  1. 对于计算日期没有相应覆盖的任何计算行,请选择计算值。
  2. 对于计算日期与覆盖日期匹配的任何计算行,请选择覆盖值。
  3. 对于计算中没有对应行的任何覆盖行,请选择覆盖值。

现在,通常我只会执行由三部分组成的查询:

CREATE VIEW Calcs AS ... (current logic)

CREATE VIEW CalcsWithOverrides AS

   SELECT * FROM Calcs WHERE NOT EXISTS (match in Overrides)

   UNION ALL

   SELECT override.c1, override.c2, override.c3, (other non-overridden columns)
       FROM Calcs INNER JOIN Overrides

   UNION ALL

   SELECT *, (null placeholders for non-overridden columns) FROM Overrides WHERE
       NOT EXISTS (match in Calcs)

然而,这似乎比使用 OUTER JOIN 不那么简单:

   SELECT
       COALESCE(Calcs.fordate, Overrides.fordate) AS fordate,
       -- Note that I am not using COALESCE() below on purpose: a null override should still override any calculated value
       CASE WHEN Overrides.fordate IS NULL THEN Calcs.c1 ELSE Overrides.c1 END AS C1,
       CASE WHEN Overrides.fordate IS NULL THEN Calcs.c2 ELSE Overrides.c2 END AS C2,
       CASE WHEN Overrides.fordate IS NULL THEN Calcs.c3 ELSE Overrides.c3 END AS C3,
       (....other columns from calcs that will be null for override-only rows...)
   FROM
       Calcs
       FULL OUTER JOIN Overrides ON Overrides.fordate = Calcs.fordate

那么,在这种情况下 OUTER JOIN 是否合理,或者是否有更好的方法 em> 除了上面的还有其他选择吗?

In general, I believe "FULL OUTER JOIN Considered Harmful", to turn the phrase.

Background:

http://weblogs.sqlteam.com/jeffs/archive/2007/04/19/Full-Outer-Joins.aspx

But I do have a specific situation where it would be really handy:

Given:

CREATE VIEW Calcs(c1, c2, c3, fordate, ...other columns) AS
   /* Complicated set of equations, etc. */

And:

CREATE TABLE Overrides(c1, c2, c3, fordate)

I need to adjust the View above to follow this logic:

  1. For any Calcs rows whose calculated date has no corresponding Override, select the calculated values.
  2. For any Calcs rows whose calculated date matches an Override date, select the override values.
  3. For any Override rows with no corresponding row in Calcs, select the override values.

Now, usually I would just do a three part query:

CREATE VIEW Calcs AS ... (current logic)

CREATE VIEW CalcsWithOverrides AS

   SELECT * FROM Calcs WHERE NOT EXISTS (match in Overrides)

   UNION ALL

   SELECT override.c1, override.c2, override.c3, (other non-overridden columns)
       FROM Calcs INNER JOIN Overrides

   UNION ALL

   SELECT *, (null placeholders for non-overridden columns) FROM Overrides WHERE
       NOT EXISTS (match in Calcs)

And yet, that seems a lot less straightforward than using an OUTER JOIN:

   SELECT
       COALESCE(Calcs.fordate, Overrides.fordate) AS fordate,
       -- Note that I am not using COALESCE() below on purpose: a null override should still override any calculated value
       CASE WHEN Overrides.fordate IS NULL THEN Calcs.c1 ELSE Overrides.c1 END AS C1,
       CASE WHEN Overrides.fordate IS NULL THEN Calcs.c2 ELSE Overrides.c2 END AS C2,
       CASE WHEN Overrides.fordate IS NULL THEN Calcs.c3 ELSE Overrides.c3 END AS C3,
       (....other columns from calcs that will be null for override-only rows...)
   FROM
       Calcs
       FULL OUTER JOIN Overrides ON Overrides.fordate = Calcs.fordate

So, is this a situation where an OUTER JOIN is justified, or is there a better alternative than the one above?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

留一抹残留的笑 2024-08-05 01:27:50

你想要在这里完全加入,所以我认为这根本不被认为是有害的。 这是获得解决方案集的最佳方式。 我会在每种方式上运行查询执行,看看哪种方式最快,但我的猜测是完全连接。

请记住,在处理外连接时,where 子句中的条件与 join 子句中的条件之间存在明显差异。 Where 限制整个结果集,join 限制匹配的行。

You want a full join here, so I don't think it's considered harmful at all. That's the best way to get to your solution set. I'd run the query execution play on each way to see which would be fastest, but my guess is that the full join would be.

Just remember that there is a distinct difference between a condition in a where clause and a condition in a join clause when dealing with outer joins. Where limits the whole result set, join limits the rows that get matched.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文