SQL Grammar
Index
Literals
Datetime fields
Other Grammar
Details
Click on the header to switch between railroad diagram and BNF.
Literals
Value
A literal value of any data type, or null.
Example:
10
Array
ARRAY [ |
| ] |
An array of values.
Example:
ARRAY[1, 2]
ARRAY[1]
ARRAY[]
Boolean
TRUE | ||
FALSE | ||
UNKNOWN |
A boolean value. UNKNOWN
is a NULL
value with the boolean data type.
Example:
TRUE
Bytes
A binary value. The hex value is not case sensitive and may contain space characters.
Example:
X''
X'01FF'
X'01 bc 2a'
X'01' '02'
Date
DATE ' |
| 2000-01-01 ' |
A date literal.
Example:
DATE '2004-12-31'
Date and time
A literal value of any date-time data type.
Example:
TIMESTAMP '1999-01-31 10:00:00'
Decimal
A decimal number with fixed precision and scale. Internally, java.lang.BigDecimal
is used. To ensure the floating point representation is used, use CAST
(X AS DOUBLE
). There are some special decimal values: to represent positive infinity, use POWER(0, -1)
; for negative infinity, use (-POWER(0, -1))
; for -0.0, use (-CAST(0 AS DOUBLE))
; for NaN
(not a number), use SQRT(-1)
.
Example:
SELECT -1600.05
SELECT CAST(0 AS DOUBLE)
SELECT -1.4e-10
Dollar Quoted String
$ $ anything $ $ |
A string starts and ends with two dollar signs. Two dollar signs are not allowed within the text. A whitespace is required before the first set of dollar signs. No escaping is required within the text.
Example:
$$John's car$$
Hex Number
| 0x |
|
|
A number written in hexadecimal notation.
Example:
0xff
Int
| number |
The maximum integer number is 2147483647, the minimum is -2147483648.
Example:
10
JSON
A binary or character string with a RFC
8259-compliant JSON
text and data format. JSON
text is parsed into internal representation. Order of object members is preserved as is. Duplicate object member names are allowed.
Example:
JSON '{"id":10,"name":"What''s this?"}'
JSON '[1, ' '2]';
JSON X'7472' '7565'
Long
| number |
Long numbers are between -9223372036854775808 and 9223372036854775807.
Example:
100000
Null
NULL
NULL
is a value without data type and means 'unknown value'.
Example:
NULL
Number
digit |
|
The maximum length of the number depends on the data type used.
Example:
100
Numeric
The data type of a numeric value is always the lowest possible for the given value. If the number contains a dot this is decimal; otherwise it is int, long, or decimal (depending on the value).
Example:
SELECT -1600.05
SELECT CAST(0 AS DOUBLE)
SELECT -1.4e-10
String
' anything ' |
|
A string starts and ends with a single quote. Two single quotes can be used to create a single quote inside a string.
Example:
'John''s car'
'A' 'B' 'C'
Time
TIME |
| ' 12:00:00 |
| ' |
A time literal. A value is between 0:00:00 and 23:59:59.999999999 and has nanosecond resolution.
Example:
TIME '23:59:59'
Time with time zone
TIME WITH TIME ZONE ' 12:00:00 |
|
| ' |
A time with time zone literal. A value is between 0:00:00 and 23:59:59.999999999 and has nanosecond resolution.
Example:
TIME WITH TIME ZONE '23:59:59+01'
TIME WITH TIME ZONE '10:15:30.334-03:30'
TIME WITH TIME ZONE '0:00:00Z'
Timestamp
TIMESTAMP |
| ' |
| 2000-01-01 12:00:00 |
| ' |
A timestamp literal.
Example:
TIMESTAMP '2005-12-31 23:59:59'
Timestamp with time zone
TIMESTAMP WITH TIME ZONE ' |
| 2000-01-01 12:00:00 |
|
| ' |
A timestamp with time zone literal. If name of time zone is specified it will be converted to time zone offset.
Example:
TIMESTAMP WITH TIME ZONE '2005-12-31 23:59:59Z'
TIMESTAMP WITH TIME ZONE '2005-12-31 23:59:59-10:00'
TIMESTAMP WITH TIME ZONE '2005-12-31 23:59:59.123+05'
TIMESTAMP WITH TIME ZONE '2005-12-31 23:59:59.123456789 Europe/London'
Interval
An interval literal.
Example:
INTERVAL '1-2' YEAR TO MONTH
INTERVAL YEAR
INTERVAL |
| ' |
| yearInt ' YEAR |
An INTERVAL YEAR
literal.
Example:
INTERVAL '10' YEAR
INTERVAL MONTH
INTERVAL |
| ' |
| monthInt ' MONTH |
An INTERVAL MONTH
literal.
Example:
INTERVAL '10' MONTH
INTERVAL DAY
INTERVAL |
| ' |
| dayInt ' DAY |
An INTERVAL DAY
literal.
Example:
INTERVAL '10' DAY
INTERVAL HOUR
INTERVAL |
| ' |
| hourInt ' HOUR |
An INTERVAL HOUR
literal.
Example:
INTERVAL '10' HOUR
INTERVAL MINUTE
INTERVAL |
| ' |
| minuteInt ' MINUTE |
An INTERVAL MINUTE
literal.
Example:
INTERVAL '10' MINUTE
INTERVAL SECOND
INTERVAL |
| ' |
| secondInt |
| ' SECOND |
An INTERVAL SECOND
literal.
Example:
INTERVAL '10.123' SECOND
INTERVAL YEAR TO MONTH
An INTERVAL YEAR TO MONTH
literal.
Example:
INTERVAL '1-6' YEAR TO MONTH
INTERVAL DAY TO HOUR
An INTERVAL DAY TO HOUR
literal.
Example:
INTERVAL '10 11' DAY TO HOUR
INTERVAL DAY TO MINUTE
INTERVAL |
| ' |
| dayInt 12:00:00 ' DAY TO MINUTE |
An INTERVAL DAY TO MINUTE
literal.
Example:
INTERVAL '10 11:12' DAY TO MINUTE
INTERVAL DAY TO SECOND
INTERVAL |
| ' |
| dayInt 12:00:00 |
| ' DAY TO SECOND |
An INTERVAL DAY TO SECOND
literal.
Example:
INTERVAL '10 11:12:13.123' DAY TO SECOND
INTERVAL HOUR TO MINUTE
INTERVAL |
| ' |
| 12:00:00 ' HOUR TO MINUTE |
An INTERVAL HOUR TO MINUTE
literal.
Example:
INTERVAL '10:11' HOUR TO MINUTE
INTERVAL HOUR TO SECOND
INTERVAL |
| ' |
| 12:00:00 |
| ' HOUR TO SECOND |
An INTERVAL HOUR TO SECOND
literal.
Example:
INTERVAL '10:11:12.123' HOUR TO SECOND
INTERVAL MINUTE TO SECOND
INTERVAL |
| ' |
| 12:00:00 |
| ' MINUTE TO SECOND |
An INTERVAL MINUTE TO SECOND
literal.
Example:
INTERVAL '11:12.123' MINUTE TO SECOND
Datetime fields
Datetime field
Fields for EXTRACT, DATEADD
, and DATEDIFF
functions.
Example:
YEAR
Year field
YEAR | ||
YYYY | ||
YY | ||
SQL_TSI_YEAR |
Year.
Example:
YEAR
Month field
MONTH | ||
MM | ||
M | ||
SQL_TSI_MONTH |
Month (1-12).
Example:
MONTH
Day of month field
DAY | ||
DD | ||
D | ||
SQL_TSI_DAY |
Day of month (1-31).
Example:
DAY
Hour field
HOUR | ||
HH | ||
SQL_TSI_HOUR |
Hour (0-23).
Example:
HOUR
Minute field
MINUTE | ||
MI | ||
N | ||
SQL_TSI_MINUTE |
Minute (0-59).
Example:
MINUTE
Second field
SECOND | ||
SS | ||
S | ||
SQL_TSI_SECOND |
Second (0-59).
Example:
SECOND
Millisecond field
MILLISECOND | ||
MS |
Millisecond (0-999).
Example:
MILLISECOND
Microsecond field
MICROSECOND | ||
MCS |
Microsecond (0-999999).
Example:
MICROSECOND
Nanosecond field
NANOSECOND | ||
NS |
Nanosecond (0-999999999).
Example:
NANOSECOND
Timezone hour field
TIMEZONE_HOUR
Timezone hour (from -18 to +18).
Example:
TIMEZONE_HOUR
Timezone minute field
TIMEZONE_MINUTE
Timezone minute (from -59 to +59).
Example:
TIMEZONE_MINUTE
Timezone second field
TIMEZONE_SECOND
Timezone second (from -59 to +59). Local mean time (LMT
) used in the past may have offsets with seconds. Standard time doesn't use such offsets.
Example:
TIMEZONE_SECOND
Day of week field
DAY_OF_WEEK | ||
DAYOFWEEK | ||
DOW |
Day of week (1-7). Sunday is 1.
Example:
DAY_OF_WEEK
ISO week year field
ISO_YEAR | ||
ISOYEAR |
Returns the ISO
week year from a date/time value.
Example:
ISO_YEAR
ISO day of week field
ISO_DAY_OF_WEEK | ||
ISODOW |
ISO
day of week (1-7). Monday is 1.
Example:
ISO_DAY_OF_WEEK
Week of year field
WEEK | ||
WW | ||
W | ||
SQL_TSI_WEEK |
Week of year (1-53). EXTRACT
function uses local rules to get number of week in year. DATEDIFF
function uses Sunday as a first day of week.
Example:
WEEK
ISO week of year field
ISO_WEEK
ISO
week of year (1-53). ISO
definition is used when first week of year should have at least four days and week is started with Monday.
Example:
ISO_WEEK
Quarter field
QUARTER
Quarter (1-4).
Example:
QUARTER
Day of year field
DAYOFYEAR | ||
DAY_OF_YEAR | ||
DOY | ||
DY |
Day of year (1-366).
Example:
DAYOFYEAR
Epoch field
EPOCH
For TIMESTAMP
values number of seconds since 1970-01-01 00:00:00 in local time zone. For TIMESTAMP WITH TIME ZONE
values number of seconds since 1970-01-01 00:00:00 in UTC
time zone. For DATE
values number of seconds since 1970-01-01. For TIME
values number of seconds since midnight.
Example:
EPOCH
Other Grammar
Alias
An alias is a name that is only valid in the context of the statement.
Example:
A
And Condition
Value or condition.
Example:
ID=1 AND NAME='Hi'
Case
Returns the first expression where the value is equal to the test expression. If no else part is specified, return NULL
.
Example:
CASE CNT WHEN 0 THEN 'No' WHEN 1 THEN 'One' ELSE 'Some' END
Case When
Returns the first expression where the condition is true. If no else part is specified, return NULL
.
Example:
CASE WHEN CNT<10 THEN 'Low' ELSE 'High' END
Cipher
AES
Only the algorithm AES
(AES-128
) is supported currently.
Example:
AES
Column Definition
dataType |
|
|
|
|
|
|
|
|
|
Default expressions are used if no explicit value was used when adding a row. The computed column expression is evaluated and assigned whenever the row changes. On update column expression is used if row is updated, at least one column have a new value that is different from its previous value and value for this column is not set explicitly in update statement.
Identity, auto-increment, or generated as identity columns are columns with a sequence as the default. The column declared as the identity columns with IDENTITY
data type or with IDENTITY
() clause is implicitly the primary key column of this table. AUTO_INCREMENT
and GENERATED
clauses do not create the primary key constraint. GENERATED ALWAYS
is accepted but treated in the same way as GENERATED BY DEFAULT
.
The invisible column will not be displayed as a result of SELECT
* query. Otherwise, it works as normal column.
The options PRIMARY KEY, UNIQUE
, and CHECK
are not supported for ALTER
statements.
Check constraints can reference columns of the table, and they can reference objects that exist while the statement is executed. Conditions are only checked when a row is added or modified in the table where the constraint exists.
Example:
CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255) DEFAULT '');
CREATE TABLE TEST(ID BIGINT IDENTITY);
CREATE TABLE TEST(QUANTITY INT, PRICE DECIMAL, AMOUNT DECIMAL AS QUANTITY*PRICE);
Comments
| |||
| |||
|
Comments can be used anywhere in a command and are ignored by the database. Line comments end with a newline. Block comments cannot be nested, but can be multiple lines long.
Example:
// This is a comment
Compare
| |||
| |||
| |||
= | |||
< | |||
> | |||
| |||
&& |
Comparison operator. The operator != is the same as <>. The operator &&
means overlapping; it can only be used with geometry types.
Example:
<>
Condition
| ||||||||||
| ||||||||||
| ||||||||||
| ||||||||||
|
Boolean value or condition.
NOT
condition negates the result of subcondition and returns TRUE, FALSE
, or UNKNOWN
(NULL
).
EXISTS
predicate tests whether the result of the specified subquery is not empty and returns TRUE
or FALSE
.
UNIQUE
predicate tests absence of duplicate rows in the specified subquery and returns TRUE
or FALSE
. Rows with NULL
value in any column are ignored.
INTERSECTS
checks whether 2D bounding boxes of specified geometries intersects with each other and returns TRUE
or FALSE
.
Example:
ID<>2
NOT(A OR B)
EXISTS (SELECT NULL FROM TEST T WHERE T.GROUP_ID = P.ID)
UNIQUE (SELECT A, B FROM TEST T WHERE T.CATEGORY = CAT)
INTERSECTS(GEOM1, GEOM2)
Condition Right Hand Side
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
The right hand side of a condition.
Quantified comparison predicate ALL
returns TRUE
if specified comparison operation between left size of condition and each row from a subquery returns TRUE
, including case when there are no rows. ALL
predicate returns FALSE
if at least one such comparison returns FALSE
. Otherwise it returns NULL
.
Quantified comparison predicates ANY
and SOME
return TRUE
if specified comparison operation between left size of condition and at least one row from a subquery returns TRUE
. ANY
and SOME
predicates return FALSE
if all such comparisons return FALSE
. Otherwise it returns NULL
. Note that these predicates have priority over ANY
and SOME
aggregate functions with subquery on the right side. Use parentheses around aggregate function.
The conditions IS [ NOT ] NULL
check whether the specified value(s) are NULL
values. To test multiple values a row value must be specified. IS NULL
returns TRUE
if and only if all values are NULL
values; otherwise it returns FALSE
. IS NOT NULL
returns TRUE
if and only if all values are not NULL
values; otherwise it returns FALSE
.
The conditions IS [ NOT ] DISTINCT FROM
are null-safe, meaning NULL
is considered the same as NULL
, and the condition never evaluates to NULL
.
Boolean tests IS [ NOT ] { TRUE | FALSE | UNKNOWN }
check whether the specified value is (not) TRUE, FALSE
, or UNKNOWN
(NULL
) and return TRUE
or FALSE
.
The conditions IS [ NOT ] OF
check whether the data type of the specified operand is one of the specified data types. Some data types have multiple names, these names are considered as equal here. Domains and their base data types are currently not distinguished from each other. Precision and scale are also ignored. If operand is NULL
, the result is NULL
.
The conditions IS [ NOT ] JSON
check whether value of the specified string, binary data, or a JSON
is a valid JSON
. If ARRAY, OBJECT
, or SCALAR
is specified, only JSON
items of the specified type are considered as valid. If WITH UNIQUE [ KEYS ]
is specified only JSON
with unique keys is considered as valid. These conditions aren't null-safe, they return NULL
if operand is NULL
.
When comparing with LIKE
, the wildcards characters are _
(any one character) and %
(any characters). The database uses an index when comparing with LIKE
except if the operand starts with a wildcard. To search for the characters %
and _
, the characters need to be escaped. The default escape character is \
(backslash). To select no escape character, use ESCAPE ''
(empty string). At most one escape character is allowed. Each character that follows the escape character in the pattern needs to match exactly. Patterns that end with an escape character are invalid and the expression returns NULL
.
ILIKE
does a case-insensitive compare.
When comparing with REGEXP
, regular expression matching is used. See Java Matcher.find
for details.
Example:
VALUE > 10
A IS NULL
(A, B) IS NOT NULL
A IS NOT DISTINCT FROM B
T IS OF (DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE)
T IS JSON OBJECT WITH UNIQUE KEYS
LIKE 'Jo%'
Constraint
constraintNameDefinition |
| |||||||||||||||||||
| |||||||||||||||||||
referentialConstraint | |||||||||||||||||||
|
Defines a constraint. The check condition must evaluate to TRUE, FALSE
or NULL
. TRUE
and NULL
mean the operation is to be permitted, and FALSE
means the operation is to be rejected. To prevent NULL
in a column, use NOT NULL
instead of a check constraint.
Example:
PRIMARY KEY(ID, NAME)
Constraint Name Definition
CONSTRAINT |
| newConstraintName |
Defines a constraint name.
Example:
CONSTRAINT CONST_ID
Csv Options
Optional parameters for CSVREAD
and CSVWRITE
. Instead of setting the options one by one, all options can be combined into a space separated key-value pairs, as follows: STRINGDECODE('charset=UTF-8 escape=\" fieldDelimiter=\" fieldSeparator=, ' || 'lineComment=# lineSeparator=\n null= rowSeparator=')
. The following options are supported:
caseSensitiveColumnNames
(true or false; disabled by default),
charset
(for example 'UTF
-8'),
escape
(the character that escapes the field delimiter),
fieldDelimiter
(a double quote by default),
fieldSeparator
(a comma by default),
lineComment
(disabled by default),
lineSeparator
(the line separator used for writing; ignored for reading),
null
, Support reading existing CSV
files that contain explicit null
delimiters. Note that an empty, unquoted values are also treated as null.
preserveWhitespace
(true or false; disabled by default),
writeColumnHeader
(true or false; enabled by default).
For a newline or other special character, use STRINGDECODE
as in the example above. A space needs to be escaped with a backslash ('\ '
), and a backslash needs to be escaped with another backslash ('\\'
). All other characters are not to be escaped, that means newline and tab characters are written as such.
Example:
CALL CSVWRITE('test2.csv', 'SELECT * FROM TEST', 'charset=UTF-8 fieldSeparator=|');
Data Change Delta Table
Executes the inner data change command and returns old, new, or final rows.
OLD
is not allowed for INSERT
command. It returns old rows.
NEW
and FINAL
are not allowed for DELETE
command.
NEW
returns new rows after evaluation of default expressions, but before execution of triggers.
FINAL
returns new rows after execution of triggers. If table or view has INSTEAD OF
triggers FINAL
is not allowed.
Example:
SELECT ID FROM FINAL TABLE (INSERT INTO TEST (A, B) VALUES (1, 2))
Data Type
A data type definition.
Example:
INT
Digit
0-9
A digit.
Example:
0
Expression
andCondition |
|
Value or condition.
Example:
ID=1 OR NAME='Hi'
Factor
A value or a numeric factor.
Example:
ID * 10
Grouping element
expression | ||||||||||||
| ||||||||||||
|
A grouping element of GROUP BY
clause.
Example:
A
(B, C)
()
Hex
The hexadecimal representation of a number or of bytes with optional space characters. Two hexadecimal digit characters are one byte.
Example:
cafe
11 22 33
a b c d
Index Column
columnName |
|
|
Indexes this column in ascending or descending order. Usually it is not required to specify the order; however doing so will speed up large queries that order the column in the same way.
Example:
NAME
Insert columns and source
| ||||||||||||||||||||||||||||||||||||||||||||
|
Names of columns and their values for INSERT
statement.
Example:
(ID, NAME) VALUES (1, 'Test')
Insert values
VALUES |
| , |
|
Values for INSERT
statement.
Example:
VALUES (1, 'Test')
Join specification
| |||||||||||
|
Specifies a join condition or column names.
Example:
ON B.ID = A.PARENT_ID
USING (ID)
Merge when clause
WHEN MATCHED
or WHEN NOT MATCHED
clause for MERGE USING
command.
Example:
WHEN MATCHED THEN DELETE
Merge when matched clause
WHEN MATCHED |
| THEN |
| ||||||||||||||||||||
DELETE | ||||||||||||||||||||
|
WHEN MATCHED
clause for MERGE USING
command.
If both UPDATE
and DELETE
are specified, DELETE
can delete only rows that were updated, WHERE
condition in DELETE
clause can be used to specify which updated rows should be deleted. This condition checks values in updated row. Such combination of UPDATE
and DELETE
clauses in single WHEN MATCHED
clause is not allowed when MERGE
statement is included into data change delta table.
Example:
WHEN MATCHED THEN UPDATE SET VALUE = S.VALUE
WHEN MATCHED THEN DELETE
Merge when not matched clause
WHEN NOT MATCHED |
| THEN INSERT insertColumnsAndSource |
WHEN NOT MATCHED
clause for MERGE USING
command.
Example:
WHEN NOT MATCHED THEN INSERT (ID, NAME) VALUES (S.ID, S.NAME)
Name
| ||||||||||||||||||||||||
quotedName |
With default settings unquoted names are converted to upper case. There is no maximum name length.
Identifiers in H2 are case sensitive by default. Because unquoted names are converted to upper case, they can be written in any case anyway. When both quoted and unquoted names are used for the same identifier the quoted names must be written in upper case. Identifiers with lowercase characters can be written only as a quoted name, they aren't accessible with unquoted names.
If DATABASE_TO_UPPER
setting is set to FALSE
the unquoted names aren't converted to upper case.
If DATABASE_TO_LOWER
setting is set to TRUE
the unquoted names are converted to lower case instead.
If CASE_INSENSITIVE_IDENTIFIERS
setting is set to TRUE
all identifiers are case insensitive.
Example:
TEST
Operand
Performs the concatenation of character string, binary string, or array values. In the default mode, the result is NULL
if either parameter is NULL
. In compatibility modes result of string concatenation with NULL
parameter can be different.
Example:
'Hi' || ' Eva'
X'AB' || X'CD'
ARRAY[1, 2] || 3
1 || ARRAY[2, 3]
ARRAY[1, 2] || ARRAY[3, 4]
Order
|
|
|
Sorts the result by the given column number, or by an expression. If the expression is a single parameter, then the value is interpreted as a column number. Negative column numbers reverse the sort order.
Example:
NAME DESC NULLS LAST
Query
A query, such as SELECT
, explicit table, or table value.
Example:
SELECT ID FROM TEST;
TABLE TEST;
VALUES (1, 2), (3, 4);
Quoted Name
" anything " |
Case of characters in quoted names is preserved as is. Such names can contain spaces. There is no maximum name length. Two double quotes can be used to create a single double quote inside an identifier. With default settings identifiers in H2 are case sensitive.
Example:
"FirstName"
Referential Constraint
FOREIGN KEY ( columnName |
| ) |
REFERENCES |
|
|
|
|
Defines a referential constraint. If the table name is not specified, then the same table is referenced. RESTRICT
is the default action. If the referenced columns are not specified, then the primary key columns are used. The required indexes are automatically created if required. Some tables may not be referenced, such as metadata tables.
Example:
FOREIGN KEY(ID) REFERENCES TEST(ID)
Referential Action
CASCADE | ||||||||||
RESTRICT | ||||||||||
| ||||||||||
|
The action CASCADE
will cause conflicting rows in the referencing (child) table to be deleted or updated. RESTRICT
is the default action. As this database does not support deferred checking, RESTRICT
and NO ACTION
will both throw an exception if the constraint is violated. The action SET DEFAULT
will set the column in the referencing (child) table to the default value, while SET NULL
will set it to NULL
.
Example:
FOREIGN KEY(ID) REFERENCES TEST(ID) ON UPDATE CASCADE
Script Compression Encryption
The compression and encryption algorithm to use for script files. When using encryption, only DEFLATE
and LZF
are supported. LZF
is faster but uses more space.
Example:
COMPRESSION LZF
Row value expression
| |||||||||||||||||||
| |||||||||||||||||||
expression |
A row value expression.
Example:
ROW (1)
(1, 2)
1
Select Expression
An expression in a SELECT
statement.
Example:
ID AS VALUE
Sequence value expression
| VALUE FOR |
| sequenceName |
The next or current value of a sequence.
When the next value is requested the sequence is incremented and the current value of the sequence and the last identity in the current session are updated with the generated value. Used values are never re-used, even when the transaction is rolled back.
Current value may only be requested after generation of the sequence value in the current session. It returns the latest generated value for the current session.
If a single command contains next and current value expressions for the same sequence there is no guarantee that the next value expression will be evaluated before the evaluation of current value expression.
Example:
NEXT VALUE FOR SEQ1
CURRENT VALUE FOR SCHEMA2.SEQ2
Sequence options
sequenceOption |
|
Options of a sequence.
Example:
START WITH 1
START WITH 10 INCREMENT BY 10
Sequence option
| ||||||||||
| ||||||||||
| ||||||||||
| ||||||||||
NOMINVALUE | ||||||||||
| ||||||||||
| ||||||||||
NOMAXVALUE | ||||||||||
CYCLE | ||||||||||
| ||||||||||
NOCYCLE | ||||||||||
| ||||||||||
| ||||||||||
NOCACHE |
Option of a sequence.
START WITH
and RESTART WITH
are used to set the first generated value of the sequence. START WITH
may only be used in CREATE SEQUENCE
command and it column definition, RESTART WITH
may only be used in ALTER SEQUENCE
command. The default is MINVALUE
for incrementing sequences and MAXVALUE
for decrementing sequences.
INCREMENT BY
specifies the step of the sequence, may be positive or negative, but may not be zero. The default is 1.
MINVALUE
and MAXVALUE
specify the bounds of the sequence.
Sequences with CYCLE
option start the generation again from MINVALUE
(incrementing sequences) or MAXVALUE
(decrementing sequences) instead of exhausting with an error.
The CACHE
option sets the number of pre-allocated numbers. If the system crashes without closing the database, at most this many numbers are lost. The default cache size is 32. NO CACHE
option or the cache size 1 or lower disable the cache.
Example:
START WITH 10000
CYCLE
NO CACHE
Set clause list
|
|
List of SET
clauses.
Example:
NAME = 'Test', VALUE = 2
(A, B) = (1, 2)
(A, B) = (1, 2), C = 3
(A, B) = (SELECT X, Y FROM OTHER T2 WHERE T1.ID = T2.ID)
Summand
A value or a numeric sum.
Please note the text concatenation operator is ||
.
Example:
ID + 20
Table Expression
| |||||||||||
| |||||||||||
unnest | |||||||||||
table | |||||||||||
dataChangeDeltaTable |
|
|
|
Joins a table. The join specification is not supported for cross and natural joins. A natural join is an inner join, where the condition is automatically on the columns with the same name.
Example:
TEST1 AS T1 LEFT JOIN TEST2 AS T2 ON T1.ID = T2.PARENT_ID
Within group specification
WITHIN GROUP ( ORDER BY expression |
|
| ) |
Group specification for ordered set functions.
Example:
WITHIN GROUP (ORDER BY ID DESC)
Wildcard expression
|
|
A wildcard expression in a SELECT
statement. A wildcard expression represents all visible columns. Some columns can be excluded with optional EXCEPT
clause.
Example:
*
* EXCEPT (DATA)
Window name or specification
A window name or inline specification for a window function or aggregate.
Window functions in H2 may require a lot of memory for large queries.
Example:
W1
(ORDER BY ID)
Window specification
A window specification for a window, window function or aggregate.
If name of an existing window is specified its clauses are used by default.
Optional window partition clause separates rows into independent partitions. Each partition is processed separately. If this clause is not present there is one implicit partition with all rows.
Optional window order clause specifies order of rows in the partition. If some rows have the same order position they are considered as a group of rows in optional window frame clause.
Optional window frame clause specifies which rows are processed by a window function, see its documentation for a more details.
Example:
()
(W1 ORDER BY ID)
(PARTITION BY CATEGORY)
(PARTITION BY CATEGORY ORDER BY NAME, ID)
(ORDER BY Y RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES)
Window frame
ROWS | ||
RANGE | ||
GROUP |
windowFramePreceding | |||
|
|
A window frame clause. May be specified only for aggregates and FIRST_VALUE
(), LAST_VALUE
(), and NTH_VALUE
() window functions.
If this clause is not specified for an aggregate or window function that supports this clause the default window frame depends on window order clause. If window order clause is also not specified the default window frame contains all the rows in the partition. If window order clause is specified the default window frame contains all preceding rows and all rows from the current group.
Window frame unit determines how rows or groups of rows are selected and counted. If ROWS
is specified rows are not grouped in any way and relative numbers of rows are used in bounds. If RANGE
is specified rows are grouped according window order clause, preceding and following values mean the difference between value in the current row and in the target rows, and CURRENT ROW
in bound specification means current group of rows. If GROUPS
is specified rows are grouped according window order clause, preceding and following values means relative number of groups of rows, and CURRENT ROW
in bound specification means current group of rows.
If only window frame preceding clause is specified it is treated as BETWEEN
windowFramePreceding AND CURRENT ROW
.
Optional window frame exclusion clause specifies rows that should be excluded from the frame. EXCLUDE CURRENT ROW
excludes only the current row regardless the window frame unit. EXCLUDE GROUP
excludes the whole current group of rows, including the current row. EXCLUDE TIES
excludes the current group of rows, but not the current row. EXCLUDE NO OTHERS
is default and it does not exclude anything.
Example:
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES
Window frame preceding
| |||
| |||
|
A window frame preceding clause. If value is specified it should not be negative.
Example:
UNBOUNDED PRECEDING
1 PRECEDING
CURRENT ROW
Window frame bound
A window frame bound clause. If value is specified it should not be negative.
Example:
UNBOUNDED PRECEDING
UNBOUNDED FOLLOWING
1 FOLLOWING
CURRENT ROW
Term
value | ||||||||||
column | ||||||||||
| ||||||||||
sequenceValueExpression | ||||||||||
Function | ||||||||||
| ||||||||||
| ||||||||||
query | ||||||||||
case | ||||||||||
caseWhen | ||||||||||
userDefinedFunctionName |
timeZone |
A value. Parameters can be indexed, for example ?1
meaning the first parameter.
Example:
'Hello'
Time zone
AT |
|
A time zone. Converts the timestamp with or without time zone into timestamp with time zone at specified time zone. If a day-time interval is specified as a time zone, it may not have fractional seconds and must be between -18 to 18 hours inclusive.
Example:
AT LOCAL
AT TIME ZONE '2'
AT TIME ZONE '-6:00'
AT TIME ZONE INTERVAL '10:00' HOUR TO MINUTE
AT TIME ZONE INTERVAL '10:00:00' HOUR TO SECOND
AT TIME ZONE 'UTC'
AT TIME ZONE 'Europe/London'
Column
|
|
A column name with optional table alias and schema. _ROWID_
can be used to access unique row identifier.
Example:
ID