Conditionals and Filters
Additional flexibility when composing a query
Conditionals and Filters Overview
Conditions are used to define what logic is to be executed based on the status of some condition being satisfied. Filters are expressions used to specify a subset of the data items.
CASE
Case statement. Expressions are evaluated in order, and the first to evaluate to true is returned.
ELSE returns a default value if no statement evaluates to true. If there is no ELSE condition and no case evaluates to true, then NULL is returned.
Operator
CASE
WHEN exprs THEN result1
[ WHEN exprs THEN result2 ]
…
[ ELSE default ]
END
Example
CASE
WHEN state = 'CA' THEN 1
WHEN score > 500 THEN 1
ELSE 0
END
CASE expression
“Simple” case statement. This is similar to the above, except on checks equality between a common expression and values.
Operator
CASE expression
WHEN value THEN result
[WHEN ...]
[ELSE result]
END
Example
CASE state
WHEN 'CA' THEN 1
WHEN 'TX' THEN 1
ELSE 0
END
WHERE clause
Where clause. Takes a list of filter expressions.
NOTE: we do not currently support subqueries in WHERE clauses.
Operator
WHERE filter_exprs [ AND | OR filter_exprs ]
Example
WHERE
state = 'CA'
FILTER
Filter with a where clause. The where clause only applies to the aggregation preceding the FILTER keyword. Otherwise, the WHERE clause is identical to any other WHERE clause.
Operator
aggregate_function( * ) [ FILTER ( WHERE filter_exprs ) ]
Example
COUNT(*)
FILTER (
WHERE
state = 'CA' OR state = 'TX'
)
Last updated
Was this helpful?