▲
Search:
| Highlight keyword(s)
Home Download Cheat Sheet Documentation Quickstart Installation Tutorial Features Performance Advanced Reference Commands Functions Data Types SQL Grammar System Tables Javadoc PDF (1.5 MB) Support FAQ Error Analyzer Google Group (English) Google Group (Japanese) Google Group (Chinese) Appendix History & Roadmap License Build Links MVStore Architecture |  | # SQL Grammar ## Index ### Literals
Value Array Boolean Bytes Date Date and time Decimal Dollar Quoted String Hex Number Int Long | Null Number Numeric String Time Timestamp Timestamp with time zone Interval INTERVAL YEAR INTERVAL MONTH INTERVAL DAY | INTERVAL HOUR INTERVAL MINUTE INTERVAL SECOND INTERVAL YEAR TO MONTH INTERVAL DAY TO HOUR INTERVAL DAY TO MINUTE INTERVAL DAY TO SECOND INTERVAL HOUR TO MINUTE INTERVAL HOUR TO SECOND INTERVAL MINUTE TO SECOND
### Datetime fields
Datetime field Year field Month field Day of month field Hour field Minute field Second field | Millisecond field Microsecond field Nanosecond field Timezone hour field Timezone minute field Day of week field ISO week year field | ISO day of week field Week of year field ISO week of year field Quarter field Day of year field Epoch field
### Other Grammar
Alias And Condition Case Case When Cipher Column Definition Comments Compare Condition Condition Right Hand Side Constraint Constraint Name Definition Csv Options Data Type Digit Expression | Factor Hex Index Column Insert columns and source Insert values Merge when clause Merge when matched clause Merge when not matched clause Name Operand Order Quoted Name Referential Constraint Referential Action Script Compression Encryption Row value expression | Select Expression Sequence options Set clause list Summand Table Expression Within group specification Wildcard expression Window name or specification Window specification Window frame Window frame preceding Window frame bound Term Column
## Details Click on the header to switch between railroad diagram and BNF. ## Literals ### Value
    string | dollarQuotedString | numeric | dateAndTime | boolean | bytes
    | interval | array | null
| `string` |
| `dollarQuotedString` |
| `numeric` |
| `dateAndTime` |
| `boolean` |
| `bytes` |
| `interval` |
| `array` |
| `null` |
A literal value of any data type, or null. Example: 10 ### Array
    ARRAY '[' [ expression, [,...] ] ']'
`ARRAY [` |
`expression ,` |
| `, ...` |
| `]`
An array of values. Example: ARRAY[1, 2] ARRAY[1] ARRAY[] ### Boolean
    TRUE | FALSE
| `TRUE` |
| `FALSE` |
A boolean value. Example: TRUE ### Bytes
    X'hex'
`X ' hex '`
A binary value. The hex value is not case sensitive. Example: X'01FF' ### Date
    DATE 'yyyy-MM-dd'
`DATE ' 2000-01-01 '`
A date literal. The limitations are the same as for the Java data type `java.sql.Date`, but for compatibility with other databases the suggested minimum and maximum years are 0001 and 9999. Example: DATE '2004-12-31' ### Date and time
    date | time | timestamp | timestampWithTimeZone
| `date` |
| `time` |
| `timestamp` |
| `timestampWithTimeZone` |
A literal value of any date-time data type. Example: TIMESTAMP '1999-01-31 10:00:00' ### Decimal
    [ + | - ] { { number [ . number ] } | { . number } }
    [ E [ + | - ] expNumber [...] ] ]
| `+` |
| `-` |
`number` |
`. number`
`. number`
`E` |
| `+` |
| `-` |
| `expNumber` |
| `...` |
A decimal number with fixed precision and scale. Internally, `java.lang.BigDecimal` is used. To ensure the floating point representation is used, use `CAST`(X `AS DOUBLE`). There are some special decimal values: to represent positive infinity, use `POWER(0, -1)`; for negative infinity, use `(-POWER(0, -1))`; for -0.0, use `(-CAST(0 AS DOUBLE))`; for `NaN` (not a number), use `SQRT(-1)`. Example: SELECT -1600.05 SELECT CAST(0 AS DOUBLE) SELECT -1.4e-10 ### Dollar Quoted String
    $$anything$$
`$ $ anything $ $`
A string starts and ends with two dollar signs. Two dollar signs are not allowed within the text. A whitespace is required before the first set of dollar signs. No escaping is required within the text. Example: $$John's car$$ ### Hex Number
    [ + | - ] 0x hex
| `+` |
| `-` |
| `0x hex`
A number written in hexadecimal notation. Example: 0xff ### Int
    [ + | - ] number
| `+` |
| `-` |
| `number`
The maximum integer number is 2147483647, the minimum is -2147483648. Example: 10 ### Long
    [ + | - ] number
| `+` |
| `-` |
| `number`
Long numbers are between -9223372036854775808 and 9223372036854775807. Example: 100000 ### Null
    NULL
`NULL` `NULL` is a value without data type and means 'unknown value'. Example: NULL ### Number
    digit [...]
`digit` |
| `...` |
The maximum length of the number depends on the data type used. Example: 100 ### Numeric
    decimal | int | long | hexNumber
| `decimal` |
| `int` |
| `long` |
| `hexNumber` |
The data type of a numeric value is always the lowest possible for the given value. If the number contains a dot this is decimal; otherwise it is int, long, or decimal (depending on the value). Example: SELECT -1600.05 SELECT CAST(0 AS DOUBLE) SELECT -1.4e-10 ### String
    'anything'
`' anything '`
A string starts and ends with a single quote. Two single quotes can be used to create a single quote inside a string. Example: 'John''s car' ### 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' ### 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. The limitations are the same as for the Java data type `java.sql.Timestamp`, but for compatibility with other databases the suggested minimum and maximum years are 0001 and 9999. Example: TIMESTAMP '2005-12-31 23:59:59' ### Timestamp with time zone
    TIMESTAMP WITH TIME ZONE 'yyyy-MM-dd hh:mm:ss[.nnnnnnnnn]
    [Z | { - | + } timeZoneOffsetString | timeZoneNameString ]'
`TIMESTAMP WITH TIME ZONE ' 2000-01-01 12:00:00` |
`. 000000000`
| `Z` |
| `-` |
| `+` |
| `timeZoneOffsetString`
| `timeZoneNameString` |
| `'`
A timestamp with time zone literal. If name of time zone is specified it will be converted to time zone offset. Example: TIMESTAMP WITH TIME ZONE '2005-12-31 23:59:59Z' TIMESTAMP WITH TIME ZONE '2005-12-31 23:59:59-10:00' TIMESTAMP WITH TIME ZONE '2005-12-31 23:59:59.123+05' TIMESTAMP WITH TIME ZONE '2005-12-31 23:59:59.123456789 Europe/London' ### Interval
    intervalYear | intervalMonth | intervalDay | intervalHour | intervalMinute
    | intervalSecond | intervalYearToMonth | intervalDayToHour
    | intervalDayToMinute | intervalDayToSecond | intervalHourToMinute
    | intervalHourToSecond | intervalMinuteToSecond
| `intervalYear` |
| `intervalMonth` |
| `intervalDay` |
| `intervalHour` |
| `intervalMinute` |
| `intervalSecond` |
| `intervalYearToMonth` |
| `intervalDayToHour` |
| `intervalDayToMinute` |
| `intervalDayToSecond` |
| `intervalHourToMinute` |
| `intervalHourToSecond` |
| `intervalMinuteToSecond` |
An interval literal. Example: INTERVAL '1-2' YEAR TO MONTH ### INTERVAL YEAR
    INTERVAL [-|+] '[-|+]yearInt' YEAR
`INTERVAL` |
| `-` |
| `+` |
| `'` |
| `-` |
| `+` |
| `yearInt ' YEAR`
An `INTERVAL YEAR` literal. Example: INTERVAL '10' YEAR ### INTERVAL MONTH
    INTERVAL [-|+] '[-|+]monthInt' MONTH
`INTERVAL` |
| `-` |
| `+` |
| `'` |
| `-` |
| `+` |
| `monthInt ' MONTH`
An `INTERVAL MONTH` literal. Example: INTERVAL '10' MONTH ### INTERVAL DAY
    INTERVAL [-|+] '[-|+]dayInt' DAY
`INTERVAL` |
| `-` |
| `+` |
| `'` |
| `-` |
| `+` |
| `dayInt ' DAY`
An `INTERVAL DAY` literal. Example: INTERVAL '10' DAY ### INTERVAL HOUR
    INTERVAL [-|+] '[-|+]hourInt' HOUR
`INTERVAL` |
| `-` |
| `+` |
| `'` |
| `-` |
| `+` |
| `hourInt ' HOUR`
An `INTERVAL HOUR` literal. Example: INTERVAL '10' HOUR ### INTERVAL MINUTE
    INTERVAL [-|+] '[-|+]minuteInt' MINUTE
`INTERVAL` |
| `-` |
| `+` |
| `'` |
| `-` |
| `+` |
| `minuteInt ' MINUTE`
An `INTERVAL MINUTE` literal. Example: INTERVAL '10' MINUTE ### INTERVAL SECOND
    INTERVAL [-|+] '[-|+]secondInt[.nnnnnnnnn]' SECOND
`INTERVAL` |
| `-` |
| `+` |
| `'` |
| `-` |
| `+` |
| `secondInt` |
`. 000000000`
| `' SECOND`
An `INTERVAL SECOND` literal. Example: INTERVAL '10.123' SECOND ### INTERVAL YEAR TO MONTH
    INTERVAL [-|+] '[-|+]yearInt-monthInt' YEAR TO MONTH
`INTERVAL` |
| `-` |
| `+` |
| `'` |
| `-` |
| `+` |
| `yearInt - monthInt ' YEAR TO MONTH`
An `INTERVAL YEAR TO MONTH` literal. Example: INTERVAL '1-6' YEAR TO MONTH ### INTERVAL DAY TO HOUR
    INTERVAL [-|+] '[-|+]dayInt hoursInt' DAY TO HOUR
`INTERVAL` |
| `-` |
| `+` |
| `'` |
| `-` |
| `+` |
| `dayInt hoursInt ' DAY TO HOUR`
An `INTERVAL DAY TO HOUR` literal. Example: INTERVAL '10 11' DAY TO HOUR ### INTERVAL DAY TO MINUTE
    INTERVAL [-|+] '[-|+]dayInt hh:mm' DAY TO MINUTE
`INTERVAL` |
| `-` |
| `+` |
| `'` |
| `-` |
| `+` |
| `dayInt 12:00:00 ' DAY TO MINUTE`
An `INTERVAL DAY TO MINUTE` literal. Example: INTERVAL '10 11:12' DAY TO MINUTE ### INTERVAL DAY TO SECOND
    INTERVAL [-|+] '[-|+]dayInt hh:mm:ss[.nnnnnnnnn]' DAY TO SECOND
`INTERVAL` |
| `-` |
| `+` |
| `'` |
| `-` |
| `+` |
| `dayInt 12:00:00` |
`. 000000000`
| `' DAY TO SECOND`
An `INTERVAL DAY TO SECOND` literal. Example: INTERVAL '10 11:12:13.123' DAY TO SECOND ### INTERVAL HOUR TO MINUTE
    INTERVAL [-|+] '[-|+]hh:mm' HOUR TO MINUTE
`INTERVAL` |
| `-` |
| `+` |
| `'` |
| `-` |
| `+` |
| `12:00:00 ' HOUR TO MINUTE`
An `INTERVAL HOUR TO MINUTE` literal. Example: INTERVAL '10:11' HOUR TO MINUTE ### INTERVAL HOUR TO SECOND
    INTERVAL [-|+] '[-|+]hh:mm:ss[.nnnnnnnnn]' HOUR TO SECOND
`INTERVAL` |
| `-` |
| `+` |
| `'` |
| `-` |
| `+` |
| `12:00:00` |
`. 000000000`
| `' HOUR TO SECOND`
An `INTERVAL HOUR TO SECOND` literal. Example: INTERVAL '10:11:12.123' HOUR TO SECOND ### INTERVAL MINUTE TO SECOND
    INTERVAL [-|+] '[-|+]mm:ss[.nnnnnnnnn]' MINUTE TO SECOND
`INTERVAL` |
| `-` |
| `+` |
| `'` |
| `-` |
| `+` |
| `12:00:00` |
`. 000000000`
| `' MINUTE TO SECOND`
An `INTERVAL MINUTE TO SECOND` literal. Example: INTERVAL '11:12.123' MINUTE TO SECOND ## Datetime fields ### Datetime field
    yearField | monthField | dayOfMonthField
    | hourField | minuteField | secondField
    | millisecondField | microsecondField | nanosecondField
    | timezoneHourField | timezoneMinuteField
    | dayOfWeekField | isoWeekYearField | isoDayOfWeekField
    | weekOfYearField | isoWeekOfYearField
    | quarterField | dayOfYearField | epochField
| `yearField` |
| `monthField` |
| `dayOfMonthField` |
| `hourField` |
| `minuteField` |
| `secondField` |
| `millisecondField` |
| `microsecondField` |
| `nanosecondField` |
| `timezoneHourField` |
| `timezoneMinuteField` |
| `dayOfWeekField` |
| `isoWeekYearField` |
| `isoDayOfWeekField` |
| `weekOfYearField` |
| `isoWeekOfYearField` |
| `quarterField` |
| `dayOfYearField` |
| `epochField` |
Fields for `EXTRACT, DATEADD`, and `DATEDIFF` functions. Example: YEAR ### Year field
    YEAR | YYYY | YY | SQL_TSI_YEAR
| `YEAR` |
| `YYYY` |
| `YY` |
| `SQL_TSI_YEAR` |
Year. Example: YEAR ### Month field
    MONTH | MM | M | SQL_TSI_MONTH
| `MONTH` |
| `MM` |
| `M` |
| `SQL_TSI_MONTH` |
Month (1-12). Example: MONTH ### Day of month field
    DAY | DD | D | SQL_TSI_DAY
| `DAY` |
| `DD` |
| `D` |
| `SQL_TSI_DAY` |
Day of month (1-31). Example: DAY ### Hour field
    HOUR | HH | SQL_TSI_HOUR
| `HOUR` |
| `HH` |
| `SQL_TSI_HOUR` |
Hour (0-23). Example: HOUR ### Minute field
    MINUTE | MI | N | SQL_TSI_MINUTE
| `MINUTE` |
| `MI` |
| `N` |
| `SQL_TSI_MINUTE` |
Minute (0-59). Example: MINUTE ### Second field
    SECOND | SS | S | SQL_TSI_SECOND
| `SECOND` |
| `SS` |
| `S` |
| `SQL_TSI_SECOND` |
Second (0-59). Example: SECOND ### Millisecond field
    MILLISECOND | MS
| `MILLISECOND` |
| `MS` |
Millisecond (0-999). Example: MILLISECOND ### Microsecond field
    MICROSECOND | MCS
| `MICROSECOND` |
| `MCS` |
Microsecond (0-999999). Example: MICROSECOND ### Nanosecond field
    NANOSECOND | NS
| `NANOSECOND` |
| `NS` |
Nanosecond (0-999999999). Example: NANOSECOND ### Timezone hour field
    TIMEZONE_HOUR
`TIMEZONE_HOUR` Timezone hour (from -18 to +18). Example: TIMEZONE_HOUR ### Timezone minute field
    TIMEZONE_MINUTE
`TIMEZONE_MINUTE` Timezone minute (from -59 to +59). Example: TIMEZONE_MINUTE ### Day of week field
    DAY_OF_WEEK | DAYOFWEEK | DOW
| `DAY_OF_WEEK` |
| `DAYOFWEEK` |
| `DOW` |
Day of week (1-7). Sunday is 1. Example: DAY_OF_WEEK ### ISO week year field
    ISO_YEAR | ISOYEAR
| `ISO_YEAR` |
| `ISOYEAR` |
Returns the `ISO` week year from a date/time value. Example: ISO_YEAR ### ISO day of week field
    ISO_DAY_OF_WEEK | ISODOW
| `ISO_DAY_OF_WEEK` |
| `ISODOW` |
`ISO` day of week (1-7). Monday is 1. Example: ISO_DAY_OF_WEEK ### Week of year field
    WEEK | WW | W | SQL_TSI_WEEK
| `WEEK` |
| `WW` |
| `W` |
| `SQL_TSI_WEEK` |
Week of year (1-53). `EXTRACT` function uses local rules to get number of week in year. `DATEDIFF` function uses Sunday as a first day of week. Example: WEEK ### ISO week of year field
    ISO_WEEK
`ISO_WEEK` `ISO` week of year (1-53). `ISO` definition is used when first week of year should have at least four days and week is started with Monday. Example: ISO_WEEK ### Quarter field
    QUARTER
`QUARTER` Quarter (1-4). Example: QUARTER ### Day of year field
    DAYOFYEAR | DAY_OF_YEAR | DOY | DY
| `DAYOFYEAR` |
| `DAY_OF_YEAR` |
| `DOY` |
| `DY` |
Day of year (1-366). Example: DAYOFYEAR ### Epoch field
    EPOCH
`EPOCH` For `TIMESTAMP` values number of seconds since 1970-01-01 00:00:00 in local time zone. For `TIMESTAMP WITH TIME ZONE` values number of seconds since 1970-01-01 00:00:00 in `UTC` time zone. For `DATE` values number of seconds since 1970-01-01. For `TIME` values number of seconds since midnight. Example: EPOCH ## Other Grammar ### Alias
    name
`name` An alias is a name that is only valid in the context of the statement. Example: A ### And Condition
    condition [ { AND condition } [...] ]
`condition` |
`AND condition` |
| `...` |
Value or condition. Example: ID=1 AND NAME='Hi' ### Case
    CASE expression { WHEN expression THEN expression } [...]
    [ ELSE expression ] END
`CASE expression WHEN expression THEN expression` |
| `...` |
`ELSE expression`
| `END`
Returns the first expression where the value is equal to the test expression. If no else part is specified, return `NULL`. Example: CASE CNT WHEN 0 THEN 'No' WHEN 1 THEN 'One' ELSE 'Some' END ### Case When
    CASE { WHEN expression THEN expression} [...]
    [ ELSE expression ] END
`CASE WHEN expression THEN expression` |
| `...` |
`ELSE expression`
| `END`
Returns the first expression where the condition is true. If no else part is specified, return `NULL`. Example: CASE WHEN CNT<10 THEN 'Low' ELSE 'High' END ### Cipher
    AES
`AES` Only the algorithm `AES` (`AES-128`) is supported currently. Example: AES ### Column Definition
    dataType [ VISIBLE | INVISIBLE ]
    [ { DEFAULT expression
    | AS computedColumnExpression
    | GENERATED {ALWAYS | BY DEFAULT} AS IDENTITY [(sequenceOptions)]} ]
    [ ON UPDATE expression ] [ [ NOT ] NULL ]
    [ { AUTO_INCREMENT | IDENTITY } [ ( startInt [, incrementInt ] ) ] ]
    [ SELECTIVITY selectivity ] [ COMMENT expression ]
    [ PRIMARY KEY [ HASH ] | UNIQUE ] [ CHECK condition ]
`dataType` |
| `VISIBLE` |
| `INVISIBLE` |
`DEFAULT expression`
`AS computedColumnExpression`
`GENERATED` |
| `ALWAYS` |
`BY DEFAULT`
| `AS IDENTITY` |
`( sequenceOptions )`
`ON UPDATE expression`
| `NOT` |
| `NULL`
| `AUTO_INCREMENT` |
| `IDENTITY` |
`( startInt` |
`, incrementInt`
| `)`
`SELECTIVITY selectivity`
`COMMENT expression`
`PRIMARY KEY` |
| `HASH` |
| `UNIQUE` |
`CHECK condition`
Default expressions are used if no explicit value was used when adding a row. The computed column expression is evaluated and assigned whenever the row changes. On update column expression is used if row is updated, at least one column have a new value that is different from its previous value and value for this column is not set explicitly in update statement. Identity, auto-increment, or generated as identity columns are columns with a sequence as the default. The column declared as the identity columns with `IDENTITY` data type or with `IDENTITY` () clause is implicitly the primary key column of this table. `AUTO_INCREMENT` and `GENERATED` clauses do not create the primary key constraint. `GENERATED ALWAYS` is accepted by treated in the same way as `GENERATED BY DEFAULT`. The invisible column will not be displayed as a result of `SELECT` * query. Otherwise, it works as normal column. The options `PRIMARY KEY, UNIQUE`, and `CHECK` are not supported for `ALTER` statements. Check constraints can reference columns of the table, and they can reference objects that exist while the statement is executed. Conditions are only checked when a row is added or modified in the table where the constraint exists. Example: CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255) DEFAULT ''); CREATE TABLE TEST(ID BIGINT IDENTITY); CREATE TABLE TEST(QUANTITY INT, PRICE DECIMAL, AMOUNT DECIMAL AS QUANTITY*PRICE); ### Comments
    -- anything | // anything | /* anything */
`- - anything`
`/ / anything`
`/ * anything * /`
Comments can be used anywhere in a command and are ignored by the database. Line comments end with a newline. Block comments cannot be nested, but can be multiple lines long. Example: // This is a comment ### Compare
    <> | <= | >= | = | < | > | != | &&
`< >`
`< =`
`> =`
| `=` |
| `<` |
| `>` |
`! =`
| `&&` |
Comparison operator. The operator != is the same as <>. The operator `&&` means overlapping; it can only be used with geometry types. Example: <> ### Condition
    operand [ conditionRightHandSide ] | NOT condition | EXISTS ( select )
`operand` |
| `conditionRightHandSide` |
`NOT condition`
`EXISTS ( select )`
Boolean value or condition. Example: ID<>2 ### Condition Right Hand Side
    compare { { { ALL | ANY | SOME } ( select ) } | operand }
    | IS [ NOT ] NULL
    | IS [ NOT ] [ DISTINCT FROM ] operand
    | BETWEEN operand AND operand
    | IN ( { select | expression [,...] } )
    | [ NOT ] [ LIKE | ILIKE ] operand [ ESCAPE string ]
    | [ NOT ] REGEXP operand
`compare` |
| `ALL` |
| `ANY` |
| `SOME` |
| `( select )`
| `operand` |
`IS` |
| `NOT` |
| `NULL`
`IS` |
| `NOT` |
`DISTINCT FROM`
| `operand`
`BETWEEN operand AND operand`
`IN (` |
| `select` |
`expression` |
| `, ...` |
| `)`
| `NOT` |
| `LIKE` |
| `ILIKE` |
| `operand` |
`ESCAPE string`
| `NOT` |
| `REGEXP operand`
The right hand side of a condition. Quantified comparison predicate `ALL` returns `TRUE` if specified comparison operation between left size of condition and each row from a subquery returns `TRUE`, including case when there are no rows. `ALL` predicate returns `FALSE` if at least one such comparison returns `FALSE`. Otherwise it returns `NULL`. Quantified comparison predicates `ANY` and `SOME` return `TRUE` if specified comparison operation between left size of condition and at least one row from a subquery returns `TRUE`. `ANY` and `SOME` predicates return `FALSE` if all such comparisons return `FALSE`. Otherwise it returns `NULL`. Note that these predicates have priority over `ANY` and `SOME` aggregate functions with subquery on the right side. Use parentheses around aggregate function. The conditions `IS [ NOT ]` and `IS [ NOT ] DISTINCT FROM` are null-safe, meaning `NULL` is considered the same as `NULL`, and the condition never evaluates to `NULL`. When comparing with `LIKE`, the wildcards characters are `_` (any one character) and `%` (any characters). The database uses an index when comparing with `LIKE` except if the operand starts with a wildcard. To search for the characters `%` and `_`, the characters need to be escaped. The default escape character is `\` (backslash). To select no escape character, use `ESCAPE ''` (empty string). At most one escape character is allowed. Each character that follows the escape character in the pattern needs to match exactly. Patterns that end with an escape character are invalid and the expression returns `NULL`. `ILIKE` does a case-insensitive compare. When comparing with `REGEXP`, regular expression matching is used. See Java `Matcher.find` for details. Example: VALUE > 10 A IS NOT DISTINCT FROM B LIKE 'Jo%' ### Constraint
    [ constraintNameDefinition ]
    { CHECK expression
    | UNIQUE ( columnName [,...] )
    | referentialConstraint
    | PRIMARY KEY [ HASH ] ( columnName [,...] ) }
| `constraintNameDefinition` |
`CHECK expression`
`UNIQUE ( columnName` |
| `, ...` |
| `)`
| `referentialConstraint` |
`PRIMARY KEY` |
| `HASH` |
| `( columnName` |
| `, ...` |
| `)`
Defines a constraint. The check condition must evaluate to `TRUE, FALSE` or `NULL`. `TRUE` and `NULL` mean the operation is to be permitted, and `FALSE` means the operation is to be rejected. To prevent `NULL` in a column, use `NOT NULL` instead of a check constraint. Example: PRIMARY KEY(ID, NAME) ### Constraint Name Definition
    CONSTRAINT [ IF NOT EXISTS ] newConstraintName
`CONSTRAINT` |
`IF NOT EXISTS`
| `newConstraintName`
Defines a constraint name. Example: CONSTRAINT CONST_ID ### Csv Options
    charsetString [, fieldSepString [, fieldDelimString [, escString [, nullString]]]]]
    | optionString
`charsetString` |
`, fieldSepString` |
`, fieldDelimString` |
`, escString` |
`, nullString`
Optional parameters for `CSVREAD` and `CSVWRITE`. Instead of setting the options one by one, all options can be combined into a space separated key-value pairs, as follows: `STRINGDECODE('charset=UTF-8 escape=\" fieldDelimiter=\" fieldSeparator=, ' || 'lineComment=# lineSeparator=\n null= rowSeparator=')`. The following options are supported: `caseSensitiveColumnNames` (true or false; disabled by default), `charset` (for example '`UTF`-8'), `escape` (the character that escapes the field delimiter), `fieldDelimiter` (a double quote by default), `fieldSeparator` (a comma by default), `lineComment` (disabled by default), `lineSeparator` (the line separator used for writing; ignored for reading), `null`, Support reading existing `CSV` files that contain explicit `null` delimiters. Note that an empty, unquoted values are also treated as null. `preserveWhitespace` (true or false; disabled by default), `writeColumnHeader` (true or false; enabled by default). For a newline or other special character, use `STRINGDECODE` as in the example above. A space needs to be escaped with a backslash (`'\ '`), and a backslash needs to be escaped with another backslash (`'\\'`). All other characters are not to be escaped, that means newline and tab characters are written as such. Example: CALL CSVWRITE('test2.csv', 'SELECT * FROM TEST', 'charset=UTF-8 fieldSeparator=|'); ### Data Type
    intType | booleanType | tinyintType | smallintType | bigintType | identityType
    | decimalType | doubleType | realType | dateType | timeType | timestampType
    | timestampWithTimeZoneType | binaryType | otherType | varcharType
    | varcharIgnorecaseType | charType | blobType | clobType | uuidType
    | arrayType | enumType | intervalType
| `intType` |
| `booleanType` |
| `tinyintType` |
| `smallintType` |
| `bigintType` |
| `identityType` |
| `decimalType` |
| `doubleType` |
| `realType` |
| `dateType` |
| `timeType` |
| `timestampType` |
| `timestampWithTimeZoneType` |
| `binaryType` |
| `otherType` |
| `varcharType` |
| `varcharIgnorecaseType` |
| `charType` |
| `blobType` |
| `clobType` |
| `uuidType` |
| `arrayType` |
| `enumType` |
| `intervalType` |
A data type definition. Example: INT ### Digit
    0-9
`0-9` A digit. Example: 0 ### Expression
    andCondition [ { OR andCondition } [...] ]
`andCondition` |
`OR andCondition` |
| `...` |
Value or condition. Example: ID=1 OR NAME='Hi' ### Factor
    term [ { { * | / | % } term } [...] ]
`term` |
| `*` |
| `/` |
| `%` |
| `term` |
| `...` |
A value or a numeric factor. Example: ID * 10 ### 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. Two characters are one byte. Example: cafe ### Index Column
    columnName [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
`columnName` |
| `ASC` |
| `DESC` |
`NULLS` |
| `FIRST` |
| `LAST` |
Indexes this column in ascending or descending order. Usually it is not required to specify the order; however doing so will speed up large queries that order the column in the same way. Example: NAME ### Insert columns and source
    { [ ( columnName [,...] ) ]
    { insertValues | [ DIRECT ] [ SORTED ] select | DEFAULT VALUES } }
    | { SET { columnName = { DEFAULT | expression } } [,...] }
`( columnName` |
| `, ...` |
| `)`
| `insertValues` |
| `DIRECT` |
| `SORTED` |
| `select`
`DEFAULT VALUES`
`SET columnName =` |
| `DEFAULT` |
| `expression` |
| `, ...` |
Names of columns and their values for `INSERT` statement. Example: (ID, NAME) VALUES (1, 'Test') ### Insert values
    VALUES { DEFAULT|expression | [ROW] ({DEFAULT|expression} [,...]) }, [,...]
`VALUES` |
| `DEFAULT` |
| `expression` |
| `ROW` |
| `(` |
| `DEFAULT` |
| `expression` |
| `, ...` |
| `)`
| `,` |
| `, ...` |
Values for `INSERT` statement. Example: VALUES (1, 'Test') ### Merge when clause
    mergeWhenMatchedClause|mergeWhenNotMatchedClause
| `mergeWhenMatchedClause` |
| `mergeWhenNotMatchedClause` |
`WHEN MATCHED` or `WHEN NOT MATCHED` clause for `MERGE USING` command. Example: WHEN MATCHED THEN DELETE ### Merge when matched clause
    WHEN MATCHED [ AND expression ] THEN
    UPDATE SET setClauseList
    | DELETE
    | {UPDATE SET setClauseList [ WHERE expression ] DELETE [ WHERE expression ]}
`WHEN MATCHED` |
`AND expression`
| `THEN`
`UPDATE SET setClauseList`
| `DELETE` |
`UPDATE SET setClauseList` |
`WHERE expression`
| `DELETE` |
`WHERE expression`
`WHEN MATCHED` clause for `MERGE USING` command. If both `UPDATE` and `DELETE` are specified, `DELETE` can delete only rows that were updated, `WHERE` condition in `DELETE` clause can be used to specify which updated rows should be deleted. This condition checks values in updated row. Example: WHEN MATCHED THEN UPDATE SET VALUE = S.VALUE WHEN MATCHED THEN DELETE ### Merge when not matched clause
    WHEN NOT MATCHED [ AND expression ] THEN INSERT insertColumnsAndSource
`WHEN NOT MATCHED` |
`AND expression`
| `THEN INSERT insertColumnsAndSource`
`WHEN NOT MATCHED` clause for `MERGE USING` command. Example: WHEN NOT MATCHED THEN INSERT (ID, NAME) VALUES (S.ID, S.NAME) ### Name
    { { A-Z|_ } [ { A-Z|_|0-9 } [...] ] } | quotedName
`A-Z | _` |
| `A-Z | _` |
| `0-9` |
| `...` |
| `quotedName` |
Names are not case sensitive. There is no maximum name length. Example: TEST ### Operand
    summand [ { || summand } [...] ]
`summand` |
`|| summand` |
| `...` |
A value or a concatenation of values. In the default mode, the result is `NULL` if either parameter is `NULL`. Example: 'Hi' || ' Eva' ### Order
    { int | expression } [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
| `int` |
| `expression` |
| `ASC` |
| `DESC` |
`NULLS` |
| `FIRST` |
| `LAST` |
Sorts the result by the given column number, or by an expression. If the expression is a single parameter, then the value is interpreted as a column number. Negative column numbers reverse the sort order. Example: NAME DESC NULLS LAST ### Quoted Name
    "anything"
`" anything "`
Quoted names are case sensitive, and can contain spaces. There is no maximum name length. Two double quotes can be used to create a single double quote inside an identifier. Example: "FirstName" ### Referential Constraint
    FOREIGN KEY ( columnName [,...] )
    REFERENCES [ refTableName ] [ ( refColumnName [,...] ) ]
    [ ON DELETE referentialAction ] [ ON UPDATE referentialAction ]
`FOREIGN KEY ( columnName` |
| `, ...` |
| `)`
`REFERENCES` |
| `refTableName` |
`( refColumnName` |
| `, ...` |
| `)`
`ON DELETE referentialAction`
`ON UPDATE referentialAction`
Defines a referential constraint. If the table name is not specified, then the same table is referenced. `RESTRICT` is the default action. If the referenced columns are not specified, then the primary key columns are used. The required indexes are automatically created if required. Some tables may not be referenced, such as metadata tables. Example: FOREIGN KEY(ID) REFERENCES TEST(ID) ### Referential Action
    CASCADE | RESTRICT | NO ACTION | SET { DEFAULT | NULL }
| `CASCADE` |
| `RESTRICT` |
`NO ACTION`
`SET` |
| `DEFAULT` |
| `NULL` |
The action `CASCADE` will cause conflicting rows in the referencing (child) table to be deleted or updated. `RESTRICT` is the default action. As this database does not support deferred checking, `RESTRICT` and `NO ACTION` will both throw an exception if the constraint is violated. The action `SET DEFAULT` will set the column in the referencing (child) table to the default value, while `SET NULL` will set it to `NULL`. Example: FOREIGN KEY(ID) REFERENCES TEST(ID) ON UPDATE CASCADE ### Script Compression Encryption
    [ COMPRESSION { DEFLATE | LZF | ZIP | GZIP } ]
    [ CIPHER cipher PASSWORD string ]
`COMPRESSION` |
| `DEFLATE` |
| `LZF` |
| `ZIP` |
| `GZIP` |
`CIPHER cipher PASSWORD string`
The compression and encryption algorithm to use for script files. When using encryption, only `DEFLATE` and `LZF` are supported. `LZF` is faster but uses more space. Example: COMPRESSION LZF ### Row value expression
    ROW (expression, [,...])
    | ( [ expression, expression [,...] ] )
    | expression
`ROW ( expression ,` |
| `, ...` |
| `)`
`(` |
`expression , expression` |
| `, ...` |
| `)`
| `expression` |
A row value expression. Example: ROW (1) (1, 2) 1 ### Select Expression
    wildcardExpression | expression [ [ AS ] columnAlias ]
| `wildcardExpression` |
`expression` |
| `AS` |
| `columnAlias`
An expression in a `SELECT` statement. Example: ID AS VALUE ### Sequence options
    [ START WITH long ]
    [ INCREMENT BY long ]
    [ MINVALUE long | NOMINVALUE | NO MINVALUE ]
    [ MAXVALUE long | NOMAXVALUE | NO MAXVALUE ]
    [ CYCLE long | NOCYCLE | NO CYCLE ]
    [ CACHE long | NOCACHE | NO CACHE ]
`START WITH long`
`INCREMENT BY long`
`MINVALUE long`
| `NOMINVALUE` |
`NO MINVALUE`
`MAXVALUE long`
| `NOMAXVALUE` |
`NO MAXVALUE`
`CYCLE long`
| `NOCYCLE` |
`NO CYCLE`
`CACHE long`
| `NOCACHE` |
`NO CACHE`
Options of a sequence. Example: START WITH 1 ### Set clause list
    { { columnName = { DEFAULT | expression } } [,...] }
    | { ( columnName [,...] ) = {rowValueExpression|(select)} }
`columnName =` |
| `DEFAULT` |
| `expression` |
| `, ...` |
`( columnName` |
| `, ...` |
| `) =` |
| `rowValueExpression` |
`( select )`
List of `SET` clauses. Example: NAME = 'Test', VALUE = 2 (A, B) = (1, 2) (A, B) = (SELECT X, Y FROM OTHER T2 WHERE T1.ID = T2.ID) ### Summand
    factor [ { { + | - } factor } [...] ]
`factor` |
| `+` |
| `-` |
| `factor` |
| `...` |
A value or a numeric sum. Please note the text concatenation operator is `||`. Example: ID + 20 ### Table Expression
    { [ schemaName. ] tableName | ( select ) | valuesExpression }
    [ [ AS ] newTableAlias [ ( columnName [,...] ) ] ]
    [ USE INDEX ([ indexName [,...] ]) ]
    [ { { LEFT | RIGHT } [ OUTER ] | [ INNER ] | CROSS | NATURAL }
    JOIN tableExpression [ ON expression ] ]
`schemaName .`
| `tableName`
`( select )`
| `valuesExpression` |
| `AS` |
| `newTableAlias` |
`( columnName` |
| `, ...` |
| `)`
`USE INDEX (` |
`indexName` |
| `, ...` |
| `)`
| `LEFT` |
| `RIGHT` |
| `OUTER` |
| `INNER` |
| `CROSS` |
| `NATURAL` |
| `JOIN tableExpression` |
`ON expression`
Joins a table. The join expression 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: TEST AS T LEFT JOIN TEST AS T1 ON T.ID = T1.ID ### Within group specification
    WITHIN GROUP (ORDER BY {expression [ASC|DESC]} [,...])
`WITHIN GROUP ( ORDER BY expression` |
| `ASC` |
| `DESC` |
| `, ...` |
| `)`
Group specification for ordered set functions. Example: WITHIN GROUP (ORDER BY ID DESC) ### Wildcard expression
    {* | tableAlias.*} [EXCEPT ([tableAlias.]columnName, [,...])]
| `*` |
`tableAlias . *`
`EXCEPT (` |
`tableAlias .`
| `columnName ,` |
| `, ...` |
| `)`
A wildcard expression in a `SELECT` statement. A wildcard expression represents all visible columns. Some columns can be excluded with optional `EXCEPT` clause. Example: * * EXCEPT (DATA) ### Window name or specification
    windowName | windowSpecification
| `windowName` |
| `windowSpecification` |
A window name or inline specification for a window function or aggregate. Window functions in H2 may require a lot of memory for large queries. Example: W1 (ORDER BY ID) ### Window specification
    ([existingWindowName]
    [PARTITION BY expression [,...]] [ORDER BY order [,...]]
    [windowFrame])
`(` |
| `existingWindowName` |
`PARTITION BY expression` |
| `, ...` |
`ORDER BY order` |
| `, ...` |
| `windowFrame` |
| `)`
A window specification for a window, window function or aggregate. If name of an existing window is specified its clauses are used by default. Optional window partition clause separates rows into independent partitions. Each partition is processed separately. If this clause is not present there is one implicit partition with all rows. Optional window order clause specifies order of rows in the partition. If some rows have the same order position they are considered as a group of rows in optional window frame clause. Optional window frame clause specifies which rows are processed by a window function, see its documentation for a more details. Example: () (W1 ORDER BY ID) (PARTITION BY CATEGORY) (PARTITION BY CATEGORY ORDER BY NAME, ID) (ORDER BY Y RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) ### Window frame
    ROWS|RANGE|GROUP
    {windowFramePreceding|BETWEEN windowFrameBound AND windowFrameBound}
    [EXCLUDE {CURRENT ROW|GROUP|TIES|NO OTHERS}]
| `ROWS` |
| `RANGE` |
| `GROUP` |
| `windowFramePreceding` |
`BETWEEN windowFrameBound AND windowFrameBound`
`EXCLUDE` |
`CURRENT ROW`
| `GROUP` |
| `TIES` |
`NO OTHERS`
A window frame clause. May be specified only for aggregates and `FIRST_VALUE`(), `LAST_VALUE`(), and `NTH_VALUE`() window functions. If this clause is not specified for an aggregate or window function that supports this clause the default window frame depends on window order clause. If window order clause is also not specified the default window frame contains all the rows in the partition. If window order clause is specified the default window frame contains all preceding rows and all rows from the current group. Window frame unit determines how rows or groups of rows are selected and counted. If `ROWS` is specified rows are not grouped in any way and relative numbers of rows are used in bounds. If `RANGE` is specified rows are grouped according window order clause, preceding and following values mean the difference between value in the current row and in the target rows, and `CURRENT ROW` in bound specification means current group of rows. If `GROUPS` is specified rows are grouped according window order clause, preceding and following values means relative number of groups of rows, and `CURRENT ROW` in bound specification means current group of rows. If only window frame preceding clause is specified it is treated as `BETWEEN` windowFramePreceding `AND CURRENT ROW`. Optional window frame exclusion clause specifies rows that should be excluded from the frame. `EXCLUDE CURRENT ROW` excludes only the current row regardless the window frame unit. `EXCLUDE GROUP` excludes the whole current group of rows, including the current row. `EXCLUDE TIES` excludes the current group of rows, but not the current row. `EXCLUDE NO OTHERS` is default and it does not exclude anything. Example: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES ### Window frame preceding
    UNBOUNDED PRECEDING|value PRECEDING|CURRENT ROW
`UNBOUNDED PRECEDING`
`value PRECEDING`
`CURRENT ROW`
A window frame preceding clause. If value is specified it should not be negative. Example: UNBOUNDED PRECEDING 1 PRECEDING CURRENT ROW ### Window frame bound
    UNBOUNDED PRECEDING|value PRECEDING|CURRENT ROW
    |value FOLLOWING|UNBOUNDED FOLLOWING
`UNBOUNDED PRECEDING`
`value PRECEDING`
`CURRENT ROW`
`value FOLLOWING`
`UNBOUNDED FOLLOWING`
A window frame bound clause. If value is specified it should not be negative. Example: UNBOUNDED PRECEDING UNBOUNDED FOLLOWING 1 FOLLOWING CURRENT ROW ### Term
    value
    | column
    | ?[ int ]
    | NEXT VALUE FOR sequenceName
    | function
    | { - | + } term
    | ( expression )
    | select
    | case
    | caseWhen
    | userDefinedFunctionName
| `value` |
| `column` |
`?` |
| `int` |
`NEXT VALUE FOR sequenceName`
| `Function` |
| `-` |
| `+` |
| `term`
`( expression )`
| `select` |
| `case` |
| `caseWhen` |
| `userDefinedFunctionName` |
A value. Parameters can be indexed, for example `?1` meaning the first parameter. Example: 'Hello' ### 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