Multiple OR operator in Snowflake in WHERE Clause is not working

user5aX21 0
Viewed 30

I am trying to do below

Table 1

enter image description here

Table 2

enter image description here

I am writing a query like below to ensure that if any of the NOT IN satisfies, those records should be filtered out.

SELECT * FROM TABLE1 

WHERE TABLE1."DEPTID" NOT IN (SELECT TABLE2."DEPTID" FROM TABLE2)

OR

TABLE1."EMPCOUNTRY" NOT IN (SELECT TABLE2."EMPCOUNTRY" FROM TABLE2)

OR

TABLE1."EMPZONE" NOT IN (SELECT TABLE2."EMPZONE" FROM TABLE2)

But it errors out

What am I doing wrong?

Edited : Exact Query is working with nvl , but result set is not as per requirement.

SELECT * FROM TABLE1 AS T1

WHERE 

(
  UPPER (T1."DEPTID") NOT IN
    (SELECT UPPER (nvl (T2."DEPTID", '')) FROM TABLE2 AS T2)

OR
 UPPER (T1."EMPZONE") NOT IN
    (SELECT UPPER (nvl (T2."EMPZONE",'')) FROM TABLE2 AS T2)
)

Result - set is not as per requirement, it should filter out if there any DEPTID in the Table2 or if there is any EMPZONE in table 2 or both etc.

What should be the best way to achieve this?

snowflake-cloud-data-platform
2 Answers

I think the issue is about NULL values coming from the subqueries. Could you try to use something like this?

SELECT * FROM TABLE1 AS T1
WHERE 
(
    NOT EXISTS (SELECT 1 FROM TABLE2 T2 WHERE equal_null( UPPER(T1."DEPTID"), UPPER(T2."DEPTID")) )
OR
    NOT EXISTS (SELECT 1 FROM TABLE2 T2 WHERE equal_null( UPPER(T1."EMPZONE"), UPPER(T2."EMPZONE")) )
);

Also here is a sample to demonstrate why you can't use NOT IN with a subquery returning NULL values:

create table NULL_TABLE ( v varchar);
insert into NULL_TABLE values (NULL),('ABC');

create or replace table MAIN_TABLE ( v varchar);
INSERT INTO MAIN_TABLE values
('Jack'),('Joe'),('ABC');

select * from MAIN_TABLE
where v NOT IN (select v FROM NULL_TABLE);

The last query returns NULL, because we can't determine if a value does not exist in series of numbers where some of them are not known - the last WHERE clause.

Gokhan Atil0

When using NOT IN the subquery should not allow null values as reselut, second conditions to happen all at once should be joined with AND instead of OR.

NOT (cond1 OR cond2 OR cond3)
<=>
(NOT cond1) AND (NOT cond2) AND (NOT cond3)

De Morgan's law: "The negation of a disjunction is the conjunction of the negations"

The final query should rather be:

SELECT * 
FROM TABLE1 AS T1
WHERE T1."DEPTID"     NOT IN (SELECT T2."DEPTID" FROM TABLE2 T2 
                              WHERE T2."DEPTID" IS NOT NULL)
  AND T1."EMPCOUNTRY" NOT IN (SELECT T2."EMPCOUNTRY" FROM TABLE2 T2 
                              WHERE T2."EMPCOUNTRY" IS NOT NULL)
  AND T1."EMPZONE"    NOT IN (SELECT T2."EMPZONE" FROM TABLE2 T2 
                              WHERE T2."EMPZONE" IS NOT NULL);
Lukasz Szozda0
Related