# 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 (
Ordering rows by a value of a column requires a matching index on that column.
# INNER JOIN
immudb supports standard SQL
INNER JOIN syntax.
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
Unlike in other SQL engines though, the pattern use a regexp syntax
supported by the regexp library in the go language (opens new window).
NOT prefix negates the value of the
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
NOT prefix negates the value of the
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.
Available aggregation functions:
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;
The table in the
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
WHERE clause outside of the sub-query.
# Combining query results with UNION
It is possible to combine multiple query results with the
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
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 (
The final point can be inclusive (
UNTIL) or exclusive (
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()