CODEWITHSUNDEEP
google-bigquery
Joseph Ishak
Joseph Ishak

Reputation: 1276

How to add a WHERE clause to BigQuery Query to find rows where a column value is not in a list of values

I have a table in BigQuery named events that has a column named 'entitlement'. That column can have values such as 'lapb', 'lacf', 'lasg', 'laxy', 'laza' etc. It can also have 'null' or '' or other values that I don't know. It will only have 1 given value per row so it isn't a list of values. So for example, here is some sample data:

name    entitlement date
alice   laxy        01072017
bob     null        01082018
charlie lapb        01092017
daniel  lacf        01092017

etc

How can I write a query so that I can check if the entitlement value is not in a list of values?

This is how I am currently doing it. I am adding an additional "and entitlement != clause for each one. 

SELECT *
FROM events 
WHERE entitlement != 'lapb' and entitlement != 'lacf' 
and entitlement != 'lasg' and entitlement != 'laxy' 
and entitlement != 'laza' and entitlement is not null
order by logtime desc
LIMIT 1000

I tried doing the following:

SELECT *
FROM events 
WHERE entitlement is not in ['labp', 'lpaf']
order by logtime desc
LIMIT 1000

but I get the following error:

Query Failed Error: Encountered "" at line 3, column 33.

Is there a way in BigQuery to check for only rows that do not contain values in a list or must I add an individual "and entitlement != ..." clause for each additional value? Basically the query is really becoming large and complicated very quickly and I was trying to find a way to simplify it for manageability and readability purposes. I tried square brackets, parentheses, nothing, etc but all give me the same error.

Upvotes: 7

Views: 20646

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172954

Below is for BigQuery Standard SQL 

#standardSQL
WITH events AS (
  SELECT 'alice' AS name, 'laxy' AS entitlement, '01072017' AS DATE UNION ALL
  SELECT 'bob', NULL, '01082018' UNION ALL
  SELECT 'mike', '', '01082018' UNION ALL
  SELECT 'charlie', 'lapb', '01092017' UNION ALL
  SELECT 'dan', 'n/a', '01082018' UNION ALL
  SELECT 'daniel', 'lacf', '01092017'
), known_entitlements AS (
  SELECT entitlement 
  FROM UNNEST(['laxy','lapb','lacf']) entitlement
)
SELECT e.*
FROM events e
LEFT JOIN known_entitlements k
ON e.entitlement = k.entitlement
WHERE k.entitlement IS NULL

You can add all known entitlements into known_entitlements or you can just have all in separate table

result is 

name    entitlement date     
bob     null        01082018     
mike                01082018     
dan     n/a         01082018

Upvotes: 2

Elliott Brossard
Elliott Brossard

Reputation: 33705

#standardSQL
WITH events AS (
  SELECT 'alice' AS name, 'laxy' AS entitlement, '01072017' AS date UNION ALL
  SELECT 'bob', NULL, '01082018' UNION ALL
  SELECT 'charlie', 'lapb', '01092017' UNION ALL
  SELECT 'daniel', 'lacf', '01092017'
)
SELECT *
FROM events
WHERE entitlement NOT IN ('labp', 'lpaf');
+---------+-------------+----------+
| name    | entitlement | date     |
+---------+-------------+----------+
| alice   | laxy        | 01072017 |
| charlie | lapb        | 01092017 |
| daniel  | lacf        | 01092017 |
+---------+-------------+----------+

Upvotes: 5

Related Questions