▲
Search:
| Highlight keyword(s)
Home Download Cheat Sheet Documentation Quickstart Installation Tutorial Features Security Performance Advanced Reference Commands Functions • Aggregate • Window Data Types SQL Grammar System Tables Javadoc PDF (2 MB) Support FAQ Error Analyzer Google Group Appendix History License Build Links MVStore Architecture Migration to 2.0 |  | # SQL Grammar ## Index ### Literals
Value Approximate numeric Array Boolean Bytes Date Date and time Dollar Quoted String Exact numeric Hex Number Octal Number Binary Number Int | GEOMETRY JSON Long Null Number Numeric String UUID 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 Timezone hour field Timezone minute field | Timezone second field Millennium field Century field Decade field Quarter field Millisecond field Microsecond field Nanosecond field Day of year field | ISO day of week field ISO week field ISO week year field Day of week field Week field Week year field Epoch field
### Other Grammar
Alias And Condition Array element reference Field reference Array value constructor by query Case expression Simple case Searched case Cast specification Cipher Column Definition Column Constraint Definition Comment Bracketed comment Compare Condition Condition Right Hand Side Comparison Right Hand Side Quantified Comparison Right Hand Side Null Predicate Right Hand Side Distinct Predicate Right Hand Side Quantified Distinct Predicate Right Hand Side Boolean Test Right Hand Side Type Predicate Right Hand Side JSON Predicate Right Hand Side Between Predicate Right Hand Side In Predicate Right Hand Side Like Predicate Right Hand Side | Regexp Predicate Right Hand Side Nulls Distinct Table Constraint Definition Constraint Name Definition Csv Options Data Change Delta Table Data Type or Domain Data Type Predefined Type Digit Expression Factor Grouping element Hex Index Column Insert values Interval qualifier Join specification Merge when clause Merge when matched clause Merge when not matched clause Name Operand Override clause Query Quoted Name Referential Constraint References Specification | Referential Action Script Compression Encryption Select order Row value expression Select Expression Sequence value expression Sequence option Alter sequence option Alter identity column option Basic sequence option Set clause list Sort specification Sort specification list Summand Table Expression Update target 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 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
| `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
| `+` |
| `-` |
`number` |
`.` |
| `number` |
`. number`
`E` |
| `+` |
| `-` |
| `expNumber`
An approximate numeric value. Approximate numeric values have `DECFLOAT` data type. To define a `DOUBLE PRECISION` value, use `CAST(X AS DOUBLE PRECISION)`. To define a `REAL` value, use `CAST(X AS REAL)`. There are some special `REAL, DOUBLE PRECISION`, and `DECFLOAT` values: to represent positive infinity, use `CAST('Infinity' AS dataType)`; for negative infinity, use `CAST('-Infinity' AS dataType)`; for `NaN` (not a number), use `CAST('NaN' AS dataType)`. Example: -1.4e-10 1.111_111E3 CAST(1e2 AS REAL) CAST('NaN' AS DOUBLE PRECISION) ### 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 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'
`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' ### 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$$ ### Exact numeric
    [ + | - ] { { number [ . number ] } | { . number } }
| `+` |
| `-` |
`number` |
`. number`
`. number`
An exact numeric value. Exact numeric values with dot have `NUMERIC` data type, values without dot small enough to fit into `INTEGER` data type have this type, larger values small enough to fit into `BIGINT` data type have this type, others also have `NUMERIC` data type. Example: -1600.05 134_518.235_114 ### Hex Number
    [+|-] {0x|0X} { [_] { digit | a-f | A-F } [...] } [...]
| `+` |
| `-` |
| `0x` |
| `0X` |
| `_` |
| `digit` |
`a - f`
| `A-F` |
| `...` |
| `...` |
A number written in hexadecimal notation. Example: 0xff 0x_ABCD_1234 ### Octal Number
    [+|-] {0o|0O} { [_] { 0-7 } [...] } [...]
| `+` |
| `-` |
| `0o` |
| `0O` |
| `_` |
| `0 - 7` |
| `...` |
| `...` |
A number written in octal notation. Example: 0o664 0o_123_777 ### Binary Number
    [+|-] {0b|0B} { [_] { 0-1 } [...] } [...]
| `+` |
| `-` |
| `0b` |
| `0B` |
| `_` |
| `0 - 1` |
| `...` |
| `...` |
A number written in binary notation. Example: 0b101 0b_01010101_10101010 ### Int
    [ + | - ] number
| `+` |
| `-` |
| `number`
The maximum integer number is 2147483647, the minimum is -2147483648. Example: 10 65_536 ### GEOMETRY
    GEOMETRY { bytes | string }
`GEOMETRY` |
| `bytes` |
| `string` |
A binary string or character string with `GEOMETRY` object. A binary string should contain Well-known Binary Representation (`WKB`) from `OGC` 06-103r4. Dimension system marks may be specified either in both `OGC WKB` or in Post`GIS EWKB` formats. Optional `SRID` from `EWKB` may be specified. `POINT EMPTY` stored with NaN values as specified in `OGC` 12-128r15 is supported. A character string should contain Well-known Text Representation (`WKT`) from `OGC` 06-103r4 with optional `SRID` from Post`GIS EWKT` extension. Example: GEOMETRY 'GEOMETRYCOLLECTION (POINT (1 2))' GEOMETRY X'00000000013ff00000000000003ff0000000000000' ### 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 1_000_000_000 ### Null
    NULL
`NULL` `NULL` is a value without data type and means 'unknown value'. Example: NULL ### Number
    digit [ [_] digit [...] ] [...]
`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
| `exactNumeric` |
| `approximateNumeric` |
| `int` |
| `long` |
| `hexNumber` |
| `octalNumber` |
| `binaryNumber` |
The data type of a numeric literal is the one of numeric data types, such as `NUMERIC, DECFLOAT, BIGINT`, or `INTEGER` depending on format and value. An explicit `CAST` 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' ]
| `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. Prefix `N` 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 with `U&` 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` and `000000` are Unicode character codes in hexadecimal notation. Optional `UESCAPE` 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 | - } [...]'
`UUID '` |
| `digit` |
`a - f`
| `A-F` |
| `-` |
| `...` |
| `'`
A `UUID` 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]'
`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 [ ( precisionInt ) ]
`INTERVAL` |
| `-` |
| `+` |
| `'` |
| `-` |
| `+` |
| `yearInt ' YEAR` |
`( precisionInt )`
An `INTERVAL YEAR` literal. If precision is specified it should be from 1 to 18. Example: INTERVAL '10' YEAR ### INTERVAL MONTH
    INTERVAL [-|+] '[-|+]monthInt' MONTH [ ( precisionInt ) ]
`INTERVAL` |
| `-` |
| `+` |
| `'` |
| `-` |
| `+` |
| `monthInt ' MONTH` |
`( precisionInt )`
An `INTERVAL MONTH` literal. If precision is specified it should be from 1 to 18. Example: INTERVAL '10' MONTH ### INTERVAL DAY
    INTERVAL [-|+] '[-|+]dayInt' DAY [ ( precisionInt ) ]
`INTERVAL` |
| `-` |
| `+` |
| `'` |
| `-` |
| `+` |
| `dayInt ' DAY` |
`( precisionInt )`
An `INTERVAL DAY` literal. If precision is specified it should be from 1 to 18. Example: INTERVAL '10' DAY ### INTERVAL HOUR
    INTERVAL [-|+] '[-|+]hourInt' HOUR [ ( precisionInt ) ]
`INTERVAL` |
| `-` |
| `+` |
| `'` |
| `-` |
| `+` |
| `hourInt ' HOUR` |
`( precisionInt )`
An `INTERVAL HOUR` literal. If precision is specified it should be from 1 to 18. Example: INTERVAL '10' HOUR ### INTERVAL MINUTE
    INTERVAL [-|+] '[-|+]minuteInt' MINUTE [ ( precisionInt ) ]
`INTERVAL` |
| `-` |
| `+` |
| `'` |
| `-` |
| `+` |
| `minuteInt ' MINUTE` |
`( precisionInt )`
An `INTERVAL 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 ] ) ]
`INTERVAL` |
| `-` |
| `+` |
| `'` |
| `-` |
| `+` |
| `secondInt` |
`. 000000000`
| `'`
`SECOND` |
`( precisionInt` |
`, fractionalPrecisionInt`
| `)`
An `INTERVAL 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
`INTERVAL` |
| `-` |
| `+` |
| `'` |
| `-` |
| `+` |
| `yearInt - monthInt ' YEAR` |
`( precisionInt )`
| `TO MONTH`
An `INTERVAL 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 [-|+] '[-|+]dayInt hoursInt' DAY [ ( precisionInt ) ] TO HOUR
`INTERVAL` |
| `-` |
| `+` |
| `'` |
| `-` |
| `+` |
| `dayInt hoursInt ' DAY` |
`( precisionInt )`
| `TO HOUR`
An `INTERVAL 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
`INTERVAL` |
| `-` |
| `+` |
| `'` |
| `-` |
| `+` |
| `dayInt 12:00:00 ' DAY` |
`( precisionInt )`
| `TO MINUTE`
An `INTERVAL 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 ) ]
`INTERVAL` |
| `-` |
| `+` |
| `'` |
| `-` |
| `+` |
| `dayInt 12:00:00` |
`. 000000000`
| `' DAY` |
`( precisionInt )`
`TO SECOND` |
`( fractionalPrecisionInt )`
An `INTERVAL 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
`INTERVAL` |
| `-` |
| `+` |
| `'` |
| `-` |
| `+` |
| `12:00:00 ' HOUR` |
`( precisionInt )`
| `TO MINUTE`
An `INTERVAL 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 ) ]
`INTERVAL` |
| `-` |
| `+` |
| `'` |
| `-` |
| `+` |
| `12:00:00` |
`. 000000000`
| `' HOUR` |
`( precisionInt )`
`TO SECOND` |
`( fractionalPrecisionInt )`
An `INTERVAL 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 ) ]
`INTERVAL` |
| `-` |
| `+` |
| `'` |
| `-` |
| `+` |
| `12:00:00` |
`. 000000000`
| `' MINUTE` |
`( precisionInt )`
`TO SECOND` |
`( fractionalPrecisionInt )`
An `INTERVAL 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 }
| `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 for `EXTRACT, DATEADD, DATEDIFF`, and `DATE_TRUNC` 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 ### 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 ### Millennium field
    MILLENNIUM
`MILLENNIUM` Century, or one thousand years (2001-01-01 to 3000-12-31). Example: MILLENNIUM ### Century field
    CENTURY
`CENTURY` Century, or one hundred years (2001-01-01 to 2100-12-31). Example: CENTURY ### Decade field
    DECADE
`DECADE` Decade, or ten years (2020-01-01 to 2029-12-31). Example: DECADE ### Quarter field
    QUARTER
`QUARTER` Quarter (1-4). Example: QUARTER ### 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 ### Day of year field
    { DAYOFYEAR | DAY_OF_YEAR } | { DOY | DY }
| `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` |
| `ISODOW` |
`ISO` day of week (1-7). Monday is 1. Example: ISO_DAY_OF_WEEK ### ISO week 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 ### ISO week year field
    { ISO_WEEK_YEAR } | { ISO_YEAR | ISOYEAR }
| `ISO_WEEK_YEAR` |
| `ISO_YEAR` |
| `ISOYEAR` |
Returns the `ISO` week-based year from a date/time value. Example: ISO_WEEK_YEAR ### Day of week field
    { DAY_OF_WEEK | DAYOFWEEK } | { DOW }
| `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` |
| `WW` |
| `W` |
| `SQL_TSI_WEEK` |
Week of year (1-53) using local rules. Example: WEEK ### Week year field
    { WEEK_YEAR }
`WEEK_YEAR` Returns the week-based year (locale-specific) from a date/time value. Example: WEEK_YEAR ### 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' ### Array element reference
    { array | json } '[' indexInt ']'
| `array` |
| `json` |
| `[ indexInt ]`
Returns array element at specified 1-based index. Returns `NULL` if array or json is null, index is null, or element with specified index isn't found in `JSON`. Example: A[2] M[5][8] ### Field reference
    (expression).fieldName
`( expression ) . fieldName`
Returns field value from the row value or `JSON` value. Returns `NULL` if value is null or field with specified name isn't found in `JSON`. 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)
`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
| `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
    CASE expression
    { WHEN { expression | conditionRightHandSide } [,...] THEN expression } [...]
    [ ELSE expression ] END
`CASE expression`
`WHEN` |
| `expression` |
| `conditionRightHandSide` |
| `, ...` |
| `THEN expression` |
| `...` |
`ELSE expression`
| `END`
Returns then expression from the first when clause where one of its operands was was evaluated to `TRUE` for the case expression. If there are no such clauses, returns else expression or `NULL` if it is absent. Plain expressions are tested for equality with the case expression, `NULL` is not equal to `NULL`. 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 { 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 CASE WHEN A IS NULL THEN 'Null' ELSE 'Not null' END ### Cast specification
    CAST(value AS dataTypeOrDomain [ FORMAT templateString ])
`CAST ( value AS dataTypeOrDomain` |
`FORMAT templateString`
| `)`
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`, or `YYY` pattern missing leading digits are filled using digits from the current year. `RR` and `RRRR` have the same meaning as `YY` and `YYYY` for formatting. When a year is parsed with `RR`, 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` and `DD` may not be specified. `HH24` represent an hour (from 0 to 23). `HH` and `HH12` 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 with `HH24`. `MI` represent minutes. `SS` represent seconds of minute. `SSSSS` represent seconds of day, this pattern may not be used together with `HH24, HH` (`HH12`), A.M. (P.M.), `MI` or `SS` pattern. `FF1, FF2`, ..., `FF9` represent fractional seconds. `TZH, TZM` and `TZH` 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
`AES` Only the algorithm `AES` (`AES-128`) is supported currently. Example: AES ### Column Definition
    dataTypeOrDomain [ VISIBLE | INVISIBLE ]
    [ { DEFAULT expression
    | GENERATED ALWAYS AS (generatedColumnExpression)
    | GENERATED {ALWAYS | BY DEFAULT} AS IDENTITY [(sequenceOption [...])]} ]
    [ ON UPDATE expression ]
    [ DEFAULT ON NULL ]
    [ SELECTIVITY selectivityInt ] [ COMMENT expression ]
    [ columnConstraintDefinition ] [...]
`dataTypeOrDomain` |
| `VISIBLE` |
| `INVISIBLE` |
`DEFAULT expression`
`GENERATED ALWAYS AS ( generatedColumnExpression )`
`GENERATED` |
| `ALWAYS` |
`BY DEFAULT`
| `AS IDENTITY` |
`( sequenceOption` |
| `...` |
| `)`
`ON UPDATE expression`
`DEFAULT ON NULL`
`SELECTIVITY selectivityInt`
`COMMENT expression`
| `columnConstraintDefinition` |
| `...` |
The default expression is used if no explicit value was used when adding a row and when `DEFAULT` 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 have `DEFAULT` or `ON 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 with `IDENTITY` data type or with `IDENTITY` () clause is implicitly the primary key column of this table. `GENERATED ALWAYS AS IDENTITY, GENERATED BY DEFAULT AS IDENTITY`, and `AUTO_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 implicit `NOT NULL` constraint. Identity column may not have `DEFAULT` or `ON UPDATE` expressions. `DEFAULT ON NULL` makes `NULL` value work as `DEFAULT` value is assignments to this column. The invisible column will not be displayed as a result of `SELECT` * query. Otherwise, it works as normal column. Column constraint definitions are not supported for `ALTER` 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)
| `constraintNameDefinition` |
`NOT NULL`
`PRIMARY KEY`
`UNIQUE` |
| `nullsDistinct` |
| `referencesSpecification` |
`CHECK ( condition )`
`NOT NULL` disallows `NULL` value for a column. `PRIMARY KEY` and `UNIQUE` require unique values. `PRIMARY KEY` also disallows `NULL` values and marks the column as a primary key. `UNIQUE` constraint allows `NULL` values, if nulls distinct clause is not specified, the default is `NULLS 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 return `TRUE` or `UNKNOWN` (`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
| `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 ] [...] ] */
`/ *` |
| `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 ]
    | NOT condition
    | EXISTS ( query )
    | UNIQUE [ nullsDistinct ] ( query )
    | INTERSECTS (operand, operand)
`operand` |
| `conditionRightHandSide` |
`NOT condition`
`EXISTS ( query )`
`UNIQUE` |
| `nullsDistinct` |
| `( 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`. 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 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
    comparisonRightHandSide
    | quantifiedComparisonRightHandSide
    | nullPredicateRightHandSide
    | distinctPredicateRightHandSide
    | quantifiedDistinctPredicateRightHandSide
    | booleanTestRightHandSide
    | typePredicateRightHandSide
    | jsonPredicateRightHandSide
    | betweenPredicateRightHandSide
    | inPredicateRightHandSide
    | likePredicateRightHandSide
    | regexpPredicateRightHandSide
| `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
    compare operand
`compare operand`
Right side of comparison predicates. Example: > 10 ### Quantified Comparison Right Hand Side
    compare { ALL | ANY | SOME } ( { query | { array } } )
`compare` |
| `ALL` |
| `ANY` |
| `SOME` |
| `(` |
| `query` |
| `array` |
| `)`
Right side of quantified comparison predicates. Quantified comparison predicate `ALL` returns `TRUE` if specified comparison operation between left size of condition and each row from a subquery or each element of array returns `TRUE`, including case when there are no rows (elements). `ALL` predicate returns `FALSE` if at least one such comparison returns `FALSE`. Otherwise it returns `UNKNOWN`. 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 or at least one element of array returns `TRUE`. `ANY` and `SOME` predicates return `FALSE` if all such comparisons return `FALSE`. Otherwise they return `UNKNOWN`. Note that these predicates have priority over `ANY` and `SOME` 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
`IS` |
| `NOT` |
| `NULL`
Right side of null predicate. 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`. Example: IS NULL ### Distinct Predicate Right Hand Side
    IS [ NOT ] [ DISTINCT FROM ] operand
`IS` |
| `NOT` |
`DISTINCT FROM`
| `operand`
Right side of distinct predicate. Distinct predicate is null-safe, meaning `NULL` is considered the same as `NULL`, and the condition never evaluates to `UNKNOWN`. Example: IS NOT DISTINCT FROM OTHER ### Quantified Distinct Predicate Right Hand Side
    IS [ NOT ] [ DISTINCT FROM ] { ALL | ANY | SOME } ( { query | array } )
`IS` |
| `NOT` |
`DISTINCT FROM`
| `ALL` |
| `ANY` |
| `SOME` |
| `(` |
| `query` |
| `array` |
| `)`
Right side of quantified distinct predicate. Quantified distinct predicate is null-safe, meaning `NULL` is considered the same as `NULL`, and the condition never evaluates to `UNKNOWN`. Quantified distinct predicate `ALL` returns `TRUE` if specified distinct predicate between left size of condition and each row from a subquery or each element of array returns `TRUE`, including case when there are no rows. Otherwise it returns `FALSE`. Quantified distinct predicates `ANY` and `SOME` return `TRUE` if specified distinct predicate between left size of condition and at least one row from a subquery or at least one element of array returns `TRUE`. Otherwise they return `FALSE`. Note that these predicates have priority over `ANY` and `SOME` 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 }
`IS` |
| `NOT` |
| `TRUE` |
| `FALSE` |
| `UNKNOWN` |
Right side of boolean test. Checks whether the specified value is (not) `TRUE, FALSE`, or `UNKNOWN` (`NULL`) and return `TRUE` or `FALSE`. This test is null-safe. Example: IS TRUE ### Type Predicate Right Hand Side
    IS [ NOT ] OF (dataType [,...])
`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 is `NULL`, the result is `UNKNOWN`. Example: IS OF (INTEGER, BIGINT) ### JSON Predicate Right Hand Side
    IS [ NOT ] JSON [ VALUE | ARRAY | OBJECT | SCALAR ]
        [ [ WITH | WITHOUT ] UNIQUE [ KEYS ] ]
`IS` |
| `NOT` |
| `JSON` |
| `VALUE` |
| `ARRAY` |
| `OBJECT` |
| `SCALAR` |
| `WITH` |
| `WITHOUT` |
| `UNIQUE` |
| `KEYS` |
Right side of `JSON` predicate. Checks 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. This predicate isn't null-safe, it returns `UNKNOWN` if operand is `NULL`. Example: IS JSON OBJECT WITH UNIQUE KEYS ### Between Predicate Right Hand Side
    [ NOT ] BETWEEN [ ASYMMETRIC | SYMMETRIC ] operand AND operand
| `NOT` |
| `BETWEEN` |
| `ASYMMETRIC` |
| `SYMMETRIC` |
| `operand AND operand`
Right side of between predicate. Checks whether the value is within the range inclusive. `V BETWEEN [ ASYMMETRIC ] A AND B` is equivalent to `A <= V AND V <= B`. `V BETWEEN SYMMETRIC A AND B` is equivalent to `A <= V AND V <= B OR A >= V AND V >= B`. Example: BETWEEN LOW AND HIGH ### In Predicate Right Hand Side
    [ NOT ] IN ( { query | expression [,...] } )
| `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. Returns `TRUE` if row value on the left side is equal to one of values on the right side, `FALSE` if all comparison operations were evaluated to `FALSE` or right side has no values, and `UNKNOWN` otherwise. This operation is logically equivalent to `OR` 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 [ ESCAPE string ]
| `NOT` |
| `LIKE` |
| `ILIKE` |
| `operand` |
`ESCAPE string`
Right side of like predicate. 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. Example: LIKE 'a%' ### Regexp Predicate Right Hand Side
    { [ NOT ] REGEXP operand }
| `NOT` |
| `REGEXP operand`
Right side of Regexp predicate. Regular expression matching is used. See Java `Matcher.find` for details. Example: REGEXP '[a-z]' ### Nulls Distinct
    NULLS { DISTINCT | NOT DISTINCT | { ALL DISTINCT } }
`NULLS` |
| `DISTINCT` |
`NOT DISTINCT`
`ALL DISTINCT`
Are nulls distinct for unique constraint, index, or predicate. If `NULLS DISTINCT` is specified, rows with null value in any column are distinct. If `NULLS ALL DISTINCT` is specified, rows with null value in all columns are distinct. If `NULLS 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)
| `constraintNameDefinition` |
`PRIMARY KEY` |
| `HASH` |
| `( columnName` |
| `, ...` |
| `)`
`UNIQUE` |
| `nullsDistinct` |
| `(` |
`columnName` |
| `, ...` |
| `VALUE` |
| `)`
| `referentialConstraint` |
`CHECK ( condition )`
Defines a constraint. `PRIMARY KEY` and `UNIQUE` require unique values. `PRIMARY KEY` also disallows `NULL` values and marks the column as a primary key, a table can have only one primary key. `UNIQUE` constraint supports `NULL` values and rows with `NULL` value in any column are considered as unique. `UNIQUE` constraint allows `NULL` values, if nulls distinct clause is not specified, the default is `NULLS 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 return `TRUE` or `UNKNOWN` (`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
`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`
| `optionString` |
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. Example: SELECT ID FROM FINAL TABLE (INSERT INTO TEST (A, B) VALUES (1, 2)) ### Data Type or Domain
    dataType | [schemaName.]domainName
| `dataType` |
`schemaName .`
| `domainName`
A data type or domain name. Example: INTEGER MY_DOMAIN ### Data Type
    predefinedType | arrayType | rowType
| `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 }
| `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
`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 values
    VALUES { DEFAULT|expression | [ROW] ({DEFAULT|expression} [,...]) }, [,...]
`VALUES` |
| `DEFAULT` |
| `expression` |
| `ROW` |
| `(` |
| `DEFAULT` |
| `expression` |
| `, ...` |
| `)`
| `,` |
| `, ...` |
Values for `INSERT` 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])]
`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
    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
`WHEN MATCHED` |
`AND expression`
| `THEN`
`UPDATE SET setClauseList`
| `DELETE` |
`WHEN MATCHED` clause for `MERGE USING` command. Example: WHEN MATCHED THEN UPDATE SET NAME = S.NAME WHEN MATCHED THEN DELETE ### Merge when not matched clause
    WHEN NOT MATCHED [ AND expression ] THEN INSERT
    [ ( columnName [,...] ) ]
    [ overrideClause ]
    VALUES ({DEFAULT|expression} [,...])
`WHEN NOT MATCHED` |
`AND expression`
| `THEN INSERT`
`( columnName` |
| `, ...` |
| `)`
| `overrideClause` |
`VALUES (` |
| `DEFAULT` |
| `expression` |
| `, ...` |
| `)`
`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. 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. 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] ### Override clause
    OVERRIDING { USER | SYSTEM } VALUE
`OVERRIDING` |
| `USER` |
| `SYSTEM` |
| `VALUE`
If `OVERRIDING USER VALUE` is specified, `INSERT` statement ignores the provided value for identity column and generates a new one instead. If `OVERRIDING SYSTEM VALUE` is specified, `INSERT` statement assigns the provided value to identity column. If neither clauses are specified, `INSERT` statement assigns the provided value to `GENERATED BY DEFAULT AS IDENTITY` column, but throws an exception if value is specified for `GENERATED ALWAYS AS IDENTITY` column. Example: OVERRIDING SYSTEM VALUE OVERRIDING USER VALUE ### 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"
    | U&"anything" [ UESCAPE 'anything' ]
`" 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 with `U&` 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` and `000000` are Unicode character codes in hexadecimal notation. Optional `UESCAPE` 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
`FOREIGN KEY ( columnName` |
| `, ...` |
| `) referencesSpecification`
Defines a referential constraint. Example: FOREIGN KEY(ID) REFERENCES TEST(ID) ### References Specification
    REFERENCES [ refTableName ] [ ( refColumnName [,...] ) ]
    [ ON DELETE referentialAction ] [ ON UPDATE referentialAction ]
`REFERENCES` |
| `refTableName` |
`( refColumnName` |
| `, ...` |
| `)`
`ON DELETE referentialAction`
`ON UPDATE referentialAction`
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 }
| `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: CASCADE SET NULL ### 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 ### Select order
    { expression | { int } } [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
| `expression` |
| `int` |
| `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 ### 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 DOCUMENT_ID ### 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. The next value of the sequence is generated only once for each processed row. If this expression is used multiple times with the same sequence it returns the same value within a processed row. 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 option
    START WITH long
    | { RESTART WITH long }
    | basicSequenceOption
`START WITH long`
`RESTART WITH long`
| `basicSequenceOption` |
Option of a sequence. `START WITH` is used to set the initial value of the sequence. If initial value is not defined, `MINVALUE` for incrementing sequences and `MAXVALUE` for decrementing sequences is used. `RESTART` is used to immediately restart the sequence with the specified value. Example: START WITH 10000 NO CACHE ### Alter sequence option
    { START WITH long }
    | RESTART [ WITH long ]
    | basicSequenceOption
`START WITH long`
`RESTART` |
`WITH long`
| `basicSequenceOption` |
Option of a sequence. `START WITH` is used to change the initial value of the sequence. It does not affect the current value of the sequence, it only changes the preserved initial value that is used for simple `RESTART` without a value. `RESTART` is used to restart the sequence from its initial value or with the specified value. Example: START WITH 10000 NO CACHE ### Alter identity column option
    { START WITH long }
    | RESTART [ WITH long ]
    | SET basicSequenceOption
`START WITH long`
`RESTART` |
`WITH long`
`SET basicSequenceOption`
Option of an identity column. `START WITH` is used to set or change the initial value of the sequence. `START WITH` does not affect the current value of the sequence, it only changes the preserved initial value that is used for simple `RESTART` without a value. `RESTART` is used to restart the sequence from its initial value or with the specified value. Example: START WITH 10000 SET NO CACHE ### Basic sequence option
    INCREMENT BY long
    | MINVALUE long | NO MINVALUE | { NOMINVALUE }
    | MAXVALUE long | NO MAXVALUE | { NOMAXVALUE }
    | CYCLE | NO CYCLE | { EXHAUSTED } | { NOCYCLE }
    | { CACHE long } | { NO CACHE } | { NOCACHE }
`INCREMENT BY long`
`MINVALUE long`
`NO MINVALUE`
| `NOMINVALUE` |
`MAXVALUE long`
`NO MAXVALUE`
| `NOMAXVALUE` |
| `CYCLE` |
`NO CYCLE`
| `EXHAUSTED` |
| `NOCYCLE` |
`CACHE long`
`NO CACHE`
| `NOCACHE` |
Basic option of a sequence. `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. Sequences with `EXHAUSTED` option can't return values until they will be restarted. 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 if sequence has enough range of values. `NO CACHE` option or the cache size 1 or lower disable the cache. If `CACHE` option is specified, it cannot be larger than the total number of values that sequence can produce within a cycle. Example: MAXVALUE 100000 CYCLE NO CACHE ### Set clause list
    { { updateTarget = { DEFAULT | expression } }
    | { ( updateTarget [,...] ) = { rowValueExpression | (query) } } } [,...]
`updateTarget =` |
| `DEFAULT` |
| `expression` |
`( updateTarget` |
| `, ...` |
| `) =` |
| `rowValueExpression` |
`( query )`
| `, ...` |
List of `SET` clauses. Each column may be specified only once in update targets. Example: NAME = 'Test', PRICE = 2 (A, B) = (1, 2) (A, B) = (1, 2), C = 3 (A, B) = (SELECT X, Y FROM OTHER T2 WHERE T1.ID = T2.ID) ### Sort specification
    expression [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
`expression` |
| `ASC` |
| `DESC` |
`NULLS` |
| `FIRST` |
| `LAST` |
Sorts the result by an expression. Example: X ASC NULLS FIRST ### Sort specification list
    sortSpecification [,...]
`sortSpecification` |
| `, ...` |
Sorts the result by expressions. Example: V A, B DESC NULLS FIRST ### 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 ### Update target
    columnName [ '[' int ']' [...] ]
`columnName` |
`[ int ]` |
| `...` |
Column or element of a column of `ARRAY` data type. If array indexes are specified, column must have a compatible `ARRAY` data type and updated rows may not have `NULL` values in this column. It means for C[2][3] both C and C[2] may not be `NULL`. Too short arrays are expanded, missing elements are set to `NULL`. Example: A B[1] C[2][3] ### Within group specification
    WITHIN GROUP (ORDER BY sortSpecificationList)
`WITHIN GROUP ( ORDER BY sortSpecificationList )`
Group specification for ordered set functions. Example: WITHIN GROUP (ORDER BY ID DESC) ### Wildcard expression
    [[schemaName.]tableAlias.]*
    [EXCEPT ([[schemaName.]tableAlias.]columnName, [,...])]
`schemaName .`
| `tableAlias .`
| `*`
`EXCEPT (` |
`schemaName .`
| `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 sortSpecificationList]
    [windowFrame])
`(` |
| `existingWindowName` |
`PARTITION BY expression` |
| `, ...` |
`ORDER BY sortSpecificationList`
| `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 )
    | arrayElementReference
    | fieldReference
    | ( query )
    | caseExpression
    | castSpecification
    | userDefinedFunctionName }
    [ timeZone | intervalQualifier ]
| `value` |
| `column` |
`?` |
| `int` |
| `sequenceValueExpression` |
| `Function` |
| `-` |
| `+` |
| `term`
`( expression )`
| `arrayElementReference` |
| `fieldReference` |
`( query )`
| `caseExpression` |
| `castSpecification` |
| `userDefinedFunctionName` |
| `timeZone` |
| `intervalQualifier` |
A value. Parameters can be indexed, for example `?1` meaning the first parameter. Interval qualifier may only be specified for a compatible value or for a subtraction operation between two datetime values. The subtraction operation ignores the leading field precision of the qualifier. 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