mysql和redshift都提供 COALESCE 可以在可能存在空值的情况下使用的函数。此查询将为您提供预期结果。

-- 9999999 value used below is a value sure not to exist in other table.
-- can be 0 or any value you choose based on your a prioiri knowledge of the data

WITH test AS (
    SELECT 1 AS orig, 1 AS dest UNION
    SELECT 1 AS orig, 2 AS dest UNION
    SELECT 1 AS orig, NULL AS dest
)
SELECT COUNT(*) FROM test WHERE orig != COALESCE(dest,999999999)

null被视为未知值。比较时 1 != null 结果被评估为false。
因此，在您的情况下，结果是1 1 != 2

正如vkÙu所说，这是由于null不被视为一个值，并且不能与现有值进行比较，因此任何类型的比较都将返回null（因此，与where子句不匹配）。
您可以在本文中找到有关该行为的更多详细信息：https://www.xaprb.com/blog/2006/05/18/why-null-never-compares-false-to-anything-in-sql/
如果你需要有预期的行为，以下是我最终使用的：

WITH test AS (
    SELECT 1 AS orig, 1 AS dest UNION
    SELECT 1 AS orig, 2 AS dest UNION
    SELECT 1 AS orig, NULL AS dest
)
SELECT COUNT(*) FROM test WHERE (
    (orig IS NULL AND dest IS NOT NULL)
    OR
    (orig IS NOT NULL AND dest IS NULL)
    OR
    (orig != dest)
)