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:
|=||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|
The least familiar operators that most will observe from that list are LIKE and IN.
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
- % = 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.
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.
Conditions are separated by the operators,
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'
There are two limitations with the WHERE clause that are commonly asked about, which are aliased column names and aggregations.
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%'
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.