# 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, 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.
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 the value of one or more columns.
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()
# 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:
SELECT _rev, price
FROM (HISTORY OF products)
WHERE id = 2;
SELECT * 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).
SELECT _tx_metadata->'ip' as ip_addr FROM customers
WHERE _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.
SELECT *
FROM products;
LIMIT 10 OFFSET 30
SELECT id, customer_name, email
FROM customers
WHERE country = 'SE' AND city = 'Arvika';
ORDER BY customer_name
LIMIT 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.
CREATE TABLE items (
id INTEGER AUTO_INCREMENT,
details JSON,
PRIMARY KEY id
);
Internally, JSON data are represented in their textual format. Columns can be then populated by any valid JSON string:
INSERT 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:
SELECT * FROM items
WHERE details->'name' IS NOT NULL
ORDER BY details->'name';
SELECT * FROM items
WHERE json_typeof(details->'1') != 'OBJECT' AND details->'1' = 'banana';
SELECT details->'1'->'price' as price FROM items;