Operators and nulls

ReferenceApplies to: Unified engine deployments

Operators combine values into expressions. Use parentheses to make precedence explicit, especially when mixing AND with OR or combining numeric operations.

Comparisons and logic

Operator or predicate	Result
`=`, `<>`, `!=`, `<`, `<=`, `>`, `>=`	Value comparison
`AND`, `OR`, `NOT`	Three-valued Boolean logic
`IS NULL`, `IS NOT NULL`	A non-null Boolean test for missing values
`BETWEEN low AND high`	Inclusive range test
`IN (value, ...)`	Membership comparison
`LIKE pattern`	Text matching with `%` for a sequence and `_` for one character

An ordinary comparison with NULL usually returns NULL. WHERE and HAVING retain only TRUE. Use IS NULL to test for missing values: column = NULL does not find them. In Boolean logic, FALSE AND NULL is FALSE, TRUE OR NULL is TRUE, and TRUE AND NULL is NULL.

SELECT i_item_sk, i_category,
       i_category IS NULL AS missing_category,
       i_category IN ('Books', 'Electronics') AS selected_category
FROM demo.tpcds.item
ORDER BY i_item_sk
LIMIT 10;

Text patterns

LIKE is case-sensitive unless another operation explicitly normalizes the input. Use LOWER or UPPER when a case-normalized match is intended. Regular-expression functions have separate syntax and behavior; see string functions.

SELECT i_item_sk, i_item_desc
FROM demo.tpcds.item
WHERE LOWER(i_item_desc) LIKE '%green%'
ORDER BY i_item_sk
LIMIT 10;

Arithmetic

+, -, *, /, and remainder forms operate on compatible numeric inputs. Type, overflow, division by zero, and ANSI settings affect behavior. Decimal and floating-point arithmetic have different precision. Native execution rejects expressions for which it cannot preserve the required semantics, including some integer arithmetic and cast paths.

SELECT i_item_sk,
       CAST(i_current_price AS DOUBLE) AS price,
       CAST(i_current_price AS DOUBLE) * 1.10 AS adjusted_price
FROM demo.tpcds.item
WHERE i_current_price IS NOT NULL
ORDER BY i_item_sk
LIMIT 5;

Conditional expressions

CASE chooses a result by condition. All result branches must resolve to a compatible type. COALESCE supplies the first non-null value; NULLIF converts an equal value to NULL. These are useful for presenting missing dimensions and preparing denominators.

SELECT i_item_sk,
       CASE WHEN i_current_price IS NULL THEN 'missing'
            WHEN i_current_price < 20 THEN 'under 20'
            ELSE '20 or more' END AS price_band,
       COALESCE(i_category, 'Unknown') AS category
FROM demo.tpcds.item
ORDER BY i_item_sk
LIMIT 10;

Null-safe equality and null-aware join conditions have Native backend restrictions. A null-safe expression that works as a scalar expression does not establish support for using it as a join condition.

Transformation Engine

Learn Laminar

Workloads

SQL reference

HTTP API

Recipes

Operations

Comparisons and logic

Text patterns

Arithmetic

Conditional expressions

​Comparisons and logic

​Text patterns

​Arithmetic

​Conditional expressions

Comparisons and logic

Text patterns

Arithmetic

Conditional expressions