SQL query with many 'AND NOT CONTAINS' statements

user_16628026 0
Viewed 271

I am trying to exclude timezones that have a substring in them so I only have records likely from the US.

The query works fine (e.g., the first line after the OR will remove local_timezones that include 'Africa/Abidjan'), but there's got to be a better way to write it.

It's too verbose, repetitive, and I suspect it's slower than it could be. Any advice greatly appreciated. (I'm using Snowflake's flavor of SQL but not sure that matters in this case).

NOTE: I'd like to keep a timezone such as America/Los_Angeles, but not America/El_Salvador, so for this reason I don't think wildcards are a good solution.

  SELECT a_col
  FROM a_table
  WHERE 
   (country = 'United States')
  OR 
  ((country is NULL and not contains (local_timezone, 'Africa')
  AND
  country is NULL and not contains (local_timezone, 'Asia')
  AND
  country is NULL and not contains (local_timezone, 'Atlantic')
  AND
  country is NULL and not contains (local_timezone, 'Australia')
  AND
  country is NULL and not contains (local_timezone, 'Etc')
  AND
  country is NULL and not contains (local_timezone, 'Europe')
  AND
  country is NULL and not contains (local_timezone, 'Araguaina')
  etc etc
snowflake-cloud-data-platform sql
2 Answers

If you have a known list of "good things" I would make a table, and then just JOIN to id. Here I made you a list of good timezones:

CREATE TABLE acceptable_timezone (tz_name text) AS 
SELECT * FROM VALUES
('Pacific/Auckland'),
('Pacific/Fiji'),
('Pacific/Tahiti');

I love me some Pacific... now we have some important data in a CTE

WITH data(id, timezone) AS (
    SELECT * FROM VALUES
    (1, 'Pacific/Auckland'),
    (2, 'Pacific/Fiji'),
    (3, 'America/El_Salvador')
) 
SELECT d.*
FROM data AS d
JOIN acceptable_timezone AS a
    ON a.tz_name = d.timezone
ORDER BY 1;

which total does not match the El Salvador:

ID TIMEZONE
1 Pacific/Auckland
2 Pacific/Fiji

You cannot get much faster than an equijoin, but if your DATA has the timezones as substrings, then the TABLE can have the wildcard matches % and you can use a LIKE just like Felipe's answer does but as

JOIN acceptable_timezone AS a
    ON d.timezone LIKE a.tz_name
user_439920

You can use LIKE ANY:

with data as 
(select null country, 'something Australia maybe' local_timezone)

select *
from data
where country = 'United States'
or (
    country is null 
    and not local_timezone like any ('%Australia%', '%Africa%', '%Atlantic%')
)
user_1324380
Related