Querying

Selecting all columns

All columns from all joined tables can be queried with SELECT * statement.

SELECT *
FROM products;

Selecting specific columns

SELECT id, customer_name, ip
FROM customers;

Filtering entries

SELECT id, customer_name, email
FROM customers
WHERE country = 'SE' AND city = 'Arvika';

Ordering by column value

SELECT id, customer_name
FROM customers
ORDER BY customer_name ASC;

Currently only one column can be used in the ORDER BY clause.

The order may be either ascending (ASC suffix, default) or descending (DESC suffix).

Ordering rows by a value of a column requires a matching index on that column.

INNER JOIN

immudb supports standard SQL INNER JOIN syntax. The INNER join type is optional.

SELECT *
FROM orders
INNER JOIN customers ON orders.customerid = customers.id;

SELECT *
FROM orders
JOIN customers ON orders.customerid = customers.id
WHERE orders.productid = 2;

SELECT * FROM orders
JOIN customers ON customers.id = orders.customerid
JOIN products ON products.id = orders.productid;

LIKE operator

immudb supports the LIKE operator. Unlike in other SQL engines though, the pattern use a regexp syntax supported by the regexp library in the go language (opens new window).

A NOT prefix negates the value of the LIKE operator.

SELECT product
FROM products
WHERE product LIKE '(J.*ce|Red)';

SELECT product
FROM products
WHERE product NOT LIKE '(J.*ce|Red)';

SELECT id, product
FROM products
WHERE (id > 0 AND NOT products.id >= 10)
  AND (products.product LIKE 'J');

IN operator

immudb has a basic supports for the IN operator.

A NOT prefix negates the value of the IN operator.

Note: Currently the list for the IN operator can not be calculated using a sub-query.

SELECT product
FROM products
WHERE product IN ('Milk', 'Grapes - Red');

SELECT product
FROM products
WHERE product NOT IN ('Milk', 'Grapes - Red');

SELECT id, product
FROM products
WHERE (id > 0 AND NOT products.id >= 10)
  AND (product IN ('Milk', 'Grapes - Red'));

Column and table aliasing

SELECT c.id, c.customer_name AS name, active
FROM customers AS c
WHERE c.id <= 3 AND c.active = true;

SELECT c.id, c.customer_name AS name, active
FROM customers c
WHERE c.id <= 3 AND c.active = true;

Table name aliasing is necessary when using more than one join with the same table.

Aggregations

Available aggregation functions:

• COUNT
• SUM
• MAX
• MIN
• AVG

SELECT
    COUNT(*) AS c,
    SUM(age),
    MIN(age),
    MAX(age),
    AVG(age)
FROM customers;

Grouping results with GROUP BY

Results can be grouped by a value of a single column. That column must also be used in a matching ORDER BY clause.

SELECT COUNT(*) as customer_count, country
FROM customers
GROUP BY country
ORDER BY country;

Filtering grouped results with HAVING

SELECT
    active,
    COUNT(*) as c,
    MIN(age),
    MAX(age)
FROM customers
GROUP BY active
HAVING COUNT(*) > 0
ORDER BY active DESC;

Sub-queries

The table in the SELECT or JOIN clauses can be replaced with a sub-query.

SELECT * FROM (
    SELECT id, customer_name
    FROM customers
    WHERE age < 30
)
INNER JOIN customer_review
    ON customer_review.customerid = customers.id;

SELECT * FROM (
    SELECT id, customer_name
    FROM customers
    WHERE age < 30
) AS c
INNER JOIN (
    SELECT * FROM customer_review
) AS r
    ON r.customerid = c.id;

Note: the context of a sub-query does not propagate outside, e.g. it is not possible to reference a table from a sub-query in the WHERE clause outside of the sub-query.

Combining query results with UNION

It is possible to combine multiple query results with the UNION operator.

Subqueries must select the same number and type of columns. The final return will assign the same naming as in the first subquery, even if names differ.

SELECT customer_name as name
FROM customers
WHERE age < 30
UNION
SELECT seller_name
FROM sellers
WHERE age < 30

Subqueries are not constrained in any way, they can contain aggregations or joins.

Duplicate rows are excluded by default. Using UNION ALL will leave duplicate rows in place.

SELECT AVG(age) FROM customers
UNION ALL 
SELECT AVG(age) FROM sellers

Transactions

The ACID (Atomicity, Consistency, Isolation, and Durability) compliance is complete.

Handling read-write conflicts may be necessary when dealing with concurrent transactions. Getting the error ErrTxReadConflict ("tx read conflict") means there was another transaction committed before the current one, and the data it read may have been invalidated. MVCC (opens new window) validations have not yet been implemented, therefore there may be false positives generated. In case of conflict, a new attempt may be required.

BEGIN TRANSACTION;
    UPSERT INTO products (id, price, product)
    VALUES (4, '$5.76', 'Bread');

    INSERT INTO orders(productid, customerid)
    VALUES(4, 1);
COMMIT;

Time travel

Time travel allows you to read data from SQL as if it were in a previous state or from a specific time range. Initial and final points are optional and can be specified using either a transaction ID or a timestamp.

The temporal range can be used to filter out rows from the specified (physical) table, but it is not supported in subqueries.

The initial point can be inclusive (SINCE) or exclusive (AFTER). The final point can be inclusive (UNTIL) or exclusive (BEFORE).

SELECT id, product, price
FROM products BEFORE TX 13
WHERE id = 2;

SELECT * FROM sales SINCE '2022-01-06 11:38' UNTIL '2022-01-06 12:00'

Temporal ranges can be specified using functions and parameters

SELECT * FROM mytable SINCE TX @initialTx BEFORE now()