Skip to main content
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

For keyword matching on a text column, 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.

​
Arithmetic

+ - * / %, with the usual precedence. String concatenation is ||.
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; whether SQL can express it depends on the Enterprise server version.

​
Literals

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

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.