# SQL

[Wiki](../README.md) / [Tables and SQL](README.md)

**On this page**

- [Where SQL runs](#where-sql-runs)
- [Names and parameters](#names-and-parameters)
- [SELECT](#select)
- [Expressions and functions](#expressions-and-functions)
- [INSERT, UPDATE and DELETE](#insert-update-and-delete)
- [Reading by key and index](#reading-by-key-and-index)
- [SQL as text: the sql. procedures](#sql-as-text-the-sql-procedures)
- [Errors](#errors)

SQL in Galactus follows SQLite: the same results for the same data and
query, checked against SQLite on thousands of generated queries. From version
2.1.0. Two choices differ: `LIKE` is
case-sensitive (`ILIKE` ignores case), and `CAST` of text that is not a
number fails instead of reading as `0`.

## Where SQL runs

A braced block marks where SQL starts and ends:

| Form | Gives |
|---|---|
| `CALL (c) SQL { … }` | One Cypher row per SQL row; the columns become variables |
| `SQL { … }` | One value: one column, at most one row, `null` for none |
| `EXISTS SQL { … }` | Whether the block returns a row |
| `COLLECT SQL { … }` | A list: values for one column, maps for several |

```cypher
// Each friend's latest three orders
MATCH (:Customer {id: $id})-[:KNOWS]->(f:Customer)
CALL (f) SQL { SELECT sku, placed_at FROM f.orders ORDER BY placed_at DESC LIMIT 3 }
RETURN f.name, sku, placed_at;

// A summary back onto the node, in the same transaction
MATCH (c:Customer {id: $id})
SET c.lifetime_spend = SQL { SELECT coalesce(sum(amount), 0) FROM c.orders };
```

`INSERT`, `UPDATE` and `DELETE` run only in `CALL (…) SQL { }`. A block is
parsed with the statement, so a syntax error fails before anything runs, and
its compiled plan is kept while the tables' schemas stay the same.

## Names and parameters

- In `FROM`, `c.orders` names a table of a variable in scope. `CALL (c, d)
  SQL` sees `c` and `d`, `CALL (*) SQL` every variable, and `CALL SQL` none.
  The value forms see every variable.
- `$name` is a query parameter or a variable in scope, and `$name.key` reads
  a property; a name that is both is an error. A list of maps also reads as a
  table: `FROM $rows AS r`.
- Parameters are values, never SQL: `$sku` holding `'x'; DROP …` is the
  string it holds. A table name cannot come from a parameter's text.
- Names compare without regard to case where unique; `"x"`, `` `x` `` and
  `[x]` quote a name. A keyword can name a table after `FROM`
  (`FROM Order`).

## SELECT

| Area | Supported |
|---|---|
| Clauses | `SELECT [DISTINCT\|ALL]`, `*`, `t.*`, `FROM`, `WHERE`, `GROUP BY` (expressions, aliases, positions), `HAVING`, `ORDER BY … ASC\|DESC NULLS FIRST\|LAST`, `LIMIT n [OFFSET m]`, `LIMIT m, n`, `OFFSET … FETCH FIRST\|NEXT n ROWS ONLY`, `VALUES` |
| Sources | Tables in scope, lists of maps, `(SELECT …) AS t(a, b)`, `WITH` CTEs, `FROM Type AS o` ([Definitions](definitions.md#reading-every-table-of-a-type)) |
| Joins | `[INNER] JOIN … ON\|USING`, `LEFT [OUTER] JOIN`, `CROSS JOIN`, `,` |
| Set operations | `UNION [ALL]`, `INTERSECT`, `EXCEPT`, with `ORDER BY`/`LIMIT` on the whole |
| Subqueries | Scalar `(SELECT …)`, `EXISTS`, `IN (SELECT …)`, correlated |
| Aggregates | `count(*)`, `count([DISTINCT] x)`, `sum`, `total`, `avg`, `min`, `max`, `group_concat`, `string_agg`, `collect`/`array_agg`, `stddev`, `stddev_samp`, `stddev_pop`, `variance`, `var_samp`, `var_pop`, `bool_and`/`every`, `bool_or`, each with `FILTER (WHERE …)` |

Nulls sort first ascending, as in SQLite. `sum` over no rows is `null`;
`total` is `0.0`. Integer division truncates. Result columns need distinct
names.

## Expressions and functions

Comparison (`= == <> != < <= > >=`), `AND OR NOT`, `IS [NOT] NULL|TRUE|FALSE`,
`IS [NOT] DISTINCT FROM`, `[NOT] BETWEEN`, `[NOT] IN`, `[NOT] LIKE … [ESCAPE]`,
`ILIKE`, `GLOB`, `REGEXP`, arithmetic, `||`, `CASE`, `CAST`/`::`, typed
literals (`DATE '2026-10-24'`) and `CURRENT_DATE`/`TIME`/`TIMESTAMP`.

Functions: `coalesce`, `ifnull`, `nullif`, `iif`, `upper`, `lower`, `length`,
`octet_length`, `substr`/`substring` (SQLite's rules, `FROM … FOR`), `trim`,
`ltrim`, `rtrim`, `replace`, `instr`/`strpos`/`position`, `lpad`, `rpad`,
`repeat`, `reverse`, `left`, `right`, `concat`, `concat_ws`, `split_part`,
`initcap`, `chr`/`char`, `ascii`/`unicode`, `hex`, `typeof`, `abs`, `round`,
`ceil`, `floor`, `trunc`, `sign`, `sqrt`, `cbrt`, `power`, `exp`, `ln`, `log`,
`log10`, `log2`, `mod`, `pi`, trigonometry, `degrees`, `radians`, `greatest`,
`least`, `EXTRACT`/`date_part`, `now()`. Any other name calls the Cypher
function of that name.

## INSERT, UPDATE and DELETE

```cypher
MATCH (c:Customer {id: $id})
CALL (c) SQL {
  INSERT INTO c.orders (id, placed_at, sku, qty, amount)
  VALUES ($orderId, localdatetime(), $sku, $qty, $amount)
  ON CONFLICT (id) DO UPDATE SET qty = orders.qty + excluded.qty
  RETURNING id, qty
}
RETURN id, qty;
```

| Statement | Supported |
|---|---|
| `INSERT INTO t [(cols)]` | `VALUES (…), …` (with `DEFAULT`), `SELECT …`, `DEFAULT VALUES`, `VALUES ($map)` and `VALUES ($rows)` by column name |
| Conflicts | `ON CONFLICT [(key or unique columns)] DO NOTHING \| DO UPDATE SET … [WHERE …]` with `excluded.col`, several clauses in turn; `INSERT OR IGNORE \| REPLACE \| ABORT`, `REPLACE INTO` |
| `UPDATE t SET …` | `col = e`, `(a, b) = (x, y)`, `DEFAULT`; `WHERE`, `ORDER BY … LIMIT` |
| `DELETE FROM t` | `WHERE`, `ORDER BY … LIMIT` |
| All | `RETURNING` items or `*`, a leading `WITH` CTE, an alias (`INSERT INTO c.orders AS o`) |

- Every value is computed before the first write, so `SET a = b, b = a`
  swaps. Types, `NOT NULL`, the primary key, unique indexes and `CHECK`
  constraints are checked for every row.
- A failed statement changes nothing, graph changes in it included; in an
  explicit transaction only that statement is undone.
- Without `RETURNING` a change keeps the outer row; with it, the outer row
  repeats once per changed row (none when nothing changed).
- `MATCH (c:Customer) CALL (c) SQL { UPDATE c.orders … }` changes every
  customer's table. A node without the table is skipped; `INSERT` into one
  is an error naming it.

## Reading by key and index

A select over one table whose `WHERE` bounds the primary key or an index
(`=`, `IN`, `IN $list`, `<`, `<=`, `>`, `>=`, `BETWEEN`, joined by `AND`, on
the leading columns) reads that range instead of the table: on 20,000 rows a
key lookup is more than ten times faster than a scan. The `WHERE` still
filters, so a bound of another type is simply left to it. `sql.explain`
shows the choice.

## SQL as text: the sql. procedures

| Name | Gives |
|---|---|
| `sql.value(text, params)` | The value of a one-column select, as `SQL { }` |
| `CALL sql.query(text, params) YIELD row` | One map per row |
| `CALL sql.page(t, {orderBy, where, limit, after, params}) YIELD row, cursor` | One page (default 100, at most 10,000 rows) and the cursor for the next; `null` after the last |
| `CALL sql.explain(text, params) YIELD plan` | How each table is read (`TableScan`, `TableKeySeek`, `TableIndexSeek`, `GlobalIndexSeek`, `TypeScan`), then the operators |
| `CALL sql.types() YIELD name, columns, indexes, tables, rows, definition` | The table types |

The text sees only `params`: `sql.value('SELECT count(*) FROM $t WHERE qty >
$min', {t: c.orders, min: 3})`. These procedures read; changes run in `CALL
(c) SQL { }`. `sql.page` in key order continues after the last key returned,
so rows written between pages neither repeat nor go missing; in another order
it continues by position.

## Errors

| Code | When |
|---|---|
| `Galactus.ClientError.Schema.ConstraintValidationFailed` | A row breaks `NOT NULL`, the primary key, a unique index or a `CHECK` |
| `Galactus.ClientError.Statement.TypeError` | A value does not fit its column: the wrong type, a decimal without room, a string over its length |
| `Galactus.ClientError.Statement.SqlTooManyRows` | A `SQL { }` value returned more than one row |
| `Galactus.ClientError.Statement.SqlCastFailed` | A `CAST` cannot convert the value |
| `Galactus.ClientError.Statement.TableTooLarge` | A list form or `COLLECT SQL { }` over more rows than the list cap |

These are the Bolt codes drivers receive, from version 2.1.2; a statement that
fails changes nothing, graph changes in it included. Node and relationship
constraints report `ConstraintValidationFailed` too. Messages name the problem
in SQL terms: `CHECK constraint `positive_qty` failed: qty > 0`, `a row with
this primary key (id) already exists`, `no such column: colour`.