与 NULL 合并
我在视图中发现了这段 SQL 片段，我对它的目的感到相当困惑（为简洁起见，缩短了实际的 SQL）：
SELECT
COALESCE(b.Foo, NULL) AS Foo
FROM a
LEFT JOIN b ON b.aId=a.Id
我想不出与 null 合并的目的的任何原因，而不是仅仅这样做：
SELECT
b.Foo AS Foo
FROM a
LEFT JOIN b ON b.aId=a.Id
或者至少不要显式地包含 NULL：
SELECT
COALESCE(b.Foo) AS Foo
FROM a
LEFT JOIN b ON b.aId=a.Id
我不知道谁创作了这个（所以我不能问），何时创作，或者它是为哪个特定的 MS SQL Server 版本编写的（当然是 2008 年之前）。
是否有任何有效的理由与 NULL 合并而不是直接选择列？我忍不住笑了，并将其作为菜鸟错误注销，但这让我想知道是否存在一些“边缘”案”，我不知道。
I found this snippet of SQL in a view and I am rather puzzled by it's purpose (actual SQL shortened for brevity):
SELECT
COALESCE(b.Foo, NULL) AS Foo
FROM a
LEFT JOIN b ON b.aId=a.Id
I cannot think of a single reason of the purpose of coalescing with null instead of just doing this:
SELECT
b.Foo AS Foo
FROM a
LEFT JOIN b ON b.aId=a.Id
Or at the very least don't include the NULL explicitly:
SELECT
COALESCE(b.Foo) AS Foo
FROM a
LEFT JOIN b ON b.aId=a.Id
I don't know who authored this (so I cannot ask), when it was authored, or for what specific MS SQL Server version it was written for (pre-2008 for sure though).
Is there any valid reason to coalesce with NULL instead of just selecting the column directly? I can't help but laugh and write it off as a rookie mistake but it makes me wonder if there is some "fringe case" that I don't know about.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
你是对的 - 没有理由使用：
...因为如果
b.foo为 NULL，你不妨使用：...假设你想要知道该值是否为空。
You are right - there is no reason to use:
...because if
b.foois NULL, you might as well just use:...assuming that you want to know if the value is null.
我认为可能是一种边缘情况，但它非常具有历史意义 - 这有点猜测，但它可能围绕 b.foo = ' ' 的情况，例如一串空格。
在 SQL 中回溯得足够远，LTrim(' ') 返回 null (6 / 6.5)，所以我想知道空字符串上的 Coalesce 是否也将其计算为 null，如果是，则该机制用于将空格字符串转换为 null只是，变成空值？ （如果所有值都计算为 null，则 Coalesce 将返回 Null。）
这是一个猜测，我无法立即测试它，但应该不难检查。
I think there might be an edge case, but it is very historic - it's a bit of a guess but it could surround the situation where b.foo = ' ' e.g. a string of spaces.
Go back far enough in SQL and LTrim(' ') returned null (6 / 6.5), so I am wondering whether Coalesce on an empty string also evaluated it to null, if it did then the mechanism was being used to turn strings of spaces only, into null values? (If all values evaluate as null, Coalesce will return Null.)
It's a guess and I can not test it right away, but shouldn't be hard to check.
如果我有多个列，我想检查它们是否全部为空，我会使用它，而不是对每列使用 AND 和 IS NOT NULL，我可以使用单个 COALESCE 这将使代码更简单且更具可读性， 例如
I would use it if I have multiple columns that I want to check whether all of them are null or not, instead of using AND and IS NOT NULL with every column, I can use a single COALESCE which will make the code simpler and more readable, for example