![[Previous]](../prev.gif) |
![[Contents]](../contents.gif) |
![[Index]](../keyword_index.gif) |
![[Next]](../next.gif) |
|
|
|
|
SQL expressions
expr binary-op expr |
expr [NOT] { LIKE | GLOB } expr [ESCAPE expr] |
unary-op expr |
( expr ) |
[[database-name .] [table-name .] column-name |
literal-value |
parameter |
function-name ( expr-list | * ) |
expr ISNULL |
expr NOTNULL |
expr [NOT] BETWEEN expr AND expr |
expr [NOT] IN ( value-list ) |
expr [NOT] IN ( select-statement ) |
expr [NOT] IN [database-name .] table-name |
[EXISTS] ( select-statement ) |
CASE [expr] ( WHEN expr THEN expr )+ [ELSE expr] END |
CAST ( expr AS type )
expr COLLATE collation-name
SQL expressions are subcomponents of most other commands. QDB understands the following binary operators, in order from highest to lowest precedence:
|| * / % + - << >> & | < <= > >= = == != <> IN AND OR
The supported unary prefix operators are:
- + ! ~ NOT
The COLLATE operator can be thought of as a unary postfix operator. The COLLATE operator has the highest precedence. It always binds more tightly than any prefix unary operator or any binary operator.
The unary operator [Operator +] is a no-op. It can be applied to strings, numbers, or blobs and it always gives as its result the value of the operand.
Note that there are two variations of the equals and not equals operators. Equals can be either = or ==. The non-equals operator can be either != or <>. The || operator is “concatenate” — it joins together the two strings of its operands. The operator % outputs the remainder of its left operand modulo its right operand.
The result of any binary operator is a numeric value, except for the || concatenation operator, which gives a string result.
A literal value is an integer number or a floating point number. Scientific notation is supported. The “.” character is always used as the decimal point even if the locale setting specifies “,” for this role — the use of “,” for the decimal point would result in syntactic ambiguity. A string constant is formed by enclosing the string in single quotation marks ('). A single quotation mark within the string can be encoded by putting two single quotes in a row, as in Pascal. C-style escapes using the backslash character are not supported because they are not standard SQL. BLOB literals are string literals containing hexadecimal data and preceded by a single “x” or “X” character. For example:
X'53514697465'
A literal value can also be the token NULL.
A parameter specifies a placeholder in the expression for a literal value that is filled in at runtime using qdb_stmt_exec(). Parameters can take several forms:
Parameters that are not assigned values using qdb_stmt_exec() are treated as NULL.
The LIKE operator does a pattern-matching comparison. The operand to the right contains the pattern; the left-hand operand contains the string to match against the pattern.
A percent symbol(%) in the pattern matches any sequence of zero or more characters in the string. An underscore (_) in the pattern matches any single character in the string. Any other character matches itself or its lower/upper case equivalent (i.e. case-insensitive matching). (A bug: QDB understands only upper/lower case for 7-bit Latin characters. Hence the LIKE operator is case sensitive for 8-bit iso8859 characters or UTF-8 characters. For example, the expression 'a' LIKE 'A' is TRUE but 'æ' LIKE 'Æ' is FALSE.).
If the optional ESCAPE clause is present, then the expression following the ESCAPE keyword must evaluate to a string consisting of a single character. This character may be used in the LIKE pattern to include literal percent or underscore characters. The escape character followed by a percent symbol, underscore or itself matches a literal percent symbol, underscore or escape character in the string, respectively. The infix LIKE operator is implemented by calling the user function like(X,Y).
The GLOB operator is similar to LIKE, but uses the UNIX file-globbing syntax for its wildcards. Also, GLOB is case sensitive, unlike LIKE. Both GLOB and LIKE may be preceded by the NOT keyword to invert the sense of the test. The infix GLOB operator is implemented by calling the user function glob(X,Y) and can be modified by overriding that function.
A column name can be any of the names defined in the CREATE TABLE statement or one of the following special identifiers: ROWID, OID, or _ROWID_. These special identifiers all describe the unique random integer key (the row key) associated with every row of every table. The special identifiers only refer to the row key if the CREATE TABLE statement does not define a real column with the same name. Row keys act like read-only columns. A row key can be used anywhere a regular column can be used, except that you cannot change the value of a row key in an UPDATE or INSERT statement. SELECT * ... does not return the row key.
SELECT statements can appear in expressions as either the right-hand operand of the IN operator, as a scalar quantity, or as the operand of an EXISTS operator. As a scalar quantity or the operand of an IN operator, the SELECT should have only a single column in its result. Compound SELECTs (connected with keywords like UNION or EXCEPT) are allowed. With the EXISTS operator, the columns in the result set of the SELECT are ignored and the expression returns TRUE if one or more rows exist and FALSE if the result set is empty. If no terms in the SELECT expression refer to value in the containing query, then the expression is evaluated once prior to any other processing and the result is reused as necessary. If the SELECT expression does contain variables from the outer query, then the SELECT is reevaluated every time it is needed.
When a SELECT is the right operand of the IN operator, the IN operator returns TRUE if the result of the left operand is any of the values generated by the select. The IN operator may be preceded by the NOT keyword to invert the sense of the test.
When a SELECT appears within an expression but is not the right operand of an IN operator, then the first row of the result of the SELECT becomes the value used in the expression. If the SELECT yields more than one result row, all rows after the first are ignored. If the SELECT yields no rows, then the value of the SELECT is NULL.
A CAST expression changes the datatype of the expr into the type specified by type, where type can be any nonempty type name that is valid for the type in a column definition of a CREATE TABLE statement.
Both simple and aggregate functions are supported. A simple function can be used in any expression. Simple functions return a result immediately based on their inputs. Aggregate functions may only be used in a SELECT statement. Aggregate functions compute their result across all rows of the result set.
The functions shown below are available by default.
QDB's type handling is explained in the chapter Datatypes in QDB.
In any aggregate function that takes a single argument, that argument can be preceded by the keyword DISTINCT. In such cases, duplicate elements are filtered before being passed into the aggregate function. For example, the function count(distinct X) will return the number of distinct values of column X instead of the total number of non-NULL values in column X.
The result of total() is always a floating point value. The result of sum() is an integer value if all non-NULL inputs are integers. If any input to sum() is neither an integer or a NULL, then sum() returns a floating point value which might be an approximation to the true sum.
The sum() function throws an “integer overflow” exception if all inputs are integers or NULL and an integer overflow occurs at any point during the computation. The total() function never throws an exception.
|
|
|
|