Snowflake Join does not return null values condition matching records from 2 tables

user_13953040 0
Viewed 865

I have a header table and a transaction table. The header table has Id value Null and description has something and Transaction table having Id as null for description something.

Now when I join on Header.Id = Transaction.Id I should have Id having matching values as well as null value as id matching with null value in transaction table.

Like the below query:

SELECT 
    SH.HEADER_COLID,
    SH.HEADER_COLDESCRIPTION,
    S.SALEORDER_MONTH,
    S.SALEORDER_YEAR,
    SUM(S.SALEORDER_AMT),
    SUM(S.SALEORDER_QTYCASES),
    SUM(S.SALEORDER_QTYWGHTPNDS)
FROM SDS_HEADERS SH,  SALEORDERS S  
WHERE SH.HEADER_COLID = S.DIVISION  
    AND SH.COLUMN_ID =1  
    AND S.OPEN_CLOSE='false' 
    AND S.SALEORDER_YEAR='2021'
GROUP BY SH.HEADER_COLID, SH.HEADER_COLDESCRIPTION, 
    S.SALEORDER_MONTH, S.SALEORDER_YEAR
ORDER BY SH.HEADER_COLID, SH.HEADER_COLDESCRIPTION, 
    S.SALEORDER_MONTH, S.SALEORDER_YEAR ASC

I get matching records for SH.HEADER_COLID = S.DIVISION but Null value for HEADER_COLID should be matched against DIVISION null values but I am not getting these records. I need them please help on how to achieve this in snowflake.

snowflake-cloud-data-platform matching join null
1 Answers

Now when I join on Header.Id=Transaction.Id I should have Id having matching values as well as null value as id matching with null value in transaction table.

To treat NULL=NULL as true, NULL safe operator could be used:

ON Header.Id IS NOT DISTINCT FROM Transaction.Id

Sample:

CREATE TABLE t1 AS
SELECT 1 AS id, 'a' AS c UNION
SELECT 2, 'b'            UNION
SELECT NULL, 'c';

CREATE TABLE t2 AS
SELECT 2 AS id UNION
SELECT 3       UNION 
SELECT NULL;

Query:

SELECT *
FROM T1
JOIN T2
  ON T1.ID = T2.ID;
-- id   c   id
-- 2    b   2

SELECT *
FROM T1
JOIN T2
  ON T1.ID IS NOT DISTINCT FROM T2.ID;
-- id   c   id
-- 2    b   2
-- NULL  c  NULL

db<>fiddle demo

EDIT:

Different approaches are possible:

  1. ON col1 IS NOT DISTINCT FROM col2
  2. ON (col1 = col2 OR col1 IS NULL AND col2 IS NULL)
  3. ON EQUAL_NULL(col1, col2)

The usage of specific version depends on taste/convention. IS DISTICT FROM may seems esoteric at first but it is defined in SQL Standard. Related:The IS DISTINCT FROM predicate by Lukas Eder.

Second approach is very explicit about the intent and compatible between multiple RDBMSes though is not conscice. The third approach is using a function EQUAL_NULL that is product specific.

user_50708790
Related