# SQL Reference

Complete SQL reference for Spice.ai including SELECT syntax, subqueries, DML statements, aggregate functions, AI functions, JSON operators, and search capabilities.

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](https://spice.ai/docs/sql/select)

- [WITH Clause](https://spice.ai/docs/sql/select#with-clause)
- [SELECT Clause](https://spice.ai/docs/sql/select#select-clause)
- [FROM Clause](https://spice.ai/docs/sql/select#from-clause)
- [WHERE Clause](https://spice.ai/docs/sql/select#where-clause)
- [JOIN Clause](https://spice.ai/docs/sql/select#join-clause)
- [GROUP BY Clause](https://spice.ai/docs/sql/select#group-by-clause)
- [HAVING Clause](https://spice.ai/docs/sql/select#having-clause)
- [QUALIFY Clause](https://spice.ai/docs/sql/select#qualify-clause)
- [UNION Clause](https://spice.ai/docs/sql/select#union-clause)
- [ORDER BY Clause](https://spice.ai/docs/sql/select#order-by-clause)
- [LIMIT Clause](https://spice.ai/docs/sql/select#limit-clause)
- [EXCLUDE and EXCEPT Clause](https://spice.ai/docs/sql/select#exclude-except-replace-and-ilike-clauses)

### [Subqueries](https://spice.ai/docs/sql/subqueries)

- [Subquery Operators](https://spice.ai/docs/sql/subqueries#subquery-operators)
- [SELECT Clause Subqueries](https://spice.ai/docs/sql/subqueries#select-clause-subqueries)
- [FROM Clause Subqueries](https://spice.ai/docs/sql/subqueries#from-clause-subqueries)
- [WHERE Clause Subqueries](https://spice.ai/docs/sql/subqueries#where-clause-subqueries)
- [HAVING Clause Subqueries](https://spice.ai/docs/sql/subqueries#having-clause-subqueries)
- [Subquery Categories](https://spice.ai/docs/sql/subqueries#subquery-categories)

### [EXPLAIN](https://spice.ai/docs/sql/explain)

- [EXPLAIN ANALYZE](https://spice.ai/docs/sql/explain#explain-analyze)
- [EXPLAIN Options](https://spice.ai/docs/sql/explain#explain-options)

### [Information Schema](https://spice.ai/docs/sql/information-schema)

- [SHOW TABLES](https://spice.ai/docs/sql/information-schema#show-tables)
- [SHOW COLUMNS](https://spice.ai/docs/sql/information-schema#show-columns)
- [SHOW ALL (configuration options)](https://spice.ai/docs/sql/information-schema#show-all-configuration-options)

### [AI Functions](https://spice.ai/docs/sql/ai-functions)

- [ai (LLM Text Generation)](https://spice.ai/docs/sql/ai-functions#ai)
- [Decision functions (ai\_if, ai\_probability, ai\_classify, ai\_score, ai\_decide)](https://spice.ai/docs/sql/ai-functions#decision-functions)
- [embed (Vector Embeddings)](https://spice.ai/docs/sql/ai-functions#embed)

### [Operators and Literals](https://spice.ai/docs/sql/operators)

- [Numerical Operators](https://spice.ai/docs/sql/operators#numerical-operators)
- [Comparison Operators](https://spice.ai/docs/sql/operators#comparison-operators)
- [Logical Operators](https://spice.ai/docs/sql/operators#logical-operators)
- [Bitwise Operators](https://spice.ai/docs/sql/operators#bitwise-operators)
- [Type Casting Operators](https://spice.ai/docs/sql/operators#type-casting-operators)
- [Other Operators](https://spice.ai/docs/sql/operators#other-operators)
- [Literals](https://spice.ai/docs/sql/operators#literals)

### [Scalar Functions](https://spice.ai/docs/sql/scalar-functions)

- [Math Functions](https://spice.ai/docs/sql/scalar-functions#math-functions)
- [Conditional Functions](https://spice.ai/docs/sql/scalar-functions#conditional-functions)
- [String Functions](https://spice.ai/docs/sql/scalar-functions#string-functions)
- [Binary String Functions](https://spice.ai/docs/sql/scalar-functions#binary-string-functions)
- [Regular Expression Functions](https://spice.ai/docs/sql/scalar-functions#regular-expression-functions)
- [Time and Date Functions](https://spice.ai/docs/sql/scalar-functions#time-and-date-functions)
- [Array Functions](https://spice.ai/docs/sql/scalar-functions#array-functions)
- [Struct Functions](https://spice.ai/docs/sql/scalar-functions#struct-functions)
- [Map Functions](https://spice.ai/docs/sql/scalar-functions#map-functions)
- [Hashing Functions](https://spice.ai/docs/sql/scalar-functions#hashing-functions)
- [Encoding Functions](https://spice.ai/docs/sql/scalar-functions#encoding-functions)
- [Union Functions](https://spice.ai/docs/sql/scalar-functions#union-functions)
- [Other Functions](https://spice.ai/docs/sql/scalar-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](https://spark.apache.org/docs/latest/api/sql/index.html).

### [Aggregate Functions](https://spice.ai/docs/sql/aggregate-functions)

- [Filter Clause](https://spice.ai/docs/sql/aggregate-functions#filter-clause)
- [WITHIN GROUP / Ordered-set Aggregates](https://spice.ai/docs/sql/aggregate-functions#within-group--ordered-set-aggregates)
- [General Aggregate Functions](https://spice.ai/docs/sql/aggregate-functions#general-functions)
- [Statistical Aggregate Functions](https://spice.ai/docs/sql/aggregate-functions#statistical-functions)
- [Approximate Aggregate Functions](https://spice.ai/docs/sql/aggregate-functions#approximate-functions)

### Window Functions

Window functions perform calculations across sets of rows related to the current row. Spice supports window functions using the `OVER` clause with aggregate and ranking functions. See [Aggregate Functions](https://spice.ai/docs/sql/aggregate-functions) for functions that support the `OVER` clause, including `ROW_NUMBER`, `RANK`, `DENSE_RANK`, `PERCENT_RANK`, `CUME_DIST`, `NTILE`, `LAG`, `LEAD`, `FIRST_VALUE`, `LAST_VALUE`, and `NTH_VALUE`.

**Example:**

```sql
SELECT
  dept_id,
  employee_name,
  salary,
  ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rank
FROM employees;
```

### [JSON Functions and Operators](https://spice.ai/docs/sql/json)

- [JSON Functions](https://spice.ai/docs/sql/json#json-functions)
- [JSON Operators](https://spice.ai/docs/sql/json#json-operators)
- [Usage Examples](https://spice.ai/docs/sql/json#usage-examples)

### [Search](https://spice.ai/docs/sql/search)

- [Vector Search (`vector_search`)](https://spice.ai/docs/sql/search#vector-search-vector_search)
- [Full-Text Search (`text_search`)](https://spice.ai/docs/sql/search#full-text-search-text_search)
- [Reciprocal Rank Fusion (`rrf`)](https://spice.ai/docs/sql/search#reciprocal-rank-fusion-rrf)
- [Reranking (`rerank`)](https://spice.ai/docs/sql/search#reranking-rerank)
- [Lexical Search: LIKE, =, and Regex](https://spice.ai/docs/sql/search#lexical-search-like--and-regex)

### [Prepared Statements](https://spice.ai/docs/sql/prepared-statements)

- [Positional Arguments](https://spice.ai/docs/sql/prepared-statements#positional-arguments)

### [DML (Data Manipulation Language)](https://spice.ai/docs/sql/dml)

- [INSERT Statement](https://spice.ai/docs/sql/dml#insert)

### Data Types

Spice uses Apache Arrow data types internally. For data type compatibility with accelerators, see [Data Type Reference](https://spiceai.org/docs/reference/datatypes). Common SQL types include:

| SQL Type | Description |
| - | - |
| `INT`, `BIGINT` | Integer types |
| `FLOAT`, `DOUBLE` | Floating-point types |
| `VARCHAR`, `TEXT` | String types |
| `BOOLEAN` | Boolean type |
| `TIMESTAMP` | Timestamp with nanosecond precision |
| `DATE` | Date type |
| `DECIMAL` | Arbitrary precision numeric |

Use `CAST(expression AS type)` or `expression::type` to convert between types.

#### Comparing Decimals with Fractional Literals

A fractional number literal such as `12345678.90` is planned as a `DOUBLE` (`Float64`). When the literal is compared with or cast to a `DECIMAL`, Spice converts it from its decimal digits rather than from its binary floating-point value, within the limits described below, so the comparison matches the value as written:

```sql
SELECT count(*) AS n
FROM (SELECT CAST('12345678.90' AS DECIMAL(10, 2)) AS price) AS t
WHERE price = 12345678.90;
```

```
+---+
| n |
+---+
| 1 |
+---+
```

Bound `Float64` and `Float32` query parameters, such as `$1` in a [prepared statement](https://spice.ai/docs/sql/prepared-statements), have no written digits, so Spice converts them from the shortest decimal spelling of the value.

For both literals and parameters, the conversion applies when the digits fit: at most 15 significant digits and 15 digits before the decimal point (6 for a `Float32` parameter), no more fraction digits than the decimal's scale, and no more digits than its precision. Any other value keeps the standard floating-point cast, with its rounding and overflow behavior. For example, `CAST(1.234 AS DECIMAL(10, 2))` needs rounding, `CAST(0.30000000000000004 AS DECIMAL(38, 18))` has 17 significant digits, and a bound `1e30` cast to `DECIMAL(38, 0)` has 31 digits before the decimal point, so all three keep the floating-point cast.

As an exception, a literal that a `Float64` stores as a different, shorter number, such as `4.0700000000000003` (stored as `4.07`), is converted from its written digits and rounded half away from zero to the decimal's scale. When one statement writes two different numbers that read as the same `Float64`, such as `price = 4.0700000000000003 OR price = 4.07`, Spice cannot tell those literals apart, and both keep the floating-point cast.

To compare against a value outside these limits exactly, write it as a string cast, for example `CAST('0.30000000000000004' AS DECIMAL(38, 18))`, or pass a `Decimal128` parameter.

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