Logical Operators and Conditional and Subquery Expressions

Logical Operator Support

Operator	Description
`AND`	Logical AND
`NOT`	Negates value
`OR`	Logical OR

Conditional Expression Support

Expression	Description
`CASE WHEN condition THEN result ELSE default END`	Case operator
`COALESCE(val1, val2, ..)`	Returns the first non-null value in the list

Geospatial and array column projections are not supported in the COALESCE function and CASE expressions.

Subquery Expression Support

Expression	Description
`expr IN (subquery or list of values)`	Evaluates whether expr equals any value of the IN list.
`expr NOT IN (subquery or list of values)`	Evaluates whether expr does not equal any value of the IN list.

Usage Notes

You can use a subquery anywhere an expression can be used, subject to any runtime constraints of that expression. For example, a subquery in a CASE statement must return exactly one row, but a subquery can return multiple values to an IN expression.
You can use a subquery anywhere a table is allowed (for example, FROM subquery), using aliases to name any reference to the table and columns returned by the subquery.