本页尚未翻译

本页内容仍为英文原文，源自官方文档 sql/dql/operators。中文翻译正在分批进行中。

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

说明

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

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

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.