When I run my query in Snowflake it keeps running but without the OR statement is runs in merely sec-CodePudding

I have a query where I select 3 columns from two tables. First I check if a clientnumber is in the list, for which I used a "IN" statement. Then I check if a clientnumber is also in another table so that both numbers are equal.

At last I want to exclude values that are ending on a certain value. And this is where it goes wrong.

When I have just a single "NOT LIKE" it runs perfectly fine but when I use a "OR NOT LIKE" after that it keeps on running.

See my query below:

SELECT "MAIL_ADDR", "ClientNumber", "CNumber" 
FROM "ADRESSES", CUSTOMERS
WHERE "ClientNumber" IN ('0000206302','0000206307','0000206309','0000206321'                                        
    AND "ClientNumber" = "CNumber"
    AND "MAIL_ADDR" NOT LIKE '%domain.nl' 
    OR "MAIL_ADDR" NOT LIKE '%domain.com'

I don't know what to change in my query as I don't get any error messages from Snowflake. Maybe it has something to do with performance issues.

CodePudding user response：

The query could be rewritten to use JOIN syntax and condition for exclusion should be connected with AND:

SELECT "MAIL_ADDR", "ClientNumber", "CNumber" 
FROM "ADRESSES"
JOIN CUSTOMERS
  ON "ClientNumber" = "CNumber"
WHERE "ClientNumber" IN ('0000206302','0000206307','0000206309','0000206321')
    AND "MAIL_ADDR" NOT LIKE '%domain.nl' 
    AND "MAIL_ADDR" NOT LIKE '%domain.com';

A more compact way is to use NOT LIKE ANY syntax:

SELECT "MAIL_ADDR", "ClientNumber", "CNumber" 
FROM "ADRESSES"
JOIN CUSTOMERS
  ON "ClientNumber" = "CNumber"
WHERE "ClientNumber" IN ('0000206302','0000206307','0000206309','0000206321')       
 AND NOT ("MAIL_ADDR" LIKE ANY ('%domain.nl', '%domain.com'));