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

# Operators and literals

> Comparison, logical and arithmetic operators, literals, and precedence.

**Applies to:** Embedded ❌, Enterprise ✅

LanceDB uses DataFusion's SQL operators. This page describes comparison,
logical and arithmetic operators, literal values, type conversions and
operator precedence.

## Comparison

| | |
| - | - |
| `=` `!=` `<>` `<` `<=` `>` `>=` | ordinary comparison |
| `IS NULL` / `IS NOT NULL` | null test (`= NULL` is never true) |
| `IS DISTINCT FROM` | comparison that treats null as a value |
| `BETWEEN a AND b` | inclusive range |
| `IN (...)` | set membership |
| `LIKE` / `ILIKE` | pattern match, `%` and `_` |
| `~` `~*` `!~` | regular expression match |

> [!NOTE]
>
> For keyword matching on a text column, [`contains_tokens`](https://docs.lancedb.com/sql/dql/contains-tokens)
> is index-accelerated where `LIKE` is not. Reach for `LIKE` only when you need
> its wildcard semantics.

## Logical

`AND`, `OR`, `NOT`. Three-valued: a comparison against null is **unknown**, and
`NOT unknown` is still unknown. A `WHERE` clause keeps only rows where the
predicate is true, so unknown behaves like false there, but not inside `NOT
IN`, which is the usual place this bites. See
[Subqueries](https://docs.lancedb.com/sql/dql/subqueries.md#in-exists).

## Arithmetic

`+` `-` `*` `/` `%`, with the usual precedence. String concatenation is `||`.

> [!WARNING]
>
> Arithmetic over a vector column is **list** arithmetic, not scalar arithmetic:
> a vector is a `FixedSizeList<Float32>`. When the operation you want is a
> similarity comparison, use [vector search](https://docs.lancedb.com/search/vector-search.md); whether SQL
> can express it [depends on the Enterprise server version](https://docs.lancedb.com/basics/sql.md#no-vector-sql).

## Literals

| | |
| - | - |
| String | `'text'`, with a doubled quote to escape: `'it''s'` |
| Number | `42`, `3.14` |
| Boolean | `TRUE`, `FALSE` |
| Null | `NULL` |
| Date / timestamp | `DATE '2026-01-01'`, `TIMESTAMP '2026-01-01 12:00:00'` |
| Interval | `INTERVAL '7 days'` |
| Array | `[1, 2, 3]` |

Identifiers are quoted with double quotes when they need to be (a reserved word
or a name with mixed case): `SELECT "order" FROM "MyTable"`.

## Casting

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
SELECT CAST(identifier AS VARCHAR) AS with_cast, identifier::VARCHAR AS with_colons
FROM documents
```

Both forms work. Give each its own alias when one projection uses both: they
produce the same expression name, and a projection refuses two columns with
one name. `TRY_CAST` returns null instead of erroring on a value that
will not convert, which is the one to use over a column you do not control.

## Precedence

Highest to lowest: `::` → unary `-` → `* / %` → `+ -` → `||` → comparison →
`IS` → `NOT` → `AND` → `OR`.

Parenthesize anything mixing `AND` and `OR`. `a OR b AND c` is `a OR (b AND c)`,
which is rarely what was meant.


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