SQL row value expression with null failing in where-in query

Question

I am struggling to understand why this where-in query is failing when there are nulls in the row value expression.

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)
)

The query is not returning a single record from the below table. I expected each row value expression to match exactly one record. I appears the null spoils the fun and I don't understand why.

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

It works as expected for rows that have non-null values in the MARKET_EXTERNAL_ID field.

Tag your question with the database you are using. – Gordon Linoff Jun 04 '21 at 12:01 — Gordon Linoff, Jun 04 '21 at 12:01

banana_99 · Accepted Answer · 2021-06-04T08:21:54.940

The issue here is that nulls cannot be compared. Thus, you will need to convert the nulls to some value to compare them. In the below example, I've converted them to 0.

Note: NVL is Oracle proprietary, coalesce would be the standard function.

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 )
    )

Output:

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

Thanks! Maybe you could mention that `NVL` is proprietary for Oracle. The equivalent on most other databases is `coalesce`. — Marcel Stör, Jun 04 '21 at 07:24

SQL row value expression with null failing in where-in query

1 Answers1