I'm interested to know what the common practices are for this situation.
我很想知道这种情况的常见做法。
You need to find all rows where two columns do not match, both columns are nullable (Exclude where both columns are NULL). None of these methods will work:
您需要查找两列不匹配的所有行，两列都可以为空（排除两列都为NULL的情况）。这些方法都不起作用：
WHERE A <> B --does not include any NULLs
WHERE NOT (A = B) --does not include any NULLs
WHERE (A <> B OR A IS NULL OR B IS NULL) --includes NULL, NULL
Except this...it does work, but I don't know if there is a performance hit...
除此之外......它确实有效，但我不知道是否有性能损失......
WHERE COALESCE(A, '') <> COALESCE(B, '')
Lately I've started using this logic...it's clean, simple and works...would this be considered the common way to handle it?:
最近我开始使用这个逻辑......它干净，简单，有效...这会被认为是处理它的常用方法吗？：
WHERE IIF(A = B, 1, 0) = 0
--OR
WHERE CASE WHEN A = B THEN 1 ELSE 0 END = 0
2 个解决方案
#1
4
This is a bit painful, but I would advise direct boolean logic:
这有点痛苦，但我建议直接布尔逻辑：
where (A <> B) or (A is null and B is not null) or (A is not null and B is null)
or:
要么：
where not (A = B or A is null and B is null)
It would be much simpler if SQL Server implemented is distinct from, the ANSI standard, NULL-safe operator.
如果实现的SQL Server与ANSI标准，NULL安全的运算符不同，那将简单得多。
If you use coalesce(), a typical method is:
如果使用coalesce（），则典型方法是：
where coalesce(A, '') <> coalesce(B, '')
This is used because '' will convert to most types.
这是因为''将转换为大多数类型。
#2
0
How about using except ?
使用除外？
for example if i want to get all a and b that is not a=b and exclude all null values of a and b
例如，如果我想得到所有a和b不是a = b并且排除a和b的所有空值
select a, b from tableX where a is not null and b is not null
except
select a,b from tableX where a=b
#1
4
This is a bit painful, but I would advise direct boolean logic:
这有点痛苦，但我建议直接布尔逻辑：
where (A <> B) or (A is null and B is not null) or (A is not null and B is null)
or:
要么：
where not (A = B or A is null and B is null)
It would be much simpler if SQL Server implemented is distinct from, the ANSI standard, NULL-safe operator.
如果实现的SQL Server与ANSI标准，NULL安全的运算符不同，那将简单得多。
If you use coalesce(), a typical method is:
如果使用coalesce（），则典型方法是：
where coalesce(A, '') <> coalesce(B, '')
This is used because '' will convert to most types.
这是因为''将转换为大多数类型。
#2
0
How about using except ?
使用除外？
for example if i want to get all a and b that is not a=b and exclude all null values of a and b
例如，如果我想得到所有a和b不是a = b并且排除a和b的所有空值
select a, b from tableX where a is not null and b is not null
except
select a,b from tableX where a=b