CODEWITHSUNDEEP
google-bigquery
user2414055
user2414055

Reputation: 21

How to exclude record like WHERE NOT EXISTS

I want to exclude records which include keywords stored in exclusion_list table. In typical DB, we can do this by using WHERE NOT EXISTS phrase like below;

SELECT datetime, server, page, uniq_id, client_req, domain, search_keyword
FROM apache_log a
WHERE NOT EXISTS 
  (SELECT 0 FROM exclusion_list e WHERE regexp_like(a.client_req, e.ex_url))

Is there a way of achieving the same in BigQuery?

Upvotes: 2

Views: 6521

Answers (1)

David Freitag
David Freitag

Reputation: 2272

The closest thing i can find to a SELECT WHERE NOT EXISTS in the bigquery documentation is the SELECT WHERE NOT IN clause. 

SELECT datetime, server, page, uniq_id, client_req, domain, search_keyword
FROM apache_log a
WHERE NOT IN 
  (SELECT 0 FROM exclusion_list e WHERE regexp_like(a.client_req, e.ex_url))

According to the documentation it works in a fairly similar way to the SQL IN condition works, where each argument is evaluated then or'd with all of the other values.

Returns true if expr matches expr1, expr2, or any value in the parentheses. The IN keyword is an efficient shorthand for (expr = expr1 || expr = expr2 || ...). The expressions used with the IN keyword must be constants and they must match the data type of expr.

More information Here.

Upvotes: 3

Related Questions