SQL Grammar

Index

Literals

Datetime fields

Other Grammar

Details

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

Non-standard syntax is marked in green. Compatibility-only non-standard syntax is marked in red,don't use it unless you need it for compatibility with other databases or old versions of H2.

Literals

Value

string |{dollarQuotedString } |numeric |dateAndTime |boolean |bytes|interval |array |{geometry |json |uuid } |null

A literal value of any data type, or null.

Example:

10

Approximate numeric

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

An approximate numeric value. Approximate numeric values haveDECFLOAT data type. To define aDOUBLE PRECISION value, useCAST(X AS DOUBLE PRECISION). To define aREAL value, useCAST(X AS REAL). There are some specialREAL, DOUBLE PRECISION, andDECFLOAT values: to represent positive infinity, useCAST('Infinity' AS dataType); for negative infinity, useCAST('-Infinity' AS dataType); forNaN (not a number), useCAST('NaN' AS dataType).

Example:

-1.4e-10
1.111_111E3
CAST(1e2 AS REAL)
CAST('NaN' AS DOUBLE PRECISION)

Array

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

An array of values.

Example:

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

Boolean

TRUE | FALSE | UNKNOWN

A boolean value.UNKNOWN is aNULL value with the boolean data type.

Example:

TRUE

Bytes

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

A binary string value. The hex value is not case sensitive and may contain space characters as separators. If there are more than one group of quoted hex values, groups must be separated with whitespace.

Example:

X''
X'01FF'
X'01 bc 2a'
X'01' '02'

Date

DATE '[-]yyyy-MM-dd'

A date literal.

Example:

DATE '2004-12-31'

Date and time

date |time |timeWithTimeZone |timestamp |timestampWithTimeZone

A literal value of any date-time data type.

Example:

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

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$$

Exact numeric

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

An exact numeric value. Exact numeric values with dot haveNUMERIC data type, values without dot small enough to fit intoINTEGER data type have this type, larger values small enough to fit intoBIGINT data type have this type, others also haveNUMERIC data type.

Example:

-1600.05
134_518.235_114

Hex Number

[+|-] {0x|0X} { [_] {digit | a-f | A-F } [...] } [...]

A number written in hexadecimal notation.

Example:

0xff
0x_ABCD_1234

Octal Number

[+|-] {0o|0O} { [_] { 0-7 } [...] } [...]

A number written in octal notation.

Example:

0o664
0o_123_777

Binary Number

[+|-] {0b|0B} { [_] { 0-1 } [...] } [...]

A number written in binary notation.

Example:

0b101
0b_01010101_10101010

Int

[ + | - ]number

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

Example:

10
65_536

GEOMETRY

GEOMETRY {bytes |string }

A binary string or character string withGEOMETRY object.

A binary string should contain Well-known Binary Representation (WKB) fromOGC 06-103r4. Dimension system marks may be specified either in bothOGC WKB or in PostGIS EWKB formats. OptionalSRID fromEWKB may be specified.POINT EMPTY stored with NaN values as specified inOGC 12-128r15 is supported.

A character string should contain Well-known Text Representation (WKT) fromOGC 06-103r4 with optionalSRID from PostGIS EWKT extension.

Example:

GEOMETRY 'GEOMETRYCOLLECTION (POINT (1 2))'
GEOMETRY X'00000000013ff00000000000003ff0000000000000'

JSON

JSON {bytes |string }

A binary or character string with aRFC 8259-compliantJSON 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
1_000_000_000

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
10_000

Numeric

exactNumeric |approximateNumeric |int |long |hexNumber |octalNumber |binaryNumber

The data type of a numeric literal is the one of numeric data types, such asNUMERIC, DECFLOAT, BIGINT, orINTEGER depending on format and value.

An explicitCAST can be used to change the data type.

Example:

-1600.05
CAST(0 AS DOUBLE PRECISION)
-1.4e-10
999_999_999.999_999

String

[N]'anything' [...]| U&{'anything' [...]} [ UESCAPE 'anything' ]

A character string literal starts and ends with a single quote. Two single quotes can be used to create a single quote inside a string. PrefixN means a national character string literal; H2 does not distinguish regular and national character string literals in any way, this prefix has no effect in H2.

String literals staring withU& are Unicode character string literals. All character string literals in H2 may have Unicode characters, but Unicode character string literals may contain Unicode escape sequences\0000 or\+000000, where \ is an escape character,0000 and000000 are Unicode character codes in hexadecimal notation. OptionalUESCAPE clause may be used to specify another escape character, with exception for single quote, double quote, plus sign, and hexadecimal digits (0-9, a-f, and A-F). By default the backslash is used. Two escape characters can be used to include a single character inside a string. Two single quotes can be used to create a single quote inside a string.

Example:

'John''s car'
'A' 'B' 'C'
U&'W\00f6rter ' '\\ \+01f600 /'
U&'|00a1' UESCAPE '|'

UUID

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

AUUID literal. Must contain 32 hexadecimal digits. Digits may be separated with - signs.

Example:

UUID '12345678-1234-1234-1234-123456789ABC'

Time

TIME [ WITHOUT TIME ZONE ] 'hh:mm:ss[.nnnnnnnnn]'

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}'

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]'

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 } ]'

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

An interval literal.

Example:

INTERVAL '1-2' YEAR TO MONTH

INTERVAL YEAR

INTERVAL [-|+] '[-|+]yearInt' YEAR [ (precisionInt ) ]

AnINTERVAL YEAR literal. If precision is specified it should be from 1 to 18.

Example:

INTERVAL '10' YEAR

INTERVAL MONTH

INTERVAL [-|+] '[-|+]monthInt' MONTH [ (precisionInt ) ]

AnINTERVAL MONTH literal. If precision is specified it should be from 1 to 18.

Example:

INTERVAL '10' MONTH

INTERVAL DAY

INTERVAL [-|+] '[-|+]dayInt' DAY [ (precisionInt ) ]

AnINTERVAL DAY literal. If precision is specified it should be from 1 to 18.

Example:

INTERVAL '10' DAY

INTERVAL HOUR

INTERVAL [-|+] '[-|+]hourInt' HOUR [ (precisionInt ) ]

AnINTERVAL HOUR literal. If precision is specified it should be from 1 to 18.

Example:

INTERVAL '10' HOUR

INTERVAL MINUTE

INTERVAL [-|+] '[-|+]minuteInt' MINUTE [ (precisionInt ) ]

AnINTERVAL MINUTE literal. If precision is specified it should be from 1 to 18.

Example:

INTERVAL '10' MINUTE

INTERVAL SECOND

INTERVAL [-|+] '[-|+]secondInt[.nnnnnnnnn]'SECOND [ (precisionInt [,fractionalPrecisionInt ] ) ]

AnINTERVAL SECOND literal. If precision is specified it should be from 1 to 18. If fractional seconds precision is specified it should be from 0 to 9.

Example:

INTERVAL '10.123' SECOND

INTERVAL YEAR TO MONTH

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

AnINTERVAL YEAR TO MONTH literal. If leading field precision is specified it should be from 1 to 18.

Example:

INTERVAL '1-6' YEAR TO MONTH

INTERVAL DAY TO HOUR

INTERVAL [-|+] '[-|+]dayInthoursInt' DAY [ (precisionInt ) ] TO HOUR

AnINTERVAL DAY TO HOUR literal. If leading field precision is specified it should be from 1 to 18.

Example:

INTERVAL '10 11' DAY TO HOUR

INTERVAL DAY TO MINUTE

INTERVAL [-|+] '[-|+]dayInt hh:mm' DAY [ (precisionInt ) ] TO MINUTE

AnINTERVAL DAY TO MINUTE literal. If leading field precision is specified it should be from 1 to 18.

Example:

INTERVAL '10 11:12' DAY TO MINUTE

INTERVAL DAY TO SECOND

INTERVAL [-|+] '[-|+]dayInt hh:mm:ss[.nnnnnnnnn]' DAY [ (precisionInt ) ]TO SECOND [ (fractionalPrecisionInt ) ]

AnINTERVAL DAY TO SECOND literal. If leading field precision is specified it should be from 1 to 18. If fractional seconds precision is specified it should be from 0 to 9.

Example:

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

INTERVAL HOUR TO MINUTE

INTERVAL [-|+] '[-|+]hh:mm' HOUR [ (precisionInt ) ] TO MINUTE

AnINTERVAL HOUR TO MINUTE literal. If leading field precision is specified it should be from 1 to 18.

Example:

INTERVAL '10:11' HOUR TO MINUTE

INTERVAL HOUR TO SECOND

INTERVAL [-|+] '[-|+]hh:mm:ss[.nnnnnnnnn]' HOUR [ (precisionInt ) ]TO SECOND [ (fractionalPrecisionInt ) ]

AnINTERVAL HOUR TO SECOND literal. If leading field precision is specified it should be from 1 to 18. If fractional seconds precision is specified it should be from 0 to 9.

Example:

INTERVAL '10:11:12.123' HOUR TO SECOND

INTERVAL MINUTE TO SECOND

INTERVAL [-|+] '[-|+]mm:ss[.nnnnnnnnn]' MINUTE [ (precisionInt ) ]TO SECOND [ (fractionalPrecisionInt ) ]

AnINTERVAL MINUTE TO SECOND literal. If leading field precision is specified it should be from 1 to 18. If fractional seconds precision is specified it should be from 0 to 9.

Example:

INTERVAL '11:12.123' MINUTE TO SECOND

Datetime fields

Datetime field

yearField |monthField |dayOfMonthField|hourField |minuteField |secondField|timezoneHourField |timezoneMinuteField|{timezoneSecondField|millenniumField |centuryField |decadeField|quarterField|millisecondField |microsecondField |nanosecondField|dayOfYearField|isoDayOfWeekField |isoWeekField |isoWeekYearField|dayOfWeekField |weekField |weekYearField|epochField }

Fields forEXTRACT, DATEADD, DATEDIFF, andDATE_TRUNC 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

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

Millennium field

MILLENNIUM

Century, or one thousand years (2001-01-01 to 3000-12-31).

Example:

MILLENNIUM

Century field

CENTURY

Century, or one hundred years (2001-01-01 to 2100-12-31).

Example:

CENTURY

Decade field

DECADE

Decade, or ten years (2020-01-01 to 2029-12-31).

Example:

DECADE

Quarter field

QUARTER

Quarter (1-4).

Example:

QUARTER

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

Day of year field

{ DAYOFYEAR | DAY_OF_YEAR } |{ DOY | DY }

Day of year (1-366).

Example:

DAYOFYEAR

ISO day of week field

{ ISO_DAY_OF_WEEK } |{ ISODOW }

ISO day of week (1-7). Monday is 1.

Example:

ISO_DAY_OF_WEEK

ISO week 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

ISO week year field

{ ISO_WEEK_YEAR } |{ ISO_YEAR | ISOYEAR }

Returns theISO week-based year from a date/time value.

Example:

ISO_WEEK_YEAR

Day of week field

{ DAY_OF_WEEK | DAYOFWEEK } |{ DOW }

Day of week (1-7), locale-specific.

Example:

DAY_OF_WEEK

Week field

{ WEEK } |{ WW | W | SQL_TSI_WEEK }

Week of year (1-53) using local rules.

Example:

WEEK

Week year field

{ WEEK_YEAR }

Returns the week-based year (locale-specific) from a date/time value.

Example:

WEEK_YEAR

Epoch field

EPOCH

ForTIMESTAMP values number of seconds since 1970-01-01 00:00:00 in local time zone. ForTIMESTAMP WITH TIME ZONE values number of seconds since 1970-01-01 00:00:00 inUTC time zone. ForDATE values number of seconds since 1970-01-01. ForTIME values number of seconds since midnight.

Example:

EPOCH

Other Grammar

Alias

name

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

Example:

A

And Condition

condition [ { ANDcondition } [...] ]

Value or condition.

Example:

ID=1 AND NAME='Hi'

Array element reference

{array |json } '['indexInt ']'

Returns array element at specified 1-based index. ReturnsNULL if array or json is null, index is null, or element with specified index isn't found inJSON.

Example:

A[2]
M[5][8]

Field reference

(expression).fieldName

Returns field value from the row value orJSON value. ReturnsNULL if value is null or field with specified name isn't found inJSON. Expression on the left must be enclosed in parentheses if it is an identifier (column name), in other cases they aren't required.

Example:

(R).FIELD1
(TABLE1.COLUMN2).FIELD.SUBFIELD
JSON '{"a": 1, "b": 2}'."b"

Array value constructor by query

ARRAY (query)

Collects values from the subquery into array.

The subquery should have exactly one column. Number of elements in the returned array is the number of rows in the subquery.NULL values are included into array.

Example:

ARRAY(SELECT * FROM SYSTEM_RANGE(1, 10));

Case expression

simpleCase |searchedCase

Performs conditional evaluation of expressions.

Example:

CASE A WHEN 'a' THEN 1 ELSE 2 END
CASE WHEN V > 10 THEN 1 WHEN V < 0 THEN 2 END
CASE WHEN A IS NULL THEN 'Null' ELSE 'Not null' END

Simple case

CASEexpression{ WHEN {expression |conditionRightHandSide } [,...] THENexpression } [...][ ELSEexpression ] END

Returns then expression from the first when clause where one of its operands was was evaluated toTRUE for the case expression. If there are no such clauses, returns else expression orNULL if it is absent.

Plain expressions are tested for equality with the case expression,NULL is not equal toNULL. Right sides of conditions are evaluated with the case expression on the left side.

Example:

CASE CNT WHEN IS NULL THEN 'Null' WHEN 0 THEN 'No' WHEN 1 THEN 'One' WHEN 2, 3 THEN 'Few' ELSE 'Some' END

Searched case

CASE { WHENexpression THENexpression } [...][ ELSEexpression ] END

Returns the first expression where the condition is true. If no else part is specified, returnNULL.

Example:

CASE WHEN CNT<10 THEN 'Low' ELSE 'High' END
CASE WHEN A IS NULL THEN 'Null' ELSE 'Not null' END

Cast specification

CAST(value ASdataTypeOrDomain [ FORMATtemplateString ])

Converts a value to another data type. The following conversion rules are used: When converting a number to a boolean, 0 is false and every other value is true. When converting a boolean to a number, false is 0 and true is 1. When converting a number to a number of another type, the value is checked for overflow. When converting a string to binary,UTF-8 encoding is used. Note that some data types may need explicitly specified precision to avoid overflow or rounding.

Template may only be specified for casts from datetime data types to character string data types and for casts from character string data types to datetime data types.

'-', '.', '/', ',', '''', ';', ':' and ' ' (space) characters can be used as delimiters.

Y,YY, YYY, YYYY represent last 1, 2, 3, or 4 digits of year.YYYY, if delimited, can also be used to parse any year, including negative years. When a year is parsed with Y,YY, orYYY pattern missing leading digits are filled using digits from the current year.

RR andRRRR have the same meaning asYY andYYYY for formatting. When a year is parsed withRR, the resulting year is within current year - 49 years and current year + 50 years in H2, other database systems may use different range of years.

MM represent a month.

DD represent a day of month.

DDD represent a day of year, if this pattern in specified,MM andDD may not be specified.

HH24 represent an hour (from 0 to 23).

HH andHH12 represent an hour (from 1 to 12), this pattern may only be used together with A.M. or P.M. pattern. These patterns may not be used together withHH24.

MI represent minutes.

SS represent seconds of minute.

SSSSS represent seconds of day, this pattern may not be used together withHH24, HH (HH12), A.M. (P.M.),MI orSS pattern.

FF1, FF2, ...,FF9 represent fractional seconds.

TZH, TZM andTZH represent hours, minutes and seconds of time zone offset.

Multiple patterns for the same datetime field may not be specified.

If year is not specified, current year is used. If month is not specified, current month is used. If day is not specified, 1 is used.

If some fields of time or time zone are not specified, 0 is used.

Example:

CAST(NAME AS INT);
CAST(TIMESTAMP '2010-01-01 10:40:00.123456' AS TIME(6));
CAST('12:00:00 P.M.' AS TIME FORMAT 'HH:MI:SS A.M.');

Cipher

AES

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

Example:

AES

Column Definition

dataTypeOrDomain[ VISIBLE | INVISIBLE ][ { DEFAULTexpression| GENERATED ALWAYS AS (generatedColumnExpression)| GENERATED {ALWAYS | BY DEFAULT} AS IDENTITY [(sequenceOption [...])]} ][ ON UPDATEexpression ][ DEFAULT ON NULL ][ SELECTIVITYselectivityInt ][ COMMENTexpression ][columnConstraintDefinition ] [...]

The default expression is used if no explicit value was used when adding a row and whenDEFAULT value was specified in an update command.

A column is either a generated column or a base column. The generated column has a generated column expression. The generated column expression is evaluated and assigned whenever the row changes. This expression may reference base columns of the table, but may not reference other data. The value of the generated column cannot be set explicitly. Generated columns may not haveDEFAULT orON UPDATE expressions.

On update column expression is used if row is updated, at least one column has a new value that is different from its previous value and value for this column is not set explicitly in update statement.

Identity column is a column generated with a sequence. The column declared as the identity column withIDENTITY data type or withIDENTITY () clause is implicitly the primary key column of this table.GENERATED ALWAYS AS IDENTITY, GENERATED BY DEFAULT AS IDENTITY, andAUTO_INCREMENT clauses do not create the primary key constraint automatically.GENERATED ALWAYS AS IDENTITY clause indicates that column can only be generated by the sequence, its value cannot be set explicitly. Identity column has implicitNOT NULL constraint. Identity column may not haveDEFAULT orON UPDATE expressions.

DEFAULT ON NULL makesNULL value work asDEFAULT value is assignments to this column.

The invisible column will not be displayed as a result ofSELECT * query. Otherwise, it works as normal column.

Column constraint definitions are not supported forALTER statements.

Example:

CREATE TABLE TEST(ID INT PRIMARY KEY,
    NAME VARCHAR(255) DEFAULT '' NOT NULL);
CREATE TABLE TEST(ID BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    QUANTITY INT, PRICE NUMERIC(10, 2),
    AMOUNT NUMERIC(20, 2) GENERATED ALWAYS AS (QUANTITY * PRICE));

Column Constraint Definition

[constraintNameDefinition ]NOT NULL | PRIMARY KEY | UNIQUE [nullsDistinct ] |referencesSpecification | CHECK (condition)

NOT NULL disallowsNULL value for a column.

PRIMARY KEY andUNIQUE require unique values.PRIMARY KEY also disallowsNULL values and marks the column as a primary key.UNIQUE constraint allowsNULL values, if nulls distinct clause is not specified, the default isNULLS DISTINCT, excluding some compatibility modes.

Referential constraint requires values that exist in other column (usually in another table).

Check constraint require a specified condition to returnTRUE orUNKNOWN (NULL). It can reference columns of the table, and 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:

NOT NULL
PRIMARY KEY
UNIQUE
REFERENCES T2(ID)
CHECK (VALUE > 0)

Comment

bracketedComment | -- anything |// anything

Comments can be used anywhere in a command and are ignored by the database. Line comments-- and// end with a newline.

Example:

-- comment
/* comment */

Bracketed comment

/* [ [bracketedComment ] [ anything ] [...] ] */

Comments can be used anywhere in a command and are ignored by the database. Bracketed comments/* */ can be nested and can be multiple lines long.

Example:

/* comment */
/* comment /* nested comment */ 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 ]| NOTcondition| EXISTS (query )| UNIQUE [nullsDistinct ] (query )|INTERSECTS (operand,operand)

Boolean value or condition.

NOT condition negates the result of subcondition and returnsTRUE, FALSE, orUNKNOWN (NULL).

EXISTS predicate tests whether the result of the specified subquery is not empty and returnsTRUE orFALSE.

UNIQUE predicate tests absence of duplicate rows in the specified subquery and returnsTRUE orFALSE. If nulls distinct clause is not specified,NULLS DISTINCT is implicit.

INTERSECTS checks whether 2D bounding boxes of specified geometries intersect with each other and returnsTRUE orFALSE.

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

comparisonRightHandSide|quantifiedComparisonRightHandSide|nullPredicateRightHandSide|distinctPredicateRightHandSide|quantifiedDistinctPredicateRightHandSide|booleanTestRightHandSide|typePredicateRightHandSide|jsonPredicateRightHandSide|betweenPredicateRightHandSide|inPredicateRightHandSide|likePredicateRightHandSide|regexpPredicateRightHandSide

The right hand side of a condition.

Example:

> 10
IS NULL
IS NOT NULL
IS NOT DISTINCT FROM B
IS OF (DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE)
IS JSON OBJECT WITH UNIQUE KEYS
LIKE 'Jo%'

Comparison Right Hand Side

compareoperand

Right side of comparison predicates.

Example:

> 10

Quantified Comparison Right Hand Side

compare { ALL | ANY | SOME } ( {query |{array } } )

Right side of quantified comparison predicates.

Quantified comparison predicateALL returnsTRUE if specified comparison operation between left size of condition and each row from a subquery or each element of array returnsTRUE, including case when there are no rows (elements).ALL predicate returnsFALSE if at least one such comparison returnsFALSE. Otherwise it returnsUNKNOWN.

Quantified comparison predicatesANY andSOME returnTRUE if specified comparison operation between left size of condition and at least one row from a subquery or at least one element of array returnsTRUE.ANY andSOME predicates returnFALSE if all such comparisons returnFALSE. Otherwise they returnUNKNOWN.

Note that these predicates have priority overANY andSOME aggregate functions with subquery on the right side. Use parentheses around aggregate function.

If version with array is required and this array is returned from a subquery, wrap this subquery with a cast to distinguish this operation from standard quantified comparison predicate with a query.

Example:

< ALL(SELECT V FROM TEST)
= ANY(ARRAY_COLUMN)
= ANY(CAST((SELECT ARRAY_COLUMN FROM OTHER_TABLE WHERE ID = 5) AS INTEGER ARRAY)

Null Predicate Right Hand Side

IS [ NOT ] NULL

Right side of null predicate.

Check whether the specified value(s) areNULL values. To test multiple values a row value must be specified.IS NULL returnsTRUE if and only if all values areNULL values; otherwise it returnsFALSE.IS NOT NULL returnsTRUE if and only if all values are notNULL values; otherwise it returnsFALSE.

Example:

IS NULL

Distinct Predicate Right Hand Side

IS [ NOT ] [ DISTINCT FROM ]operand

Right side of distinct predicate.

Distinct predicate is null-safe, meaningNULL is considered the same asNULL, and the condition never evaluates toUNKNOWN.

Example:

IS NOT DISTINCT FROM OTHER

Quantified Distinct Predicate Right Hand Side

IS [ NOT ] [ DISTINCT FROM ] { ALL | ANY | SOME } ( {query |array } )

Right side of quantified distinct predicate.

Quantified distinct predicate is null-safe, meaningNULL is considered the same asNULL, and the condition never evaluates toUNKNOWN.

Quantified distinct predicateALL returnsTRUE if specified distinct predicate between left size of condition and each row from a subquery or each element of array returnsTRUE, including case when there are no rows. Otherwise it returnsFALSE.

Quantified distinct predicatesANY andSOME returnTRUE if specified distinct predicate between left size of condition and at least one row from a subquery or at least one element of array returnsTRUE. Otherwise they returnFALSE.

Note that these predicates have priority overANY andSOME aggregate functions with subquery on the right side. Use parentheses around aggregate function.

If version with array is required and this array is returned from a subquery, wrap this subquery with a cast to distinguish this operation from quantified comparison predicate with a query.

Example:

IS DISTINCT FROM ALL(SELECT V FROM TEST)
IS NOT DISTINCT FROM ANY(ARRAY_COLUMN)
IS NOT DISTINCT FROM ANY(CAST((SELECT ARRAY_COLUMN FROM OTHER_TABLE WHERE ID = 5) AS INTEGER ARRAY)

Boolean Test Right Hand Side

IS [ NOT ] { TRUE | FALSE | UNKNOWN }

Right side of boolean test.

Checks whether the specified value is (not)TRUE, FALSE, orUNKNOWN (NULL) and returnTRUE orFALSE. This test is null-safe.

Example:

IS TRUE

Type Predicate Right Hand Side

IS [ NOT ] OF (dataType [,...])

Right side of type predicate.

Checks 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 isNULL, the result isUNKNOWN.

Example:

IS OF (INTEGER, BIGINT)

JSON Predicate Right Hand Side

IS [ NOT ] JSON [ VALUE | ARRAY | OBJECT | SCALAR ]    [ [ WITH | WITHOUT ] UNIQUE [ KEYS ] ]

Right side ofJSON predicate.

Checks whether value of the specified string, binary data, or aJSON is a validJSON. IfARRAY, OBJECT, orSCALAR is specified, onlyJSON items of the specified type are considered as valid. IfWITH UNIQUE [ KEYS ] is specified onlyJSON with unique keys is considered as valid. This predicate isn't null-safe, it returnsUNKNOWN if operand isNULL.

Example:

IS JSON OBJECT WITH UNIQUE KEYS

Between Predicate Right Hand Side

[ NOT ] BETWEEN [ ASYMMETRIC | SYMMETRIC ]operand ANDoperand

Right side of between predicate.

Checks whether the value is within the range inclusive.V BETWEEN [ ASYMMETRIC ] A AND B is equivalent toA <= V AND V <= B.V BETWEEN SYMMETRIC A AND B is equivalent toA <= V AND V <= B OR A >= V AND V >= B.

Example:

BETWEEN LOW AND HIGH

In Predicate Right Hand Side

[ NOT ] IN ( {query |expression [,...] } )

Right side of in predicate.

Checks presence of value in the specified list of values or in result of the specified query.

ReturnsTRUE if row value on the left side is equal to one of values on the right side,FALSE if all comparison operations were evaluated toFALSE or right side has no values, andUNKNOWN otherwise.

This operation is logically equivalent toOR between comparison operations comparing left side and each value from the right side.

Example:

IN (A, B, C)
IN (SELECT V FROM TEST)

Like Predicate Right Hand Side

[ NOT ] { LIKE |{ ILIKE } }operand [ ESCAPEstring ]

Right side of like predicate.

The wildcards characters are_ (any one character) and% (any characters). The database uses an index when comparing withLIKE 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, useESCAPE '' (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 returnsNULL.

ILIKE does a case-insensitive compare.

Example:

LIKE 'a%'

Regexp Predicate Right Hand Side

{ [ NOT ] REGEXPoperand }

Right side of Regexp predicate.

Regular expression matching is used. See JavaMatcher.find for details.

Example:

REGEXP '[a-z]'

Nulls Distinct

NULLS { DISTINCT | NOT DISTINCT |{ ALL DISTINCT } }

Are nulls distinct for unique constraint, index, or predicate.

IfNULLS DISTINCT is specified, rows with null value in any column are distinct. IfNULLS ALL DISTINCT is specified, rows with null value in all columns are distinct. IfNULLS NOT DISTINCT is specified, null values are identical.

Treatment of null values inside composite data types is not affected.

Example:

NULLS DISTINCT
NULLS NOT DISTINCT

Table Constraint Definition

[constraintNameDefinition ]{ PRIMARY KEY[ HASH ] (columnName [,...] ) }| UNIQUE [nullsDistinct ] ( {columnName [,...] | VALUE } )|referentialConstraint| CHECK (condition)

Defines a constraint.

PRIMARY KEY andUNIQUE require unique values.PRIMARY KEY also disallowsNULL values and marks the column as a primary key, a table can have only one primary key.UNIQUE constraint supportsNULL values and rows withNULL value in any column are considered as unique.UNIQUE constraint allowsNULL values, if nulls distinct clause is not specified, the default isNULLS DISTINCT, excluding some compatibility modes.UNIQUE (VALUE) creates a unique constraint on entire row, excluding invisible columns; but if new columns will be added to the table, they will not be included into this constraint.

Referential constraint requires values that exist in other column(s) (usually in another table).

Check constraint requires a specified condition to returnTRUE orUNKNOWN (NULL). It can reference columns of the table, and 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:

PRIMARY KEY(ID, NAME)

Constraint Name Definition

CONSTRAINT[ IF NOT EXISTS ]newConstraintName

Defines a constraint name.

Example:

CONSTRAINT CONST_ID

Csv Options

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

Optional parameters forCSVREAD andCSVWRITE. 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 existingCSV files that contain explicitnull delimiters. Note that an empty, unquoted values are also treated as null.

quotedNulls (quotes the nullString. true of false; disabled by default),

preserveWhitespace (true or false; disabled by default),

writeColumnHeader (true or false; enabled by default).

For a newline or other special character, useSTRINGDECODE 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 } )

Executes the inner data change command and returns old, new, or final rows.

OLD is not allowed forINSERT command. It returns old rows.

NEW andFINAL are not allowed forDELETE command.

NEW returns new rows after evaluation of default expressions, but before execution of triggers.

FINAL returns new rows after execution of triggers.

Example:

SELECT ID FROM FINAL TABLE (INSERT INTO TEST (A, B) VALUES (1, 2))

Data Type or Domain

dataType | [schemaName.]domainName

A data type or domain name.

Example:

INTEGER
MY_DOMAIN

Data Type

predefinedType |arrayType |rowType

A data type.

Example:

INTEGER

Predefined Type

characterType |characterVaryingType |characterLargeObjectType|binaryType |binaryVaryingType |binaryLargeObjectType|booleanType|smallintType |integerType |bigintType|numericType |realType |doublePrecisionType |decfloatType|dateType |timeType |timeWithTimeZoneType|timestampType |timestampWithTimeZoneType|intervalType|{tinyintType |javaObjectType |enumType|geometryType |jsonType |uuidType }

A predefined data type.

Example:

INTEGER

Digit

0-9

A digit.

Example:

0

Expression

andCondition [ { ORandCondition } [...] ]

Value or condition.

Example:

ID=1 OR NAME='Hi'

Factor

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

A value or a numeric factor.

Example:

ID * 10

Grouping element

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

A grouping element ofGROUP BY clause.

Example:

A
(B, C)
()

Hex

[' ' [...]] { {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 } ]

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 values

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

Values forINSERT statement.

Example:

VALUES (1, 'Test')

Interval qualifier

YEAR [(precisionInt)] [ TO MONTH ]| MONTH [(precisionInt)]| DAY [(precisionInt)] [ TO { HOUR | MINUTE | SECOND [(scaleInt)] } ]| HOUR [(precisionInt)] [ TO { MINUTE | SECOND [(scaleInt)] } ]| MINUTE [(precisionInt)] [ TO SECOND [(scaleInt)] ]| SECOND [(precisionInt [,scaleInt])]

An interval qualifier.

Example:

DAY TO SECOND

Join specification

ONexpression | USING (columnName [,...])

Specifies a join condition or column names.

Example:

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

Merge when clause

mergeWhenMatchedClause|mergeWhenNotMatchedClause

WHEN MATCHED orWHEN NOT MATCHED clause forMERGE USING command.

Example:

WHEN MATCHED THEN DELETE

Merge when matched clause

WHEN MATCHED [ ANDexpression ] THENUPDATE SETsetClauseList | DELETE

WHEN MATCHED clause forMERGE USING command.

Updates or deletes rows in a target table.

Example:

WHEN MATCHED THEN UPDATE SET NAME = S.NAME
WHEN MATCHED THEN DELETE

Merge when not matched clause

WHEN NOT MATCHED [ ANDexpression ] THEN INSERT[ (columnName [,...] ) ][overrideClause ]VALUES ({DEFAULT|expression} [,...])

WHEN NOT MATCHED clause forMERGE USING command.

Inserts rows into a target table.

If column names aren't specified a list of all visible columns in the target table is assumed.

Example:

WHEN NOT MATCHED THEN INSERT (ID, NAME) VALUES (S.ID, S.NAME)

Name

{ { A-Z|_ } [ { A-Z|_|0-9 } [...] ] } |quotedName

With default settings unquoted names are converted to upper case. The maximum name length is 256 characters.

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.

IfDATABASE_TO_UPPER setting is set toFALSE the unquoted names aren't converted to upper case.

IfDATABASE_TO_LOWER setting is set toTRUE the unquoted names are converted to lower case instead.

IfCASE_INSENSITIVE_IDENTIFIERS setting is set toTRUE all identifiers are case insensitive.

Example:

TEST

Operand

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

Performs the concatenation of character string, binary string, or array values. In the default mode, the result isNULL if either parameter isNULL. In compatibility modes result of string concatenation withNULL 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]

Override clause

OVERRIDING { USER | SYSTEM } VALUE

IfOVERRIDING USER VALUE is specified,INSERT statement ignores the provided value for identity column and generates a new one instead.

IfOVERRIDING SYSTEM VALUE is specified,INSERT statement assigns the provided value to identity column.

If neither clauses are specified,INSERT statement assigns the provided value toGENERATED BY DEFAULT AS IDENTITY column, but throws an exception if value is specified forGENERATED ALWAYS AS IDENTITY column.

Example:

OVERRIDING SYSTEM VALUE
OVERRIDING USER VALUE

Query

select |explicitTable |tableValue

A query, such asSELECT, explicit table, or table value.

Example:

SELECT ID FROM TEST;
TABLE TEST;
VALUES (1, 2), (3, 4);

Quoted Name

"anything"| U&"anything" [ UESCAPE 'anything' ]

Case of characters in quoted names is preserved as is. Such names can contain spaces. The maximum name length is 256 characters. Two double quotes can be used to create a single double quote inside an identifier. With default settings identifiers in H2 are case sensitive.

Identifiers staring withU& are Unicode identifiers. All identifiers in H2 may have Unicode characters, but Unicode identifiers may contain Unicode escape sequences\0000 or\+000000, where \ is an escape character,0000 and000000 are Unicode character codes in hexadecimal notation. OptionalUESCAPE clause may be used to specify another escape character, with exception for single quote, double quote, plus sign, and hexadecimal digits (0-9, a-f, and A-F). By default the backslash is used. Two escape characters can be used to include a single character inside an Unicode identifier. Two double quotes can be used to create a single double quote inside an Unicode identifier.

Example:

"FirstName"
U&"\00d6ffnungszeit"
U&"/00d6ffnungszeit" UESCAPE '/'

Referential Constraint

FOREIGN KEY (columnName [,...] )referencesSpecification

Defines a referential constraint.

Example:

FOREIGN KEY(ID) REFERENCES TEST(ID)

References Specification

REFERENCES [refTableName ] [ (refColumnName [,...] ) ][ ON DELETEreferentialAction ] [ ON UPDATEreferentialAction ]

Defines a referential specification of 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. Referential constraint requires an existing unique or primary key constraint on referenced columns, this constraint must include all referenced columns in any order and must not include any other columns. Some tables may not be referenced, such as metadata tables.

Example:

REFERENCES TEST(ID)

Referential Action

CASCADE | RESTRICT | NO ACTION | SET { DEFAULT | NULL }

The actionCASCADE 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 andNO ACTION will both throw an exception if the constraint is violated. The actionSET DEFAULT will set the column in the referencing (child) table to the default value, whileSET NULL will set it toNULL.

Example:

CASCADE
SET NULL

Script Compression Encryption

[ COMPRESSION { DEFLATE | LZF | ZIP | GZIP } ][ CIPHERcipher PASSWORDstring ]

The compression and encryption algorithm to use for script files. When using encryption, onlyDEFLATE andLZF are supported.LZF is faster but uses more space.

Example:

COMPRESSION LZF

Select order

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