Querying

Selecting all columns#

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

sql1SELECT *
2FROM products;

Selecting specific columns#

sql1SELECT id, customer_name, ip
2FROM customers;

Selecting expressions#

In addition to selecting specific column values, you can also select expressions:

sql1SELECT NOT active as disabled, SUBSTRING(customer_name, 1, 3) AS short_name, age >= 21 AS is_adult
2FROM customers;

Filtering entries#

sql1SELECT id, customer_name, email
2FROM customers
3WHERE country = 'SE' AND city = 'Arvika';

CASE Statements#

immudb supports SQL-style CASE expressions similar to PostgreSQL. This allows you to perform conditional logic within queries, making it easier to transform and categorize data dynamically.

Example: Categorizing Age Groups#

The following example demonstrates how to use a CASE statement to categorize customers into different age groups:

sql1SELECT id, customer_name, age,
     CASE
         WHEN age < 18 THEN 'Minor'
         WHEN age >= 18 AND age <= 64 THEN 'Adult'
         ELSE 'Senior'
     END AS age_group
7FROM customers;

Ordering by column value#

sql1SELECT id, customer_name
2FROM customers
3ORDER BY customer_name ASC, id DESC;

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

Although not required, adding an index on the columns specified in the clause can drastically reduce query times.

INNER JOIN#

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

sql 1SELECT *
 2FROM orders
 3INNER JOIN customers ON orders.customerid = customers.id;
 4
 5SELECT *
 6FROM orders
 7JOIN customers ON orders.customerid = customers.id
 8WHERE orders.productid = 2;
 9
10SELECT * FROM orders
11JOIN customers ON customers.id = orders.customerid
12JOIN 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 .

A NOT prefix negates the value of the LIKE operator.

sql 1SELECT product
 2FROM products
 3WHERE product LIKE '(J.*ce|Red)';
 4
 5SELECT product
 6FROM products
 7WHERE product NOT LIKE '(J.*ce|Red)';
 8
 9SELECT id, product
10FROM products
11WHERE (id > 0 AND NOT products.id >= 10)
12  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.

sql 1SELECT product
 2FROM products
 3WHERE product IN ('Milk', 'Grapes - Red');
 4
 5SELECT product
 6FROM products
 7WHERE product NOT IN ('Milk', 'Grapes - Red');
 8
 9SELECT id, product
10FROM products
11WHERE (id > 0 AND NOT products.id >= 10)
12  AND (product IN ('Milk', 'Grapes - Red'));

Column and table aliasing#

sql1SELECT c.id, c.customer_name AS name, active
2FROM customers AS c
3WHERE c.id <= 3 AND c.active = true;
4
5SELECT c.id, c.customer_name AS name, active
6FROM customers c
7WHERE 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

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

Grouping results with GROUP BY#

Results can be grouped by the value of one or more columns.

sql1SELECT COUNT(*) as customer_count, country
2FROM customers
3GROUP BY country
4ORDER BY country;

Filtering grouped results with HAVING#

sql1SELECT
2    active,
3    COUNT(*) as c,
4    MIN(age),
5    MAX(age)
6FROM customers
7GROUP BY active
8HAVING COUNT(*) > 0
9ORDER BY active DESC;

Sub-queries#

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

sql 1SELECT * FROM (
 2    SELECT id, customer_name
 3    FROM customers
 4    WHERE age < 30
 5)
 6INNER JOIN customer_review
 7    ON customer_review.customerid = customers.id;
 8
 9SELECT * FROM (
10    SELECT id, customer_name
11    FROM customers
12    WHERE age < 30
13) AS c
14INNER JOIN (
15    SELECT * FROM customer_review
16) AS r
17    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.

sql1SELECT customer_name as name
2FROM customers
3WHERE age < 30
4UNION
5SELECT seller_name
6FROM sellers
7WHERE 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.

sql1SELECT AVG(age) FROM customers
2UNION ALL
3SELECT 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 validations have not yet been implemented, therefore there may be false positives generated. In case of conflict, a new attempt may be required.

sql1BEGIN TRANSACTION;
2    UPSERT INTO products (id, price, product)
3    VALUES (4, '$5.76', 'Bread');
4
5    INSERT INTO orders(productid, customerid)
6    VALUES(4, 1);
7COMMIT;

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).

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

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

Temporal ranges can be specified using functions and parameters

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

Row History#

Historical queries over physical tables (row revisions) is also supported. Result sets over history of <table> will include the additional _rev column, denoting the row revision number,

Historical queries can use the additional _rev column as usual:

sql1SELECT _rev, price
2FROM (HISTORY OF products)
3WHERE id = 2;

sql1SELECT * FROM (HISTORY OF mytable) WHERE _rev = @rev

Transaction metadata#

immudb can be configured to inject request information (user, ip address, etc…) to transaction metadata (see Request Metadata ). When this functionality is enabled, such information can be retrieved by querying the special _tx_metadata column, whose type is JSON (refer to Querying JSON columns section).

sql1SELECT _tx_metadata->'ip' as ip_addr FROM customers
2WHERE _tx_metadata->'usr' = 'username';

Skipping entries with OFFSET clause#

Using OFFSET clause in SQL queries can be used to skip an initial list of entries from the result set. Internally offsets are implemented by skipping entries from the result on the server side thus it may come with performance penalty when the value of such offset is large.

sql1SELECT *
2FROM products;
3LIMIT 10 OFFSET 30

sql1SELECT id, customer_name, email
2FROM customers
3WHERE country = 'SE' AND city = 'Arvika';
4ORDER BY customer_name
5LIMIT 50 OFFSET 100

Querying JSON columns#

immudb supports a JSON data type designed to store data in the JavaScript Object Notation format. It allows for flexible and efficient storage and querying of JSON objects, which can be particularly useful in applications that require a combination of structured and semi-structured data.

sql1CREATE TABLE items (
2  id INTEGER AUTO_INCREMENT,
3  details JSON,
4
5  PRIMARY KEY id
6);

Internally, JSON data are represented in their textual format. Columns can be then populated by any valid JSON string:

sql1INSERT INTO items(id, details)
VALUES
  (1, '{"name": "Alice", "age": 25}'),
  (2, '{"name": "Bob", "address": {"city": "Los Angeles", "state": "CA"}}'),
  (3, '["apple", "banana", "cherry"]'),
  (4, '[{"product": "book", "price": 12.99}, {"product": "pen", "price": 1.49}]'),
  (5, '"N/A"')

The ARROW (->) operator, used to access nested fields, is crucial when working with the JSON type. The following shows valid examples of queries over a JSON field:

sql1SELECT * FROM items
2WHERE details->'name' IS NOT NULL
3ORDER BY details->'name';
4
5SELECT * FROM items
6WHERE json_typeof(details->'1') != 'OBJECT' AND details->'1' = 'banana';
7
8SELECT details->'1'->'price' as price FROM items;

LIKE and ILIKE pattern matching#

Use standard SQL wildcards: % matches any sequence of characters, _ matches a single character.

sql 1-- Find names starting with 'A'
 2SELECT * FROM customers WHERE name LIKE 'A%';
 3
 4-- Find 5-letter names starting with 'J'
 5SELECT * FROM customers WHERE name LIKE 'J____';
 6
 7-- Case-insensitive search
 8SELECT * FROM products WHERE name ILIKE '%widget%';
 9
10-- Escape literal % or _ with backslash
11SELECT * FROM data WHERE code LIKE '100\%';

EXISTS and IN subqueries#

sql1-- Find customers who have placed orders
2SELECT * FROM customers c
3WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
4
5-- Find products not in any order
6SELECT * FROM products
7WHERE id NOT IN (SELECT product_id FROM order_items);

EXCEPT and INTERSECT#

sql 1-- Products in catalog but never ordered
 2SELECT id, name FROM products
 3EXCEPT
 4SELECT p.id, p.name FROM products p
 5INNER JOIN orders o ON p.id = o.product_id;
 6
 7-- Customers who are also employees
 8SELECT name FROM customers
 9INTERSECT
10SELECT name FROM employees;

COUNT(DISTINCT) and STRING_AGG#

sql1-- Count unique categories
2SELECT COUNT(DISTINCT category) FROM products;
3
4-- Concatenate names per department
5SELECT department, STRING_AGG(name, ', ') AS members
6FROM employees
7GROUP BY department;

ORDER BY alias#

You can reference SELECT aliases in ORDER BY:

sql1SELECT department, COUNT(*) AS total
2FROM employees
3GROUP BY department
4ORDER BY total DESC;

RETURNING clause#

Get back values from INSERT, UPDATE, or DELETE:

sql1INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com')
2RETURNING id, name;
3
4UPDATE products SET price = price * 1.1 WHERE category = 'Electronics'
5RETURNING id, name, price;
6
7DELETE FROM sessions WHERE expired = true
8RETURNING session_id;

ON CONFLICT (Upsert)#

sql1INSERT INTO settings (key, value) VALUES ('theme', 'dark')
2ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value;

Edit this page on GitHub Last updated 2 September 2026