Skip to content

SQL Queries

For examples on how to run SQL queries in each client library, see the individual driver documentation.

At the top-level, XTDB SQL queries augment the SQL standard in the following ways:

  • SELECT is optional - if not provided, it defaults to SELECT *

  • FROM is optional - if not provided, it defaults to a 0-column, 1-row table.

    This enables queries of the form SELECT 1 + 2 - e.g. to quickly evaluate a data-less calculation.

  • SELECT may optionally be provided between GROUP BY and ORDER BY, for readability - at the place in the pipeline where it’s actually evaluated.

  • GROUP BY is inferred if not provided to be every column reference used outside of an aggregate function.

    e.g. for SELECT a, SUM(b) FROM foo, XT will infer GROUP BY a

<with clause> <query term> ORDER BY <value> ASC DESC NULLS FIRST LAST , LIMIT LIMIT <limit> <limit> LIMIT LIMIT <limit> <limit> OFFSET OFFSET <offset> <offset> OFFSET OFFSET <offset> <offset> OFFSET OFFSET <offset> <offset> OFFSET OFFSET <offset> <offset> LIMIT LIMIT <limit> <limit> LIMIT LIMIT <limit> <limit> WITH MATERIALIZED <query name> AS ( <query> ) ,
  • WITH clauses in XTDB are ‘optimization fences’ - XTDB will not attempt to optimize an outer query into a WITH clause, or across WITH clauses.
  • Supply MATERIALIZED to eagerly materialize a WITH clause, so that the results can be re-used multiple times in the same query.
  • WITH RECURSIVE is not yet supported in XTDB.
<select clause> <from clause> WHERE WHERE <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> , , , , <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> , , , , <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> , , , , <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> , , , , WHERE WHERE <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> , , , , <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> , , , , <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> , , , , <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> , , , , GROUP GROUP BY BY <value> <value> , , <value> <value> , , GROUP GROUP BY BY <value> <value> , , <value> <value> , , GROUP GROUP BY BY <value> <value> , , <value> <value> , , GROUP GROUP BY BY <value> <value> , , <value> <value> , , HAVING HAVING <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> , , , , <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> , , , , <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> , , , , <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> , , , , HAVING HAVING <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> , , , , <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> , , , , <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> , , , , <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> , , , , HAVING HAVING <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> , , , , <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> , , , , <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> , , , , <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> , , , , HAVING HAVING <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> , , , , <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> , , , , <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> , , , , <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> , , , , <from clause> WHERE WHERE <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> , , , , <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> , , , , <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> , , , , <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> , , , , WHERE WHERE <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> , , , , <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> , , , , <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> , , , , <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> , , , , GROUP GROUP BY BY <value> <value> , , <value> <value> , , GROUP GROUP BY BY <value> <value> , , <value> <value> , , GROUP GROUP BY BY <value> <value> , , <value> <value> , , GROUP GROUP BY BY <value> <value> , , <value> <value> , , HAVING HAVING <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> , , , , <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> , , , , <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> , , , , <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> , , , , HAVING HAVING <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> , , , , <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> , , , , <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> , , , , <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> , , , , HAVING HAVING <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> , , , , <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> , , , , <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> , , , , <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> , , , , HAVING HAVING <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> , , , , <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> , , , , <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> , , , , <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> <predicate> , , , , <select clause> VALUES ( <value> , ) , XTQL <XTQL query> ( <XTQL query> , ? ) <query term> UNION INTERSECT EXCEPT ALL DISTINCT <query term> ( <query term> )

NB:

  • SELECT is optional in XTDB - if not provided, it defaults to SELECT *
  • SELECT may be placed after GROUP BY in XTDB, so that the query clauses are written in the order that they’re executed in practice.
  • GROUP BY is optional in XTDB - if not provided, it defaults to all of the columns used outside of an aggregate function.
  • If you start your query with FROM, you may then include arbitrarily many sets of WHERE/GROUP BY/SELECT clauses, which will be evaluated in order.
  • Predicates can be comma-separated in XTDB, to aid with SQL generation - these are treated as conjuncts. There may be an arbitrary number of commas at the start, between any two predicate expressions, or at the end.
  • XTQL queries are sent within an SQL string literal - e.g. '(-> (from ...) ...)'. Given the possible presence of single quotes within the query, it is recommended to use dollar-delimited strings here: XTQL $$ <xtql query> $$

Due to implementation details in some drivers (e.g. PGJDBC), it is required to additionally specify the params in standard SQL (?) following your XTQL query, so that the driver knows how many arguments to allow.

For more details on XTQL queries, see the XTQL documentation.

SELECT * <star exclude> <star exclude> <star exclude> <star exclude> <star rename> <star rename> <star rename> <star rename> <star exclude> <star exclude> <star exclude> <star exclude> <star rename> <star rename> <star rename> <star rename> <value> AS <column name> <table name> . * <star exclude> <star exclude> <star exclude> <star exclude> <star rename> <star rename> <star rename> <star rename> <star exclude> <star exclude> <star exclude> <star exclude> <star rename> <star rename> <star rename> <star rename> , EXCLUDE <column name> ( <column name> , ) RENAME <column name> <column name> AS AS <column name> <column name> <column name> <column name> AS AS <column name> <column name> ( <column name> <column name> AS AS <column name> <column name> <column name> <column name> AS AS <column name> <column name> , ) FROM <relation> ( <column name> , ) , ( <relation> ) <table name> <query name> <temporal filter> <table alias> <relation> LEFT LEFT RIGHT RIGHT LEFT LEFT RIGHT RIGHT OUTER OUTER OUTER OUTER LEFT LEFT RIGHT RIGHT LEFT LEFT RIGHT RIGHT OUTER OUTER OUTER OUTER INNER INNER LEFT LEFT RIGHT RIGHT LEFT LEFT RIGHT RIGHT OUTER OUTER OUTER OUTER LEFT LEFT RIGHT RIGHT LEFT LEFT RIGHT RIGHT OUTER OUTER OUTER OUTER INNER INNER JOIN <relation> ON <predicate> USING ( <column name> , ) <relation> CROSS JOIN <relation> <relation> NATURAL LEFT LEFT RIGHT RIGHT LEFT LEFT RIGHT RIGHT OUTER OUTER OUTER OUTER LEFT LEFT RIGHT RIGHT LEFT LEFT RIGHT RIGHT OUTER OUTER OUTER OUTER INNER INNER LEFT LEFT RIGHT RIGHT LEFT LEFT RIGHT RIGHT OUTER OUTER OUTER OUTER LEFT LEFT RIGHT RIGHT LEFT LEFT RIGHT RIGHT OUTER OUTER OUTER OUTER INNER INNER JOIN <relation> VALUES ( <value> , ) , XTQL <XTQL query> ( <XTQL query> , ? ) ( ( <query> <query> ) ) ( ( <query> <query> ) ) LATERAL ( ( <query> <query> ) ) ( ( <query> <query> ) ) UNNEST ( <value> ) WITH ORDINALITY <table alias> FOR VALID_TIME VALID_TIME SYSTEM_TIME SYSTEM_TIME VALID_TIME VALID_TIME SYSTEM_TIME SYSTEM_TIME AS OF <timestamp> FROM <timestamp> TO <timestamp> BETWEEN <timestamp> AND <timestamp> ALL ALL VALID_TIME VALID_TIME SYSTEM_TIME SYSTEM_TIME VALID_TIME VALID_TIME SYSTEM_TIME SYSTEM_TIME

The valid-time/system-time filters for a given table take precedence as follows:

  1. Any explicit specifications in the FROM clause.
  2. Any options passed to the SETTING clause of the given query (see ‘Basis’).
  3. Any options passed to the BEGIN clause of the current transaction (see ‘Basis’).
  4. Then as follows:
    • System time defaults to ‘as best known’ - the latest processed transaction on the queried node.
    • Valid time defaults to ‘as of now’ - the clock time taken either from CLOCK_TIME (if overridden) or the actual clock time on the queried node.
<literal> <column reference> " <column reference> " ` <column reference> ` <param> + - <value> <value> + - * / <value> <function name> ( <value> , ) ROW_NUMBER ( ) OVER ( <window> ) <predicate> CAST ( <value> AS <data type> ) CASE <value> WHEN <value> THEN <value> , WHEN <predicate> THEN <value> , ELSE <value> END COALESCE ( <value> , ) NULLIF ( <value> , <value> ) ARRAY [ <value> , ] ARRAY ( <query> ) <record> NEST_ONE NEST_MANY ( <query> ) ( <value> ) ? $<param idx> { <field name> <field name> : : <value> <value> <field name> <field name> : : <value> <value> , , <field name> <field name> : : <value> <value> <field name> <field name> : : <value> <value> , , <field name> <field name> : : <value> <value> <field name> <field name> : : <value> <value> , , <field name> <field name> : : <value> <value> <field name> <field name> : : <value> <value> , , } RECORD OBJECT ( <field name> <field name> : : <value> <value> <field name> <field name> : : <value> <value> , , <field name> <field name> : : <value> <value> <field name> <field name> : : <value> <value> , , <field name> <field name> : : <value> <value> <field name> <field name> : : <value> <value> , , <field name> <field name> : : <value> <value> <field name> <field name> : : <value> <value> , , ) NULL <numeric literal> ' <SQL-style string> ' $$ <string> $$ E' <C-style string> ' DATE ' <ISO8601 date literal> ' TIME ' <ISO8601 time literal> ' TIMESTAMP ' <ISO8601 timestamp literal> ' WITH WITHOUT TIME ZONE ' <SQL timestamp literal> ' DURATION ' <ISO8601 duration literal> ' TRUE FALSE NOT <predicate> <predicate> AND OR <predicate> <value> = <> != < <= >= > <value> ANY ALL ( <query> ) <predicate name> ( <value> , ) <value> IS NOT TRUE FALSE NULL NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT EXISTS ( <query> ) <value> NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT IN ( <value> , <query> ) <value> NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT LIKE <value> ESCAPE ' <escape character> ' <value> NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT LIKE_REGEX <JVM regex> FLAG ' <JVM regex flags> ' <value> ~ ~* !~ !~* <JVM regex> <value> NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT NOT BETWEEN ASYMMETRIC SYMMETRIC <value> AND <value> PARTITION BY <value> , ORDER BY <value> ASC DESC NULLS FIRST LAST ,

Nested sub-queries allow you to easily create tree-shaped results, using NEST_MANY and NEST_ONE:

  • For example, if you have a one-to-many relationship (e.g. customers → orders), you can write a query that, for each customer, returns an array of their orders as nested objects:

    SELECT c._id AS customer_id, c.name,
    NEST_MANY(SELECT o._id AS order_id, o.value
    FROM orders o
    WHERE o.customer_id = c._id
    ORDER BY o._id)
    AS orders
    FROM customers c

    [
    {
    "customerId": 0,
    "name": "bob",
    "orders": [ { "orderId": 0, "value": 26.20 }, { "orderId": 1, "value": 8.99 } ]
    },
    {
    "customerId": 1,
    "name": "alice",
    "orders": [ { "orderId": 2, "value": 12.34 } ]
    }
    ]
  • In the other direction (many-to-one) - for each order, additionally return details about the customer - use NEST_ONE to get a single nested object:

    SELECT o._id AS order_id, o.value,
    NEST_ONE(SELECT c.name FROM customers c
    WHERE c._id = o.customer_id)
    AS customer
    FROM orders o
    ORDER BY o._id

    [
    {
    "orderId": 0,
    "value": 26.20,
    "customer": { "name": "bob" }
    },
    {
    "order-id": 1,
    "value": 8.99,
    "customer": { "name": "bob" }
    },
    {
    "order-id": 2,
    "value": 12.34,
    "customer": { "name": "alice" }
    }
    ]

Queries in XTDB run against a ‘basis’, which consists of:

  1. a ‘snapshot’ - an upper bound on the transactions that are visible to the query.
  2. a ‘clock time’ - used for any function calls that reference the current time (e.g. CURRENT_TIMESTAMP)

These can be set either on a per-query basis, using SETTING, or at the start of a transaction, using BEGIN:

SETTING DEFAULT VALID_TIME SYSTEM_TIME TO AS OF <timestamp> FROM <timestamp> TO <timestamp> BETWEEN <timestamp> AND <timestamp> ALL SNAPSHOT_TOKEN CLOCK_TIME TO = <timestamp> , <query>
  • Setting the default valid-time/system-time applies to any FROM clause that doesn’t have any valid-time/system-time specification explicitly set.
  • Setting the SNAPSHOT_TOKEN enforces an upper-bound on the transactions visible to the query - i.e. no matter what the per-table system-time clauses specify, they will not see anything newer than this snapshot-token. If not provided, this defaults to the latest-completed transaction on the queried node.
  • Setting the CLOCK_TIME defines a fixed value for any functions that depend on the current time - e.g. CURRENT_TIMESTAMP. It also defines the default valid-time selection for any tables in FROM clauses that don’t otherwise have a valid-time specification. If not provided, it defaults to the clock-time fixed at the start of the transaction.
BEGIN READ ONLY WITH ( SNAPSHOT_TOKEN = = = = = = = = = = = = = = = = <snapshot token> CLOCK_TIME = = = = = = = = = = = = = = = = <timestamp> AWAIT_TOKEN = = = = = = = = = = = = = = = = <await token> TIMEZONE TIME ZONE = = = = = = = = = = = = = = = = <timezone> , ) READ WRITE ... COMMIT ROLLBACK
  • A transaction may be either READ ONLY or READ WRITE. If not specified, it will be inferred from the first statement in the transaction.

    Transactions must not mix query statements and DML statements.

  • Additionally, for read-only transactions:

    • SNAPSHOT_TOKEN and CLOCK_TIME behave the same as in SETTING.
    • AWAIT_TOKEN may be provided to wait for a specific transaction to be visible on the queried node before starting the transaction. If not provided, it defaults to waiting for the latest-submitted transaction on the current connection.
    • TIMEZONE sets the time zone for the duration of the transaction, affecting any time zone-aware date/time literals and functions. If not provided, it defaults to the time-zone of the connection.
  • For read-write transactions, see the transaction reference.

  • Committing/rolling back a read-only transaction has no effect in XTDB, because readers never block writers nor each other.