Compare Two Tables on Snowflake Where it Contains NULL Values

Question

Hello everyone i am facing an issue, i.e Table T1 with schema

ID	TYPE	MODEL_ID	FREQ
1	Car	234	5
2	SUV	12	6
3	EV	NULL	NULL
4	HV	NULL	NULL

TABLE T2 with schema and data

ID	TYPE	MODEL_ID	FREQ
3	EV	NULL	NULL
4	HV	NULL	NULL

Now i am checking the working with the following query,

SELECT *  FROM T1 AS T, T2 AS N
  WHERE T.ID = N.ID AND T.TYPE = N.TYPE AND T.MODEL_ID = N.MODEL_ID AND T.FREQ = N.FREQ

The result is empty set, i knew that i need to implement a null safe query but to this scenario how do i achieve it.

Note: Here ID is not an identifier, so should compare with every column in the table.

Answer 1

You could use IS NOT DISTINCT FROM to perform NULL-safe comparison:

Compares whether two expressions are equal (or not equal). The function is NULL-safe, meaning it treats NULLs as known values for comparing equality.

SELECT *  
FROM T1 AS T, T2 AS N 
WHERE T.ID       IS NOT DISTINCT FROM N.ID 
  AND T.TYPE     IS NOT DISTINCT FROM N.TYPE 
  AND T.MODEL_ID IS NOT DISTINCT FROM N.MODEL_ID 
  AND T.FREQ     IS NOT DISTINCT FROM N.FREQ;

---

Another simpler option is usage of INTERSECT set operator:

SELECT * FROM t1
INTERSECT
SELECT * FROM T2;

db<>fiddle demo

Answer 2

One more option:

SELECT *  
FROM T1 AS T, T2 AS N 
WHERE EQUAL_NULL(T.ID,N.ID)
  AND EQUAL_NULL(T.TYPE,N.TYPE)
  AND EQUAL_NULL(T.MODEL_ID,N.MODEL_ID)
  AND EQUAL_NULL(T.FREQ,N.FREQ);