The operator menu
Operators help you write more specific filters.
Common WHERE operators include:
!=or<>: not equal to.>and>=: greater than, greater than or equal to.<and<=: less than, less than or equal to.IN (...): found in a list of items.NOT: negates a condition.LIKEorILIKE: contains or matches a text pattern.BETWEEN: within a range.%: wildcard used withLIKE.
Examples:
SELECT *
FROM `project.dataset.table`
WHERE column != 'Inactive';
SELECT *
FROM `project.dataset.table`
WHERE age >= 18;
SELECT *
FROM `project.dataset.table`
WHERE zip_code IN ('30303', '30308', '30310');
SELECT *
FROM `project.dataset.table`
WHERE last_name LIKE 'G%';
BETWEEN is inclusive. This means BETWEEN 10 AND 20 includes 10 and 20.
The wildcard % represents any sequence of characters. For example, LIKE 'a%' returns rows where the value starts with "a." LIKE '%item%' returns rows where the value contains "item."
Learner action
Choose one operator from the menu and write a filter that could help your team narrow a list.
From the deck (slide 37). These are the operators you'll reach for inside a WHERE clause:
| Operator | Meaning | Example |
|---|---|---|
= | Equal to | WHERE state = 'GA' |
!= or <> | Not equal to | WHERE state != 'GA' |
>, >= | Greater than / or equal | WHERE year >= 2020 |
<, <= | Less than / or equal | WHERE age <= 17 |
IN ( … ) | Found in a list | WHERE state IN ('GA','FL','AL') |
NOT | Negates a condition | WHERE NOT state = 'GA' |
LIKE | String pattern with % and _ | WHERE name LIKE 'Mar%' |
BETWEEN … AND … | Within a range, inclusive | WHERE year BETWEEN 2010 AND 2020 |
% | Wildcard inside LIKE (any sequence of chars) | WHERE name LIKE '%a' |
From the deck : no CONTAINS in BigQuery
There is no CONTAINS operator in standard SQL. Use LIKE '%item%' instead. Some databases have ILIKE for case-insensitive search : BigQuery uses LOWER(column) LIKE 'pattern' for the same effect.
Action: Pick one operator beyond = that matches your question. Most learners use IN ( … ) or BETWEEN.