> ## Documentation Index
> Fetch the complete documentation index at: https://docs.e6data.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Operators and nulls

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

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.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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](https://docs.e6data.com/transformation/sql/functions-string.md).

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.