SQL Reference

This section provides a comprehensive reference for SQL support in Spice.ai, including syntax, data types, operators, functions, and system features. The reference is organized by topic for ease of navigation.

Table of Contents

SELECT Syntax

• WITH Clause
• SELECT Clause
• FROM Clause
• WHERE Clause
• JOIN Clause
• GROUP BY Clause
• HAVING Clause
• UNION Clause
• ORDER BY Clause
• LIMIT Clause
• EXCLUDE and EXCEPT Clause

Subqueries

• Subquery Operators
• SELECT Clause Subqueries
• FROM Clause Subqueries
• WHERE Clause Subqueries
• HAVING Clause Subqueries
• Subquery Categories

EXPLAIN

• EXPLAIN ANALYZE

Information Schema

• SHOW TABLES
• SHOW COLUMNS
• SHOW ALL (configuration options)

AI Functions

• ai (LLM Text Generation)
• embed (Vector Embeddings)

Operators and Literals

• Numerical Operators
• Comparison Operators
• Logical Operators
• Bitwise Operators
• Other Operators

Scalar Functions

• Math Functions
• Conditional Functions
• String Functions
• Binary String Functions
• Regular Expression Functions
• Time and Date Functions
• Array Functions
• Struct Functions
• Map Functions
• Hashing Functions
• Union Functions
• Other Functions

Spark-compatible scalar functions such as array, bit_get, date_add, like, and parse_url follow the semantics documented in the Spark SQL built-in function reference.

Aggregate Functions

• General Aggregate Functions
• Statistical Aggregate Functions
• Approximate Aggregate Functions

JSON Functions and Operators

• JSON Functions
• JSON Operators
• Usage Examples

Search

• Vector Search (vector_search)
• Full-Text Search (text_search)
• Lexical Search: LIKE, =, and Regex

Prepared Statements

• Positional Arguments

DML (Data Manipulation Language)

• INSERT Statement

Refer to each section for detailed syntax, supported features, and examples.