where-in查询中包含null的SQL行值表达式失败

Question

我很难理解为什么当行值表达式中有空值时，where-in查询会失败。

select * from
FOO
where (
  EPOCH,
  CURRENCY_CODE,
  PRICE_PROVIDER_CODE,
  MARKET_EXTERNAL_ID)
in (
  (1622736580084, 'USD', 'A', null),
  (1622736580085, 'USD', 'B', null),
  (1622736580086, 'USD', 'C', null)
) 

查询没有从下表中返回一条记录。我期望每个行值表达式只匹配一条记录。我觉得null破坏了乐趣，我不明白为什么。

EPOCH           CURENCY_CODE   PRICE_PROVIDER_CODE    MARKET_EXTERNAL_ID
------------------------------------------------------------------------
1622736580084            USD                     A                  NULL
1622736580085            USD                     B                  NULL    
1622736580086            USD                     C                  NULL

对于MARKET_EXTERNAL_ID字段中包含非空值的行，它可以按预期工作。

Answer 1

这里的问题是空值不能比较。因此，您需要将空值转换为某个值以对它们进行比较。在下面的示例中，我已经将它们转换为0。

注意: NVL是Oracle专有的，合并将是标准函数。

SELECT
    *
FROM
    foo
WHERE
    ( epoch,
      currency_code,
      price_provider_code,
      nvl(market_external_id, 0) ) IN (
        ( 1622736580084,
          'USD',
          'A',
          0 ),
        ( 1622736580085,
          'USD',
          'B',
          0 ),
        ( 1622736580086,
          'USD',
          'C',
          0 )
    )

输出：

EPOCH           CURENCY_CODE   PRICE_PROVIDER_CODE    MARKET_EXTERNAL_ID
------------------------------------------------------------------------
1622736580084            USD                     A                  NULL
1622736580085            USD                     B                  NULL    
1622736580086            USD                     C                  NULL