See Appendix C, SQL Functions for a full list of the SQL functions that VoltDB supports.

VoltDB supports the following operators in expressions: addition (+), subtraction (-), multiplication (*), division (*) and string concatenation (||).

TOP n is a synonym for LIMIT n.

The WHERE expression supports the boolean operators: equals (=), not equals (!= or <>), greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=), LIKE, STARTS WITH, IS NULL, IS DISTINCT, IS NOT DISTINCT, AND, OR, and NOT. Note, however, although OR is supported syntactically, VoltDB does not optimize these operations and use of OR may impact the performance of your queries.

The boolean expression LIKE provides text pattern matching in a VARCHAR column. The syntax of the LIKE expression is {string-expression} LIKE '{pattern}' where the pattern can contain text and wildcards, including the underscore (_) for matching a single character and the percent sign (%) for matching zero or more characters. The string comparison is case sensitive.

Where an index exists on the column being scanned and the pattern starts with a text prefix (rather than starting with a wildcard), VoltDB will attempt to use the index to maximize performance, For example, a query limiting the results to rows from the EMPLOYEE table where the primary index¸ the JOB_CODE column, begins with the characters "Temp" looks like this:

SELECT * from EMPLOYEE where JOB_CODE like 'Temp%';

The STARTS WITH clause is useful in stored procedures because it uses indexed scans where the LIKE clause cannot. The expression STARTS WITH '{string-expression}' is syntactically identical to LIKE '{string-expression}%' in that it matches any string value that starts with string-expression. The difference is that in a stored procedure, use of the STARTS WITH clause with a placeholder (that is, "START WITH ?") utilizes available indexes, whereas LIKE ? requires a sequential scan, since the compiler cannot tell if the replacement text ends in a percent sign or not and must plan for any possible string value. For example, if KEYWORD is the primary key for the ENTRY table, then VoltDB can use the primary key index to optimize the following stored procedure:

CREATE PROCEDURE SimpleSearch AS
   SELECT keyword FROM entry WHERE keyword STARTS WITH ?;

The boolean expression IN determines if a given value is found within a list of alternatives. For example, in the following code fragment the IN expression looks to see if a record is part of Hispaniola by evaluating whether the column COUNTRY is equal to either "Dominican Republic" or "Haiti":

WHERE Country IN ('Dominican Republic', 'Haiti')

Note that the list of alternatives must be enclosed in parentheses. The result of an IN expression is equivalent to a sequence of equality conditions separated by OR. So the preceding code fragment produces the same boolean result as:

WHERE Country='Dominican Republic' OR Country='Haiti'

The advantages are that the IN syntax provides more compact and readable code and can provide improved performance by using an index on the initial expression where available.

The boolean expression BETWEEN determines if a value falls within a given range. The evaluation is inclusive of the end points. In this way BETWEEN is a convenient alias for two boolean expressions determining if a value is greater than or equal to (>=) the starting value and less than or equal to (<=) the end value. For example, the following two WHERE clauses are equivalent:

WHERE salary BETWEEN ? AND ?
WHERE salary >= ? AND salary <= ?

The boolean expressions IS DISTINCT FROM and IS NOT DISTINCT FROM are similar to the equals ("=") and not equals ("<>") operators respectively, except when evaluating null operands. If either or both operands are null, the equals and not equals operators return a boolean null value, or false. IS DISTINCT FROM and IS NOT DISTINCT FROM consider null a valid operand. So if only one operand is null IS DISTINCT FROM returns true and IS NOT DISTINCT FROM returns false. If both operands are null IS DISTINCT FROM returns false and IS NOT DISTINCT FROM returns true.

When using placeholders in SQL statements involving the IN list expression, you can either do replacement of individual values within the list or replace the list as a whole. For example, consider the following statements:

SELECT * from EMPLOYEE where STATUS IN (?, ?,?);
SELECT * from EMPLOYEE where STATUS IN ?;

In the first statement, there are three parameters that replace individual values in the IN list, allowing you to specify exactly three selection values. In the second statement the placeholder replaces the entire list, including the parentheses. In this case the parameter to the procedure call must be an array and allows you to change not only the values of the alternatives but the number of criteria considered.

The following Java code fragment demonstrates how these two queries can be used in a stored procedure, resulting in equivalent SQL statements being executed:

String arg1 = "Salary";
String arg2 = "Hourly";
String arg3 = "Parttime";
voltQueueSQL( query1, arg1, arg2, arg3);

String listargs[] = new String[3];
listargs[0] = arg1;
listargs[1] = arg2;
listargs[2] = arg3;
voltQueueSQL( query2, (Object) listargs);

Note that when passing arrays as parameters in Java, it is a good practice to explicitly cast them as an object to avoid the array being implicitly expanded into individual call parameters.

VoltDB supports the use of CASE-WHEN-THEN-ELSE-END for conditional operations. For example, the following SELECT expression uses a CASE statement to return different values based on the contents of the price column:

SELECT Prod_name, 
    CASE WHEN price > 100.00 
          THEN 'Expensive'
          ELSE 'Cheap'
    END 
FROM products ORDER BY Prod_name;                      

For more complex conditional operations with multiple alternatives, use of the DECODE() function is recommended.

VoltDB supports both inner and outer joins.

The SELECT statement supports subqueries as a table reference in the FROM clause. Subqueries must be enclosed in parentheses and must be assigned a table alias.

You can only join two or more partitioned tables if those tables are partitioned on the same value and joined on equality of the partitioning column. Joining two partitioned tables on non-partitioned columns or on a range of values is not supported. However, there are no limitations on joining to replicated tables.

Extremely large result sets (greater than 50 megabytes in size) are not supported. If you execute a SELECT statement that generates a result set of more than 50 megabytes, VoltDB will return an error.

The PARTITION BY^[1] clause defines how the selection results are grouped.

The ORDER BY clause defines the order in which the rows are evaluated within each group.

There can be only one window function per SELECT statement.

You cannot use a window function and GROUP BY in the same SELECT statement.

The argument(s) to the ORDER BY clause can be either integer or TIMESTAMP expressions only.

A single row with a single column — this is sometimes known as a scalar subquery and represents a single value

A single row with multiple columns — this is also known as a row value expression

Multiple rows with one or more columns

In the FROM clause, the SELECT statement supports all types of subquery as a table reference. The subquery must be enclosed in parentheses and must be assigned a table alias.

In the selection expression, scalar subqueries can be used in place of a single column reference.

In the WHERE clause and CASE operations, both scalar and non-scalar subqueries can be used as part of boolean expressions. Scalar subqueries can be used in place of any single-valued expression. Non-scalar subqueries can be used in the following situations:

You cannot perform set operations on SELECT statements that reference the same table.

All tables in the SELECT statements must either be replicated tables or partitioned tables partitioned on the same column value, using equality of the partitioning column in the WHERE clause.

Defines the name of the common table expression

Optionally, renames the resulting columns

Declares the expression itself using standard SELECT statement syntax

Basic common expressions, with a name, optional column names, and the expression itself

Recursive expressions, using the WITH RECURSIVE keywords and merging two expressions with a UNION ALL set operation

The table name and, optionally, alias names for the columns

A base query that defines the starting condition

A UNION ALL set operator

A second, recursive query that iterates over the common table expression results

There can be only one common table expression per query.

In multi-partition transactions, the common expression can reference replicated tables only.

In single-partition transactions, the common expression can reference both replicated and partitioned table, with the caveat that as in any partitioned transaction partitioned tables have access to only that data in the current partition.

For basic (non-recursive) common table expressions, the common expression cannot be self-referencing. That is, the SELECT statement within the WITH clause can reference actual database table and view names only, it cannot reference the common expression name itself.