H2 database logo   ▲

Home
Download
Cheat Sheet

Documentation
Quickstart
Installation
Tutorial
Features
Performance
Advanced

Reference
Commands
Functions
• Aggregate • Window

Data Types
SQL Grammar
System Tables
Javadoc
PDF (1.5 MB)

Support
FAQ
Error Analyzer
Google Group (English)
Google Group (Japanese)
Google Group (Chinese)

Appendix
History & Roadmap
License
Build
Links
MVStore
Architecture

 

SQL Grammar

Index

Literals

Value
Array
Boolean
Bytes
Date
Date and time
Decimal
Dollar Quoted String
Hex Number
Int
JSON
Long
Null
Number
Numeric
String
Time
Time with time zone
Timestamp
Timestamp with time zone
Interval
INTERVAL YEAR
INTERVAL MONTH
INTERVAL DAY
INTERVAL HOUR
INTERVAL MINUTE
INTERVAL SECOND
INTERVAL YEAR TO MONTH
INTERVAL DAY TO HOUR
INTERVAL DAY TO MINUTE
INTERVAL DAY TO SECOND
INTERVAL HOUR TO MINUTE
INTERVAL HOUR TO SECOND
INTERVAL MINUTE TO SECOND

Datetime fields

Datetime field
Year field
Month field
Day of month field
Hour field
Minute field
Second field
Millisecond field
Microsecond field
Nanosecond field
Timezone hour field
Timezone minute field
Timezone second field
Day of week field
ISO week year field
ISO day of week field
Week of year field
ISO week of year field
Quarter field
Day of year field
Epoch field

Other Grammar

Alias
And Condition
Case
Case When
Cipher
Column Definition
Comments
Compare
Condition
Condition Right Hand Side
Constraint
Constraint Name Definition
Csv Options
Data Change Delta Table
Data Type
Digit
Expression
Factor
Grouping element
Hex
Index Column
Insert columns and source
Insert values
Join specification
Merge when clause
Merge when matched clause
Merge when not matched clause
Name
Operand
Order
Query
Quoted Name
Referential Constraint
Referential Action
Script Compression Encryption
Row value expression
Select Expression
Sequence value expression
Sequence options
Sequence option
Set clause list
Summand
Table Expression
Within group specification
Wildcard expression
Window name or specification
Window specification
Window frame
Window frame preceding
Window frame bound
Term
Time zone
Column

Details

Click on the header to switch between railroad diagram and BNF.

Literals

Value

string | dollarQuotedString | numeric | dateAndTime | boolean | bytes
| interval | array | json | null
string
dollarQuotedString
numeric
dateAndTime
boolean
bytes
interval
array
json
null

A literal value of any data type, or null.

Example:

10

Array

ARRAY '[' [ expression [,...] ] ']'
ARRAY [
 
expression
 
, ...
]

An array of values.

Example:

ARRAY[1, 2]
ARRAY[1]
ARRAY[]

Boolean

TRUE | FALSE | UNKNOWN
TRUE
FALSE
UNKNOWN

A boolean value. UNKNOWN is a NULL value with the boolean data type.

Example:

TRUE

Bytes

X'hex' [ 'hex' [...] ]
X ' hex '
 
' hex '
 
...

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 '[-]yyyy-MM-dd'
DATE '
 
-
2000-01-01 '

A date literal.

Example:

DATE '2004-12-31'

Date and time

date | time | timeWithTimeZone | timestamp | timestampWithTimeZone
date
time
timeWithTimeZone
timestamp
timestampWithTimeZone

A literal value of any date-time data type.

Example:

TIMESTAMP '1999-01-31 10:00:00'

Decimal

[ + | - ] { { number [ . number ] } | { . number } }
[ E [ + | - ] expNumber [...] ] ]
 
+
-
number
 
. number
. number

 
E
 
+
-
expNumber
 
...

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$$
$ $ 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 { digit | a-f | A-F } [...]
 
+
-
0x
digit
a - f
A-F
 
...

A number written in hexadecimal notation.

Example:

0xff

Int

[ + | - ] number
 
+
-
number

The maximum integer number is 2147483647, the minimum is -2147483648.

Example:

10

JSON

JSON { bytes | string }
JSON
bytes
string

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
 
+
-
number

Long numbers are between -9223372036854775808 and 9223372036854775807.

Example:

100000

Null

NULL
NULL

NULL is a value without data type and means 'unknown value'.

Example:

NULL

Number

digit [...]
digit
 
...

The maximum length of the number depends on the data type used.

Example:

100

Numeric

decimal | int | long | hexNumber
decimal
int
long
hexNumber

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' [ 'anything' [...] ]
' anything '
 
' 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 [ WITHOUT TIME ZONE ] 'hh:mm:ss[.nnnnnnnnn]'
TIME
 
WITHOUT TIME ZONE
' 12:00:00
 
. 000000000
'

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 'hh:mm:ss[.nnnnnnnnn]{Z | { - | + } timeZoneOffsetString}'
TIME WITH TIME ZONE ' 12:00:00
 
. 000000000
Z
-
+
timeZoneOffsetString
'

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 [ WITHOUT TIME ZONE ] '[-]yyyy-MM-dd hh:mm:ss[.nnnnnnnnn]'
TIMESTAMP
 
WITHOUT TIME ZONE
'
 
-
2000-01-01 12:00:00
 
. 000000000
'

A timestamp literal.

Example:

TIMESTAMP '2005-12-31 23:59:59'

Timestamp with time zone

TIMESTAMP WITH TIME ZONE '[-]yyyy-MM-dd hh:mm:ss[.nnnnnnnnn]
[Z | { - | + } timeZoneOffsetString | timeZoneNameString ]'
TIMESTAMP WITH TIME ZONE '
 
-
2000-01-01 12:00:00
 
. 000000000

 
Z
-
+
timeZoneOffsetString
timeZoneNameString
'

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

intervalYear | intervalMonth | intervalDay | intervalHour | intervalMinute
| intervalSecond | intervalYearToMonth | intervalDayToHour
| intervalDayToMinute | intervalDayToSecond | intervalHourToMinute
| intervalHourToSecond | intervalMinuteToSecond
intervalYear
intervalMonth
intervalDay
intervalHour
intervalMinute
intervalSecond
intervalYearToMonth
intervalDayToHour
intervalDayToMinute
intervalDayToSecond
intervalHourToMinute
intervalHourToSecond
intervalMinuteToSecond

An interval literal.

Example:

INTERVAL '1-2' YEAR TO MONTH

INTERVAL YEAR

INTERVAL [-|+] '[-|+]yearInt' YEAR
INTERVAL
 
-
+
'
 
-
+
yearInt ' YEAR

An INTERVAL YEAR literal.

Example:

INTERVAL '10' YEAR

INTERVAL MONTH

INTERVAL [-|+] '[-|+]monthInt' MONTH
INTERVAL
 
-
+
'
 
-
+
monthInt ' MONTH

An INTERVAL MONTH literal.

Example:

INTERVAL '10' MONTH

INTERVAL DAY

INTERVAL [-|+] '[-|+]dayInt' DAY
INTERVAL
 
-
+
'
 
-
+
dayInt ' DAY

An INTERVAL DAY literal.

Example:

INTERVAL '10' DAY

INTERVAL HOUR

INTERVAL [-|+] '[-|+]hourInt' HOUR
INTERVAL
 
-
+
'
 
-
+
hourInt ' HOUR

An INTERVAL HOUR literal.

Example:

INTERVAL '10' HOUR

INTERVAL MINUTE

INTERVAL [-|+] '[-|+]minuteInt' MINUTE
INTERVAL
 
-
+
'
 
-
+
minuteInt ' MINUTE

An INTERVAL MINUTE literal.

Example:

INTERVAL '10' MINUTE

INTERVAL SECOND

INTERVAL [-|+] '[-|+]secondInt[.nnnnnnnnn]' SECOND
INTERVAL
 
-
+
'
 
-
+
secondInt
 
. 000000000
' SECOND

An INTERVAL SECOND literal.

Example:

INTERVAL '10.123' SECOND

INTERVAL YEAR TO MONTH

INTERVAL [-|+] '[-|+]yearInt-monthInt' YEAR TO MONTH
INTERVAL
 
-
+
'
 
-
+
yearInt - monthInt ' YEAR TO MONTH

An INTERVAL YEAR TO MONTH literal.

Example:

INTERVAL '1-6' YEAR TO MONTH

INTERVAL DAY TO HOUR

INTERVAL [-|+] '[-|+]dayInt hoursInt' DAY TO HOUR
INTERVAL
 
-
+
'
 
-
+
dayInt hoursInt ' DAY TO HOUR

An INTERVAL DAY TO HOUR literal.

Example:

INTERVAL '10 11' DAY TO HOUR

INTERVAL DAY TO MINUTE

INTERVAL [-|+] '[-|+]dayInt hh:mm' 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 hh:mm:ss[.nnnnnnnnn]' DAY TO SECOND
INTERVAL
 
-
+
'
 
-
+
dayInt 12:00:00
 
. 000000000
' DAY TO SECOND

An INTERVAL DAY TO SECOND literal.

Example:

INTERVAL '10 11:12:13.123' DAY TO SECOND

INTERVAL HOUR TO MINUTE

INTERVAL [-|+] '[-|+]hh:mm' 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 [-|+] '[-|+]hh:mm:ss[.nnnnnnnnn]' HOUR TO SECOND
INTERVAL
 
-
+
'
 
-
+
12:00:00
 
. 000000000
' HOUR TO SECOND

An INTERVAL HOUR TO SECOND literal.

Example:

INTERVAL '10:11:12.123' HOUR TO SECOND

INTERVAL MINUTE TO SECOND

INTERVAL [-|+] '[-|+]mm:ss[.nnnnnnnnn]' MINUTE TO SECOND
INTERVAL
 
-
+
'
 
-
+
12:00:00
 
. 000000000
' MINUTE TO SECOND

An INTERVAL MINUTE TO SECOND literal.

Example:

INTERVAL '11:12.123' MINUTE TO SECOND

Datetime fields

Datetime field

yearField | monthField | dayOfMonthField
| hourField | minuteField | secondField
| millisecondField | microsecondField | nanosecondField
| timezoneHourField | timezoneMinuteField | timezoneSecondField
| dayOfWeekField | isoWeekYearField | isoDayOfWeekField
| weekOfYearField | isoWeekOfYearField
| quarterField | dayOfYearField | epochField
yearField
monthField
dayOfMonthField
hourField
minuteField
secondField
millisecondField
microsecondField
nanosecondField
timezoneHourField
timezoneMinuteField
timezoneSecondField
dayOfWeekField
isoWeekYearField
isoDayOfWeekField
weekOfYearField
isoWeekOfYearField
quarterField
dayOfYearField
epochField

Fields for EXTRACT, DATEADD, and DATEDIFF functions.

Example:

YEAR

Year field

YEAR | YYYY | YY | SQL_TSI_YEAR
YEAR
YYYY
YY
SQL_TSI_YEAR

Year.

Example:

YEAR

Month field

MONTH | MM | M | SQL_TSI_MONTH
MONTH
MM
M
SQL_TSI_MONTH

Month (1-12).

Example:

MONTH

Day of month field

DAY | DD | D | SQL_TSI_DAY
DAY
DD
D
SQL_TSI_DAY

Day of month (1-31).

Example:

DAY

Hour field

HOUR | HH | SQL_TSI_HOUR
HOUR
HH
SQL_TSI_HOUR

Hour (0-23).

Example:

HOUR

Minute field

MINUTE | MI | N | SQL_TSI_MINUTE
MINUTE
MI
N
SQL_TSI_MINUTE

Minute (0-59).

Example:

MINUTE

Second field

SECOND | SS | S | SQL_TSI_SECOND
SECOND
SS
S
SQL_TSI_SECOND

Second (0-59).

Example:

SECOND

Millisecond field

MILLISECOND | MS
MILLISECOND
MS

Millisecond (0-999).

Example:

MILLISECOND

Microsecond field

MICROSECOND | MCS
MICROSECOND
MCS

Microsecond (0-999999).

Example:

MICROSECOND

Nanosecond field

NANOSECOND | NS
NANOSECOND
NS

Nanosecond (0-999999999).

Example:

NANOSECOND

Timezone hour field

TIMEZONE_HOUR
TIMEZONE_HOUR

Timezone hour (from -18 to +18).

Example:

TIMEZONE_HOUR

Timezone minute field

TIMEZONE_MINUTE
TIMEZONE_MINUTE

Timezone minute (from -59 to +59).

Example:

TIMEZONE_MINUTE

Timezone second field

TIMEZONE_SECOND
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
DAYOFWEEK
DOW

Day of week (1-7). Sunday is 1.

Example:

DAY_OF_WEEK

ISO week year field

ISO_YEAR | ISOYEAR
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
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
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

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

Quarter (1-4).

Example:

QUARTER

Day of year field

DAYOFYEAR | DAY_OF_YEAR | DOY | DY
DAYOFYEAR
DAY_OF_YEAR
DOY
DY

Day of year (1-366).

Example:

DAYOFYEAR

Epoch field

EPOCH
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

name
name

An alias is a name that is only valid in the context of the statement.

Example:

A

And Condition

condition [ { AND condition } [...] ]
condition
 
AND condition
 
...

Value or condition.

Example:

ID=1 AND NAME='Hi'

Case

CASE expression { WHEN expression THEN expression } [...]
[ ELSE expression ] END
CASE expression WHEN expression THEN expression
 
...

 
ELSE expression
END

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

CASE { WHEN expression THEN expression} [...]
[ ELSE expression ] END
CASE WHEN expression THEN expression
 
...

 
ELSE expression
END

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
AES

Only the algorithm AES (AES-128) is supported currently.

Example:

AES

Column Definition

dataType [ VISIBLE | INVISIBLE ]
[ { DEFAULT expression
| AS computedColumnExpression
| GENERATED {ALWAYS | BY DEFAULT} AS IDENTITY [(sequenceOptions)]} ]
[ ON UPDATE expression ] [ [ NOT ] NULL ]
[ { AUTO_INCREMENT | IDENTITY } [ ( startInt [, incrementInt ] ) ] ]
[ SELECTIVITY selectivityInt ] [ COMMENT expression ]
[ PRIMARY KEY [ HASH ] | UNIQUE ] [ CHECK condition ]
dataType
 
VISIBLE
INVISIBLE

 
DEFAULT expression
AS computedColumnExpression
GENERATED
ALWAYS
BY DEFAULT
AS IDENTITY
 
( sequenceOptions )

 
ON UPDATE expression
 
 
NOT
NULL

 
AUTO_INCREMENT
IDENTITY
 
( startInt
 
, incrementInt
)

 
SELECTIVITY selectivityInt
 
COMMENT expression

 
PRIMARY KEY
 
HASH
UNIQUE
 
CHECK condition

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

-- anything | // anything | /* anything */
- - anything
/ / anything
/ * anything * /

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

operand [ conditionRightHandSide ]
| NOT condition
| EXISTS ( query )
| UNIQUE ( query )
| INTERSECTS (operand, operand)
operand
 
conditionRightHandSide
NOT condition
EXISTS ( query )
UNIQUE ( query )
INTERSECTS ( operand , operand )

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

compare { { { ALL | ANY | SOME } ( query ) } | operand }
| IS [ NOT ] NULL
| IS [ NOT ] [ DISTINCT FROM ] operand
| IS [ NOT ] { TRUE | FALSE | UNKNOWN }
| IS [ NOT ] OF (dataType [,...])
| IS [ NOT ] JSON [ VALUE | ARRAY | OBJECT | SCALAR ]
    [ [ WITH | WITHOUT ] UNIQUE [ KEYS ] ]
| BETWEEN operand AND operand
| IN ( { query | expression [,...] } )
| [ NOT ] [ LIKE | ILIKE ] operand [ ESCAPE string ]
| [ NOT ] REGEXP operand
compare
ALL
ANY
SOME
( query )
operand
IS
 
NOT
NULL
IS
 
NOT
 
DISTINCT FROM
operand
IS
 
NOT
TRUE
FALSE
UNKNOWN
IS
 
NOT
OF ( dataType
 
, ...
)
IS
 
NOT
JSON
 
VALUE
ARRAY
OBJECT
SCALAR
 
 
WITH
WITHOUT
UNIQUE
 
KEYS
BETWEEN operand AND operand
IN (
query
expression
 
, ...
)
 
NOT
 
LIKE
ILIKE
operand
 
ESCAPE string
 
NOT
REGEXP operand

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 ]
{ CHECK expression
| UNIQUE ( columnName [,...] )
| referentialConstraint
| PRIMARY KEY [ HASH ] ( columnName [,...] ) }
 
constraintNameDefinition

CHECK expression
UNIQUE ( columnName
 
, ...
)
referentialConstraint
PRIMARY KEY
 
HASH
( columnName
 
, ...
)

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 [ IF NOT EXISTS ] newConstraintName
CONSTRAINT
 
IF NOT EXISTS
newConstraintName

Defines a constraint name.

Example:

CONSTRAINT CONST_ID

Csv Options

charsetString [, fieldSepString [, fieldDelimString [, escString [, nullString]]]]]
| optionString
charsetString
 
, fieldSepString
 
, fieldDelimString
 
, escString
 
, nullString

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

{ OLD | NEW | FINAL } TABLE
( { insert | update | delete | mergeInto | mergeUsing } )
OLD
NEW
FINAL
TABLE

(
insert
update
delete
mergeInto
mergeUsing
)

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

intType | booleanType | tinyintType | smallintType | bigintType | identityType
| decimalType | doubleType | realType | dateType | timeType
| timeWithTimeZoneType | timestampType | timestampWithTimeZoneType
| binaryType | otherType | varcharType | varcharIgnorecaseType | charType
| blobType | clobType | uuidType | arrayType | enumType | intervalType
intType
booleanType
tinyintType
smallintType
bigintType
identityType
decimalType
doubleType
realType
dateType
timeType
timeWithTimeZoneType
timestampType
timestampWithTimeZoneType
binaryType
otherType
varcharType
varcharIgnorecaseType
charType
blobType
clobType
uuidType
arrayType
enumType
intervalType

A data type definition.

Example:

INT

Digit

0-9
0-9

A digit.

Example:

0

Expression

andCondition [ { OR andCondition } [...] ]
andCondition
 
OR andCondition
 
...

Value or condition.

Example:

ID=1 OR NAME='Hi'

Factor

term [ { { * | / | % } term } [...] ]
term
 
*
/
%
term
 
...

A value or a numeric factor.

Example:

ID * 10

Grouping element

expression | (expression [, ...]) | ()
expression
( expression
 
, ...
)
( )

A grouping element of GROUP BY clause.

Example:

A
(B, C)
()

Hex

[' ' [...]] { { digit | a-f | A-F } [' ' [...]] { digit | a-f | A-F } [' ' [...]] } [...]
 
' '
 
...
digit
a - f
A-F
 
' '
 
...
digit
a - f
A-F
 
' '
 
...
 
...

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 [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
columnName
 
ASC
DESC
 
NULLS
FIRST
LAST

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

{ [ ( columnName [,...] ) ]
{ insertValues | [ DIRECT ] [ SORTED ] query | DEFAULT VALUES } }
| { SET { columnName = { DEFAULT | expression } } [,...] }
 
( columnName
 
, ...
)
insertValues
 
DIRECT
 
SORTED
query
DEFAULT VALUES
SET columnName =
DEFAULT
expression
 
, ...

Names of columns and their values for INSERT statement.

Example:

(ID, NAME) VALUES (1, 'Test')

Insert values

VALUES { DEFAULT|expression | [ROW] ({DEFAULT|expression} [,...]) }, [,...]
VALUES
DEFAULT
expression
 
ROW
(
DEFAULT
expression
 
, ...
)
,
 
, ...

Values for INSERT statement.

Example:

VALUES (1, 'Test')

Join specification

ON expression | USING (columnName [,...])
ON expression
USING ( columnName
 
, ...
)

Specifies a join condition or column names.

Example:

ON B.ID = A.PARENT_ID
USING (ID)

Merge when clause

mergeWhenMatchedClause|mergeWhenNotMatchedClause
mergeWhenMatchedClause
mergeWhenNotMatchedClause

WHEN MATCHED or WHEN NOT MATCHED clause for MERGE USING command.

Example:

WHEN MATCHED THEN DELETE

Merge when matched clause

WHEN MATCHED [ AND expression ] THEN
UPDATE SET setClauseList
| DELETE
| {UPDATE SET setClauseList [ WHERE expression ] DELETE [ WHERE expression ]}
WHEN MATCHED
 
AND expression
THEN

UPDATE SET setClauseList
DELETE
UPDATE SET setClauseList
 
WHERE expression
DELETE
 
WHERE expression

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 [ AND expression ] THEN INSERT insertColumnsAndSource
WHEN NOT MATCHED
 
AND expression
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

{ { A-Z|_ } [ { A-Z|_|0-9 } [...] ] } | quotedName
A-Z | _
 
A-Z | _
0-9
 
...
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

summand [ { || summand } [...] ]
summand
 
|| summand
 
...

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

{ int | expression } [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
int
expression
 
ASC
DESC
 
NULLS
FIRST
LAST

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

select | explicitTable | tableValue
select
explicitTable
tableValue

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"
" 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 [ refTableName ] [ ( refColumnName [,...] ) ]
[ ON DELETE referentialAction ] [ ON UPDATE referentialAction ]
FOREIGN KEY ( columnName
 
, ...
)

REFERENCES
 
refTableName
 
( refColumnName
 
, ...
)

 
ON DELETE referentialAction
 
ON UPDATE referentialAction

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 | NO ACTION | SET { DEFAULT | NULL }
CASCADE
RESTRICT
NO ACTION
SET
DEFAULT
NULL

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

[ COMPRESSION { DEFLATE | LZF | ZIP | GZIP } ]
[ CIPHER cipher PASSWORD string ]
 
COMPRESSION
DEFLATE
LZF
ZIP
GZIP

 
CIPHER cipher PASSWORD string

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

ROW (expression, [,...])
| ( [ expression, expression [,...] ] )
| expression
ROW ( expression ,
 
, ...
)
(
 
expression , expression
 
, ...
)
expression

A row value expression.

Example:

ROW (1)
(1, 2)
1

Select Expression

wildcardExpression | expression [ [ AS ] columnAlias ]
wildcardExpression
expression
 
 
AS
columnAlias

An expression in a SELECT statement.

Example:

ID AS VALUE

Sequence value expression

{ NEXT | CURRENT } VALUE FOR [schemaName.]sequenceName
NEXT
CURRENT
VALUE FOR
 
schemaName .
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 [...]
sequenceOption
 
...

Options of a sequence.

Example:

START WITH 1
START WITH 10 INCREMENT BY 10

Sequence option

{ START | RESTART } WITH long
| INCREMENT BY long
| MINVALUE long | NO MINVALUE | NOMINVALUE
| MAXVALUE long | NO MAXVALUE | NOMAXVALUE
| CYCLE | NO CYCLE | NOCYCLE
| CACHE long | NO CACHE | NOCACHE
START
RESTART
WITH long
INCREMENT BY long
MINVALUE long
NO MINVALUE
NOMINVALUE
MAXVALUE long
NO MAXVALUE
NOMAXVALUE
CYCLE
NO CYCLE
NOCYCLE
CACHE long
NO CACHE
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

{ { columnName = { DEFAULT | expression } }
| { ( columnName [,...] ) = { rowValueExpression | (query) } } } [,...]
columnName =
DEFAULT
expression
( columnName
 
, ...
) =
rowValueExpression
( query )
 
, ...

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

factor [ { { + | - } factor } [...] ]
factor
 
+
-
factor
 
...

A value or a numeric sum.

Please note the text concatenation operator is ||.

Example:

ID + 20

Table Expression

{ [ schemaName. ] tableName
| ( query )
| unnest
| table
| dataChangeDeltaTable }
[ [ AS ] newTableAlias [ ( columnName [,...] ) ] ]
[ USE INDEX ([ indexName [,...] ]) ]
[ { { LEFT | RIGHT } [ OUTER ] | [ INNER ] | CROSS | NATURAL }
JOIN tableExpression [ joinSpecification ] ]
 
schemaName .
tableName
( query )
unnest
table
dataChangeDeltaTable

 
 
AS
newTableAlias
 
( columnName
 
, ...
)

 
USE INDEX (
 
indexName
 
, ...
)

 
LEFT
RIGHT
 
OUTER
 
INNER
CROSS
NATURAL
JOIN tableExpression
 
joinSpecification

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 [ASC|DESC]} [,...])
WITHIN GROUP ( ORDER BY expression
 
ASC
DESC
 
, ...
)

Group specification for ordered set functions.

Example:

WITHIN GROUP (ORDER BY ID DESC)

Wildcard expression

{* | tableAlias.*} [EXCEPT ([tableAlias.]columnName, [,...])]
*
tableAlias . *
 
EXCEPT (
 
tableAlias .
columnName ,
 
, ...
)

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

windowName | windowSpecification
windowName
windowSpecification

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

([existingWindowName]
[PARTITION BY expression [,...]] [ORDER BY order [,...]]
[windowFrame])
(
 
existingWindowName

 
PARTITION BY expression
 
, ...
 
ORDER BY order
 
, ...

 
windowFrame
)

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|BETWEEN windowFrameBound AND windowFrameBound}
[EXCLUDE {CURRENT ROW|GROUP|TIES|NO OTHERS}]
ROWS
RANGE
GROUP

windowFramePreceding
BETWEEN windowFrameBound AND windowFrameBound

 
EXCLUDE
CURRENT ROW
GROUP
TIES
NO OTHERS

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

UNBOUNDED PRECEDING|value PRECEDING|CURRENT ROW
UNBOUNDED PRECEDING
value PRECEDING
CURRENT ROW

A window frame preceding clause. If value is specified it should not be negative.

Example:

UNBOUNDED PRECEDING
1 PRECEDING
CURRENT ROW

Window frame bound

UNBOUNDED PRECEDING|value PRECEDING|CURRENT ROW
|value FOLLOWING|UNBOUNDED FOLLOWING
UNBOUNDED PRECEDING
value PRECEDING
CURRENT ROW
value FOLLOWING
UNBOUNDED FOLLOWING

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
| ?[ int ]
| sequenceValueExpression
| function
| { - | + } term
| ( expression )
| query
| case
| caseWhen
| userDefinedFunctionName }
[ timeZone ]
value
column
?
 
int
sequenceValueExpression
Function
-
+
term
( expression )
query
case
caseWhen
userDefinedFunctionName

 
timeZone

A value. Parameters can be indexed, for example ?1 meaning the first parameter.

Example:

'Hello'

Time zone

AT { TIME ZONE { intervalHourToMinute | intervalHourToSecond | string } | LOCAL }
AT
TIME ZONE
intervalHourToMinute
intervalHourToSecond
string
LOCAL

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

[[schemaName.]tableAlias.] { columnName | _ROWID_ }
 
 
schemaName .
tableAlias .
columnName
_ROWID_

A column name with optional table alias and schema. _ROWID_ can be used to access unique row identifier.

Example:

ID