The WHERE clause is the filtering function for the relational data query layer. WHERE accepts conditions that are evaluated when the query is executed and the records that meet these conditions will be generated in the final result set. The filter condition operators that are allowed are:
| Condition Expression | Description |
|---|---|
| = | Equal to the provided value |
| < | Less than the provided value |
| <= | Less than or equal to the provided value |
| > | Greater than the provided value |
| >= | Greater than or equal to the provided value |
| <> or != | Not equal to the provided value |
| IS NULL or IS NOT NULL | Is a blank value or is not a blank value |
| LIKE or NOT LIKE | Contains the provided expression or does not contain the provided expression |
| IN or NOT IN | Equals each of the provided values or does not equal each of the provided values |
| BETWEEN | Between the range of the start and end values provided |
Unique Operators
The least familiar operators that most will observe from that list are LIKE and IN.
LIKE / NOT LIKE - LIKE is one of the most flexible operators that you can use. It searches for specific patterns in a given column and accepts wildcard characters % and _
- % = Zero, one, or multiple characters
- _ = A single character
This operator is case-sensitive so the casing of the values you provide will matter, but a workaround to this limitation is to use ILIKE which is the same as LIKE, but disregards the casing of the value that is provided. NOT LIKE will look for any records that do not match the pattern provided.
IN / NOT IN - IN captures a grouping of provided values in a comma separated list that will be evaluated for a given column. This operator is commonly used to simplify repetitive condition chaining like first_name = ‘Amy’ AND first_name = ‘Scott’, which can be replaced by first_name IN (‘Amy’, ‘Scott’). NOT IN will exclude any records that match the values provided within the parentheses.
Multiple Conditions
Conditions are separated by the operators, AND and OR and can be grouped with the use of parentheses ().
...
WHERE (
(signup_date BETWEEN ‘2020-01-01’ AND ‘2020-01-31’)
AND status IN ('paying', 'trial')
)
OR membership_type = 'VIP'
WHERE Limitations
There are two limitations with the WHERE clause that are commonly asked about, which are aliased column names and aggregations.
Alias
You must directly reference the column names. Alias values are not allowed and will throw an error since the SQL execution order executes WHERE before SELECT. However, there is a common workaround with the use of subqueries that will allow you to use the aliased value name.
-- This query will throw an error
SELECT user_name AS full_name
FROM users
WHERE full_name LIKE '%Ted%'
-- This query will run successfully
SELECT full_name
FROM (
SELECT user_name AS full_name
FROM users
) x
WHERE full_name LIKE '%Ted%'
Aggregations
WHERE does not accept aggregated values. In order to filter on aggregates, you must use the HAVING clause. WHERE is executed within the relational data layer, whereas aggregations occur in the non-relational layer which begins with the GROUP BY clause which executes after the WHERE clause.