function is not null is not giving correct results on snowflake-CodePudding

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

CodePudding user response：

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

CodePudding user response：

Cannot you filter on query like below before using JOIN..

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