CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
lalaland
lalaland

Reputation: 341

function is not null is not giving correct results on snowflake

I am doing a left join with 2 tables, and I get null values. I have a condition that says where datetime IS NOT NULL. However, when I run the query I still see null values.

Does anyone know how to fix this issue?

select 
*
from TBL_A A
LEFT JOIN 
(select 
    number_id, 
    country, 
    status, 
    number_of_days,
    datetime
FROM TBL_B) B 
    ON A.NUMBER_ID = B.NUMBER_ID
    AND A.STATUS = B.STATUS
    AND A.DATETIME < B.check_date
    AND B.datetime IS NOT NULL

Upvotes: 0

Views: 4554

Answers (2)

Anand
Anand

Reputation: 564

Can't you filter on the query like below, before using the JOIN?

 select 
        number_id, 
        country, 
        status, 
        number_of_days,
        datetime
    FROM TBL_B where datetime is not null

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175706

I have a condition that says where datetime IS NOT NULL

The condition is at ON level:

select *
from TBL_A A
LEFT JOIN (select number_id,  country, status, number_of_days, datetime
           FROM TBL_B) B 
    ON A.NUMBER_ID = B.NUMBER_ID
    AND A.STATUS = B.STATUS
    AND A.DATETIME < B.check_date
    AND B.datetime IS NOT NULL  -- here

In order filter out NULLS it should be at WHERE level:

select *
from TBL_A A
LEFT JOIN (select number_id,  country, status, number_of_days, datetime
           FROM TBL_B) B 
    ON A.NUMBER_ID = B.NUMBER_ID
    AND A.STATUS = B.STATUS
    AND A.DATETIME < B.check_date
WHERE B.datetime IS NOT NULL

But at this moment it is not different that INNER JOIN:

select *
from TBL_A A
JOIN (select number_id,  country, status, number_of_days, datetime
           FROM TBL_B) B 
    ON A.NUMBER_ID = B.NUMBER_ID
    AND A.STATUS = B.STATUS
    AND A.DATETIME < B.check_date
    AND B.datetime IS NOT NULL  -- here

Upvotes: 3

Related Questions