SQL reference

LanceDB Enterprise supports SQL through an Arrow Flight SQL endpoint. This reference covers statement syntax, data types and expressions. To set up a client and run your first query, see SQL.

The dialect

LanceDB uses Apache DataFusion for SQL planning and execution. These pages describe LanceDB’s supported statements and extensions. DataFusion’s reference is useful for expression syntax, but does not establish that every DataFusion statement or feature is exposed by LanceDB.

Connecting

Use Python’s execute_query or a Flight SQL client after configuring the Enterprise connection. TypeScript applications use a Flight SQL client for SQL queries.

reader = db.execute_query("SELECT id, text FROM my_docs LIMIT 10")
for batch in reader:
    print(batch)

SQL results arrive as Arrow record batches. Iterating over the reader avoids collecting the entire result in application memory. See Read and manage results for more.

Usage

The endpoint executes one statement per request and is meant for the queries and DDL/DML pages below. Reach for the SDKs instead for table-management operations that have no SQL form, such as a merge insert. See What SQL cannot do.

Reference

DDL, by object


Database	`CREATE`, `DESCRIBE`, `SHOW`, `ATTACH`, `DROP DATABASE`
Namespace	`CREATE`, `ALTER`, `DESCRIBE`, `SHOW`, `DROP SCHEMA`/`NAMESPACE`
Table	`CREATE`, `DESCRIBE`, `ALTER`, `CLONE`, `DROP TABLE`
View	`CREATE VIEW`, `DROP VIEW`
Materialized view	`CREATE MATERIALIZED VIEW`, `SHOW MATERIALIZED VIEWS`
Secret	`CREATE`, `ALTER`, `DESCRIBE`, `SHOW`, `DROP SECRET`
Storage Integration	`CREATE`, `DESCRIBE`, `SHOW`, `DROP STORAGE INTEGRATION`
Index	`CREATE INDEX`, `DROP INDEX`
Function	`CREATE FUNCTION`, `SHOW FUNCTIONS`, `DROP FUNCTION`
Job	`SHOW JOBS`, `SHOW JOB EVENTS`, `DESCRIBE JOB`, `SHOW ERRORS`, `PAUSE`/`RESUME`/`CANCEL JOB`

DML


Insert	`INSERT INTO`, including `INSERT ... SELECT`
Update	`UPDATE ... SET`
Delete	`DELETE FROM`
Copy	`COPY` (refused by recent deployment versions)
Refresh column	filling a computed column
Refresh materialized view	bringing a view’s stored result up to date

DQL


Data types	how Lance columns appear to SQL, and pseudo-columns
Select	projection, filtering, ordering, set operations, CTEs
Subqueries	scalar, `IN`, `EXISTS`, correlated, derived tables
Joins	inner, outer, cross, and namespace-qualified paths
Aggregation	`GROUP BY`, `HAVING`, the aggregate set
Window functions	`OVER`, framing, ranking and offset functions
Sampling	`TABLESAMPLE`, by rows or by fragments
Changes	`CHANGES`: rows appended since an earlier version
Full-Text Search	the `fts()` table function, and its query forms
Operators	comparison, logical, arithmetic, literals, precedence
Functions	where to find each kind, and table functions
Scalar functions	the per-row catalogue, and what Lance accelerates
Explain	`EXPLAIN`, `EXPLAIN ANALYZE`, `EXPLAIN VERBOSE`

SQL limitations and alternatives


Vector search	See SQL vector search for supported query forms and server-version requirements. The SDK query builder also supports vector search.
Merge insert	no SQL form: it needs a source relation and a per-match policy
`information_schema`	not enabled, so `SHOW TABLES` and `SHOW COLUMNS` fail
`COPY`	refused by recent deployment versions (see Copy)
`PREPARE` / `EXECUTE`	preparation is a protocol feature, not a statement
Run on a local database	no engine to plan the statement

See Limits for the bounds these pages enforce.

Introduction

Getting Started

Concepts

Data Ingestion

Search

Data Curation

Feature Engineering

Training

Management

Maintenance

Security

Deployment

SQL reference

The dialect

Connecting

Usage

Reference

DDL, by object

DML

DQL

SQL limitations and alternatives

​The dialect

​Connecting

​Usage

​Reference

​DDL, by object

​DML

​DQL

​SQL limitations and alternatives

The dialect

Connecting

Usage

Reference

DDL, by object

DML

DQL

SQL limitations and alternatives