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.
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;