SQL 处理 NULL 和 Empty String 值时编写联合查询的最佳方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3525396/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Best way to write union query when dealing with NULL and Empty String values
提问by dretzlaff17
I have to write a query that performs a union between two tables with similar data. The results need to be distinct. The problem I have is that some fields that should be the same are not when it comes to empty values. Some are indicated as null, and some have empty string values. My question is, is there a better way to perform the following query? (without fixing the actual data to ensure proper defaults are set, etc) Will using the Case When be a big performance hit?
我必须编写一个查询，在具有相似数据的两个表之间执行联合。结果需要是不同的。我的问题是，当涉及到空值时，一些应该相同的字段不是。有些表示为空，有些表示为空字符串值。我的问题是，有没有更好的方法来执行以下查询？（不修复实际数据以确保设置正确的默认值等） 使用 Case When 是否会对性能造成很大影响？
Select
When Column1 = '' Then NULL Else Column1 as [Column1],
When Column2 = '' Then NULL Else Column2 as [Column2]
From TableA
UNION ALL
Select
When Column1 = '' Then NULL Else Column1 as [Column1],
When Column2 = '' Then NULL Else Column2 as [Column2]
From TableB
回答by Joe Stefanelli
I don't think it would make any difference in performance, but NULLIFis another way to write this and, IMHO, looks a little cleaner.
我认为它不会对性能产生任何影响，但NULLIF是另一种写法，恕我直言，看起来更简洁一些。
Select
NULLIF(Column1, '') as [Column1],
NULLIF(Column2, '') as [Column2]
From TableA
UNION
Select
NULLIF(Column1, '') as [Column1],
NULLIF(Column2, '') as [Column2]
From TableB
回答by Andomar
A Caseshould perform fine, but IsNullis more natural in this situation. And if you're searching for distinct rows, doing a unioninstead of a union allwill accomplish that (thanks to Jeffrey L Whitledge for pointing this out):
ACase应该表现良好，但IsNull在这种情况下更自然。如果您正在搜索不同的行，执行 aunion而不是 aunion all将实现这一点（感谢 Jeffrey L Whitledge 指出这一点）：
select IsNull(col1, '')
, IsNull(col2, '')
from TableA
union
select IsNull(col1, '')
, IsNull(col2, '')
from TableB
回答by OMG Ponies
Use UNIONto remove duplicates - it's slower than UNION ALLfor this functionality:
使用UNION删除重复-这是慢UNION ALL了此功能：
SELECT CASE
WHEN LEN(LTRIM(RTRIM(column1))) = 0 THEN NULL
ELSE column1
END AS column1,
CASE
WHEN LEN(LTRIM(RTRIM(column2))) = 0 THEN NULL
ELSE column2
END AS column2
FROM TableA
UNION
SELECT CASE
WHEN LEN(LTRIM(RTRIM(column1))) = 0 THEN NULL
ELSE column1
END,
CASE
WHEN LEN(LTRIM(RTRIM(column2))) = 0 THEN NULL
ELSE column2
END
FROM TableB
I changed the logic to return NULL if the column value contains any number of spaces and no actual content.
如果列值包含任意数量的空格且没有实际内容，我将逻辑更改为返回 NULL。
CASEexpressions are ANSI, and more customizable than NULLIF/etc syntax.
CASE表达式是 ANSI，并且比 NULLIF/etc 语法更可定制。
回答by Beth
You can keep your manipulation operations separate from the union if you do whatever manipulation you want (substitute NULL for the empty string) in a separate view, then union the views.
如果您在单独的视图中进行任何您想要的操作（用 NULL 替换空字符串），您可以将您的操作操作与联合分开，然后联合视图。
You shouldn't have to apply the same manipulation on both sets, though.
但是，您不必对两组应用相同的操作。
If that's the case, union them first, then apply the manipulation to the resulting, unioned set once.
如果是这种情况，请先合并它们，然后将操作应用于生成的合并集合一次。
Half as much manipulation code to support that way.
支持这种方式的操作代码减半。
