SQL Grammar
Index
Literals
Datetime fields
Other Grammar
Details
Click on the header to switch between railroad diagram and BNF.
Literals
Value
A literal value of any data type, or null.
Example:
10
Array
ARRAY [ |
| ] |
An array of values.
Example:
ARRAY[1, 2]
ARRAY[1]
ARRAY[]
Boolean
TRUE | ||
FALSE |
A boolean value.
Example:
TRUE
Bytes
X ' hex ' |
A binary value. The hex value is not case sensitive.
Example:
X'01FF'
Date
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
A literal value of any date-time data type.
Example:
TIMESTAMP '1999-01-31 10:00:00'
Decimal
A decimal number with fixed precision and scale. Internally, java.lang.BigDecimal is used. To ensure the floating point representation is used, use CAST(X AS DOUBLE). There are some special decimal values: to represent positive infinity, use POWER(0, -1); for negative infinity, use (-POWER(0, -1)); for -0.0, use (-CAST(0 AS DOUBLE)); for NaN (not a number), use SQRT(-1).
Example:
SELECT -1600.05
SELECT CAST(0 AS DOUBLE)
SELECT -1.4e-10
Dollar Quoted String
$ $ anything $ $ |
A string starts and ends with two dollar signs. Two dollar signs are not allowed within the text. A whitespace is required before the first set of dollar signs. No escaping is required within the text.
Example:
$$John's car$$
Hex Number
| 0x hex |
A number written in hexadecimal notation.
Example:
0xff
Int
| number |
The maximum integer number is 2147483647, the minimum is -2147483648.
Example:
10
Long
| number |
Long numbers are between -9223372036854775808 and 9223372036854775807.
Example:
100000
Null
NULL
NULL is a value without data type and means 'unknown value'.
Example:
NULL
Number
digit |
|
The maximum length of the number depends on the data type used.
Example:
100
Numeric
The data type of a numeric value is always the lowest possible for the given value. If the number contains a dot this is decimal; otherwise it is int, long, or decimal (depending on the value).
Example:
SELECT -1600.05
SELECT CAST(0 AS DOUBLE)
SELECT -1.4e-10
String
' anything ' |
A string starts and ends with a single quote. Two single quotes can be used to create a single quote inside a string.
Example:
'John''s car'
Time
TIME |
| ' 12:00:00 |
| ' |
A time literal. A value is between 0:00:00 and 23:59:59.999999999 and has nanosecond resolution.
Example:
TIME '23:59:59'
Timestamp
TIMESTAMP |
| ' 2000-01-01 12:00:00 |
| ' |
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 ' 2000-01-01 12:00:00 |
|
| ' |
A timestamp with time zone literal. If name of time zone is specified it will be converted to time zone offset.
Example:
TIMESTAMP WITH TIME ZONE '2005-12-31 23:59:59Z'
TIMESTAMP WITH TIME ZONE '2005-12-31 23:59:59-10:00'
TIMESTAMP WITH TIME ZONE '2005-12-31 23:59:59.123+05'
TIMESTAMP WITH TIME ZONE '2005-12-31 23:59:59.123456789 Europe/London'
Interval
An interval literal.
Example:
INTERVAL '1-2' YEAR TO MONTH
INTERVAL YEAR
INTERVAL |
| ' |
| yearInt ' YEAR |
An INTERVAL YEAR literal.
Example:
INTERVAL '10' YEAR
INTERVAL MONTH
INTERVAL |
| ' |
| monthInt ' MONTH |
An INTERVAL MONTH literal.
Example:
INTERVAL '10' MONTH
INTERVAL DAY
INTERVAL |
| ' |
| dayInt ' DAY |
An INTERVAL DAY literal.
Example:
INTERVAL '10' DAY
INTERVAL HOUR
INTERVAL |
| ' |
| hourInt ' HOUR |
An INTERVAL HOUR literal.
Example:
INTERVAL '10' HOUR
INTERVAL MINUTE
INTERVAL |
| ' |
| minuteInt ' MINUTE |
An INTERVAL MINUTE literal.
Example:
INTERVAL '10' MINUTE
INTERVAL SECOND
INTERVAL |
| ' |
| secondInt |
| ' SECOND |
An INTERVAL SECOND literal.
Example:
INTERVAL '10.123' SECOND
INTERVAL YEAR TO MONTH
An INTERVAL YEAR TO MONTH literal.
Example:
INTERVAL '1-6' YEAR TO MONTH
INTERVAL DAY TO HOUR
An INTERVAL DAY TO HOUR literal.
Example:
INTERVAL '10 11' DAY TO HOUR
INTERVAL DAY TO MINUTE
INTERVAL |
| ' |
| dayInt 12:00:00 ' DAY TO MINUTE |
An INTERVAL DAY TO MINUTE literal.
Example:
INTERVAL '10 11:12' DAY TO MINUTE
INTERVAL DAY TO SECOND
INTERVAL |
| ' |
| dayInt 12:00:00 |
| ' DAY TO SECOND |
An INTERVAL DAY TO SECOND literal.
Example:
INTERVAL '10 11:12:13.123' DAY TO SECOND
INTERVAL HOUR TO MINUTE
INTERVAL |
| ' |
| 12:00:00 ' HOUR TO MINUTE |
An INTERVAL HOUR TO MINUTE literal.
Example:
INTERVAL '10:11' HOUR TO MINUTE
INTERVAL HOUR TO SECOND
INTERVAL |
| ' |
| 12:00:00 |
| ' HOUR TO SECOND |
An INTERVAL HOUR TO SECOND literal.
Example:
INTERVAL '10:11:12.123' HOUR TO SECOND
INTERVAL MINUTE TO SECOND
INTERVAL |
| ' |
| 12:00:00 |
| ' MINUTE TO SECOND |
An INTERVAL MINUTE TO SECOND literal.
Example:
INTERVAL '11:12.123' MINUTE TO SECOND
Datetime fields
Datetime field
Fields for EXTRACT, DATEADD, and DATEDIFF functions.
Example:
YEAR
Year field
YEAR | ||
YYYY | ||
YY | ||
SQL_TSI_YEAR |
Year.
Example:
YEAR
Month field
MONTH | ||
MM | ||
M | ||
SQL_TSI_MONTH |
Month (1-12).
Example:
MONTH
Day of month field
DAY | ||
DD | ||
D | ||
SQL_TSI_DAY |
Day of month (1-31).
Example:
DAY
Hour field
HOUR | ||
HH | ||
SQL_TSI_HOUR |
Hour (0-23).
Example:
HOUR
Minute field
MINUTE | ||
MI | ||
N | ||
SQL_TSI_MINUTE |
Minute (0-59).
Example:
MINUTE
Second field
SECOND | ||
SS | ||
S | ||
SQL_TSI_SECOND |
Second (0-59).
Example:
SECOND
Millisecond field
MILLISECOND | ||
MS |
Millisecond (0-999).
Example:
MILLISECOND
Microsecond field
MICROSECOND | ||
MCS |
Microsecond (0-999999).
Example:
MICROSECOND
Nanosecond field
NANOSECOND | ||
NS |
Nanosecond (0-999999999).
Example:
NANOSECOND
Timezone hour field
TIMEZONE_HOUR
Timezone hour (from -18 to +18).
Example:
TIMEZONE_HOUR
Timezone minute field
TIMEZONE_MINUTE
Timezone minute (from -59 to +59).
Example:
TIMEZONE_MINUTE
Day of week field
DAY_OF_WEEK | ||
DAYOFWEEK | ||
DOW |
Day of week (1-7). Sunday is 1.
Example:
DAY_OF_WEEK
ISO week year field
ISO_YEAR | ||
ISOYEAR |
Returns the ISO week year from a date/time value.
Example:
ISO_YEAR
ISO day of week field
ISO_DAY_OF_WEEK | ||
ISODOW |
ISO day of week (1-7). Monday is 1.
Example:
ISO_DAY_OF_WEEK
Week of year field
WEEK | ||
WW | ||
W | ||
SQL_TSI_WEEK |
Week of year (1-53). EXTRACT function uses local rules to get number of week in year. DATEDIFF function uses Sunday as a first day of week.
Example:
WEEK
ISO week of year field
ISO_WEEK
ISO week of year (1-53). ISO definition is used when first week of year should have at least four days and week is started with Monday.
Example:
ISO_WEEK
Quarter field
QUARTER
Quarter (1-4).
Example:
QUARTER
Day of year field
DAYOFYEAR | ||
DAY_OF_YEAR | ||
DOY | ||
DY |
Day of year (1-366).
Example:
DAYOFYEAR
Epoch field
EPOCH
For TIMESTAMP values number of seconds since 1970-01-01 00:00:00 in local time zone. For TIMESTAMP WITH TIME ZONE values number of seconds since 1970-01-01 00:00:00 in UTC time zone. For DATE values number of seconds since 1970-01-01. For TIME values number of seconds since midnight.
Example:
EPOCH
Other Grammar
Alias
An alias is a name that is only valid in the context of the statement.
Example:
A
And Condition
Value or condition.
Example:
ID=1 AND NAME='Hi'
Case
Returns the first expression where the value is equal to the test expression. If no else part is specified, return NULL.
Example:
CASE CNT WHEN 0 THEN 'No' WHEN 1 THEN 'One' ELSE 'Some' END
Case When
Returns the first expression where the condition is true. If no else part is specified, return NULL.
Example:
CASE WHEN CNT<10 THEN 'Low' ELSE 'High' END
Cipher
AES
Only the algorithm AES (AES-128) is supported currently.
Example:
AES
Column Definition
dataType |
|
|
|
|
|
|
|
|
|
Default expressions are used if no explicit value was used when adding a row. The computed column expression is evaluated and assigned whenever the row changes. On update column expression is used if row is updated, at least one column have a new value that is different from its previous value and value for this column is not set explicitly in update statement.
Identity, auto-increment, or generated as identity columns are columns with a sequence as the default. The column declared as the identity columns with IDENTITY data type or with IDENTITY () clause is implicitly the primary key column of this table. AUTO_INCREMENT and GENERATED clauses do not create the primary key constraint. GENERATED ALWAYS is accepted 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
| |||
| |||
|
Comments can be used anywhere in a command and are ignored by the database. Line comments end with a newline. Block comments cannot be nested, but can be multiple lines long.
Example:
// This is a comment
Compare
| |||
| |||
| |||
= | |||
< | |||
> | |||
| |||
&& |
Comparison operator. The operator != is the same as <>. The operator && means overlapping; it can only be used with geometry types.
Example:
<>
Condition
| ||||||||||
| ||||||||||
|
Boolean value or condition.
Example:
ID<>2
Condition Right Hand Side
| |||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||
|
The right hand side of a condition.
Quantified comparison predicate ALL returns TRUE if specified comparison operation between left size of condition and each row from a subquery returns TRUE, including case when there are no rows. ALL predicate returns FALSE if at least one such comparison returns FALSE. Otherwise it returns NULL.
Quantified comparison predicates ANY and SOME return TRUE if specified comparison operation between left size of condition and at least one row from a subquery returns TRUE. ANY and SOME predicates return FALSE if all such comparisons return FALSE. Otherwise it returns NULL. Note that these predicates have priority over ANY and SOME aggregate functions with subquery on the right side. Use parentheses around aggregate function.
The conditions IS [ NOT ] 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 |
| |||||||||||||||||||
| |||||||||||||||||||
referentialConstraint | |||||||||||||||||||
|
Defines a constraint. The check condition must evaluate to TRUE, FALSE or NULL. TRUE and NULL mean the operation is to be permitted, and FALSE means the operation is to be rejected. To prevent NULL in a column, use NOT NULL instead of a check constraint.
Example:
PRIMARY KEY(ID, NAME)
Constraint Name Definition
CONSTRAINT |
| newConstraintName |
Defines a constraint name.
Example:
CONSTRAINT CONST_ID
Csv Options
Optional parameters for CSVREAD and CSVWRITE. Instead of setting the options one by one, all options can be combined into a space separated key-value pairs, as follows: STRINGDECODE('charset=UTF-8 escape=\" fieldDelimiter=\" fieldSeparator=, ' || 'lineComment=# lineSeparator=\n null= rowSeparator='). The following options are supported:
caseSensitiveColumnNames (true or false; disabled by default),
charset (for example 'UTF-8'),
escape (the character that escapes the field delimiter),
fieldDelimiter (a double quote by default),
fieldSeparator (a comma by default),
lineComment (disabled by default),
lineSeparator (the line separator used for writing; ignored for reading),
null, Support reading existing CSV files that contain explicit null delimiters. Note that an empty, unquoted values are also treated as null.
preserveWhitespace (true or false; disabled by default),
writeColumnHeader (true or false; enabled by default).
For a newline or other special character, use STRINGDECODE as in the example above. A space needs to be escaped with a backslash ('\ '), and a backslash needs to be escaped with another backslash ('\\'). All other characters are not to be escaped, that means newline and tab characters are written as such.
Example:
CALL CSVWRITE('test2.csv', 'SELECT * FROM TEST', 'charset=UTF-8 fieldSeparator=|');
Data Type
A data type definition.
Example:
INT
Digit
0-9
A digit.
Example:
0
Expression
andCondition |
|
Value or condition.
Example:
ID=1 OR NAME='Hi'
Factor
A value or a numeric factor.
Example:
ID * 10
Hex
The hexadecimal representation of a number or of bytes. Two characters are one byte.
Example:
cafe
Index Column
columnName |
|
|
Indexes this column in ascending or descending order. Usually it is not required to specify the order; however doing so will speed up large queries that order the column in the same way.
Example:
NAME
Insert columns and source
| ||||||||||||||||||||||||||||||||||||||||||||
|
Names of columns and their values for INSERT statement.
Example:
(ID, NAME) VALUES (1, 'Test')
Insert values
VALUES |
| , |
|
Values for INSERT statement.
Example:
VALUES (1, 'Test')
Merge when clause
WHEN MATCHED or WHEN NOT MATCHED clause for MERGE USING command.
Example:
WHEN MATCHED THEN DELETE
Merge when matched clause
WHEN MATCHED |
| THEN |
| ||||||||||||||||||||
DELETE | ||||||||||||||||||||
|
WHEN MATCHED clause for MERGE USING command.
If both UPDATE and DELETE are specified, DELETE can delete only rows that were updated, WHERE condition in DELETE clause can be used to specify which updated rows should be deleted. This condition checks values in updated row.
Example:
WHEN MATCHED THEN UPDATE SET VALUE = S.VALUE
WHEN MATCHED THEN DELETE
Merge when not matched clause
WHEN NOT MATCHED |
| THEN INSERT insertColumnsAndSource |
WHEN NOT MATCHED clause for MERGE USING command.
Example:
WHEN NOT MATCHED THEN INSERT (ID, NAME) VALUES (S.ID, S.NAME)
Name
| ||||||||||||||||||||||||
quotedName |
Names are not case sensitive. There is no maximum name length.
Example:
TEST
Operand
A value or a concatenation of values. In the default mode, the result is NULL if either parameter is NULL.
Example:
'Hi' || ' Eva'
Order
|
|
|
Sorts the result by the given column number, or by an expression. If the expression is a single parameter, then the value is interpreted as a column number. Negative column numbers reverse the sort order.
Example:
NAME DESC NULLS LAST
Quoted Name
" 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 |
|
|
|
|
Defines a referential constraint. If the table name is not specified, then the same table is referenced. RESTRICT is the default action. If the referenced columns are not specified, then the primary key columns are used. The required indexes are automatically created if required. Some tables may not be referenced, such as metadata tables.
Example:
FOREIGN KEY(ID) REFERENCES TEST(ID)
Referential Action
CASCADE | ||||||||||
RESTRICT | ||||||||||
| ||||||||||
|
The action CASCADE will cause conflicting rows in the referencing (child) table to be deleted or updated. RESTRICT is the default action. As this database does not support deferred checking, RESTRICT and NO ACTION will both throw an exception if the constraint is violated. The action SET DEFAULT will set the column in the referencing (child) table to the default value, while SET NULL will set it to NULL.
Example:
FOREIGN KEY(ID) REFERENCES TEST(ID) ON UPDATE CASCADE
Script Compression Encryption
The compression and encryption algorithm to use for script files. When using encryption, only DEFLATE and LZF are supported. LZF is faster but uses more space.
Example:
COMPRESSION LZF
Row value expression
| |||||||||||||||||||
| |||||||||||||||||||
expression |
A row value expression.
Example:
ROW (1)
(1, 2)
1
Select Expression
An expression in a SELECT statement.
Example:
ID AS VALUE
Sequence options
|
|
|
|
|
|
Options of a sequence.
Example:
START WITH 1
Set clause list
| |||||||||||||||||||
|
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
A value or a numeric sum.
Please note the text concatenation operator is ||.
Example:
ID + 20
Table Expression
| |||||||||||
| |||||||||||
valuesExpression |
|
|
|
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 |
|
| ) |
Group specification for ordered set functions.
Example:
WITHIN GROUP (ORDER BY ID DESC)
Wildcard expression
|
|
A wildcard expression in a SELECT statement. A wildcard expression represents all visible columns. Some columns can be excluded with optional EXCEPT clause.
Example:
*
* EXCEPT (DATA)
Window name or specification
A window name or inline specification for a window function or aggregate.
Window functions in H2 may require a lot of memory for large queries.
Example:
W1
(ORDER BY ID)
Window specification
A window specification for a window, window function or aggregate.
If name of an existing window is specified its clauses are used by default.
Optional window partition clause separates rows into independent partitions. Each partition is processed separately. If this clause is not present there is one implicit partition with all rows.
Optional window order clause specifies order of rows in the partition. If some rows have the same order position they are considered as a group of rows in optional window frame clause.
Optional window frame clause specifies which rows are processed by a window function, see its documentation for a more details.
Example:
()
(W1 ORDER BY ID)
(PARTITION BY CATEGORY)
(PARTITION BY CATEGORY ORDER BY NAME, ID)
(ORDER BY Y RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES)
Window frame
ROWS | ||
RANGE | ||
GROUP |
windowFramePreceding | |||
|
|
A window frame clause. May be specified only for aggregates and FIRST_VALUE(), LAST_VALUE(), and NTH_VALUE() window functions.
If this clause is not specified for an aggregate or window function that supports this clause the default window frame depends on window order clause. If window order clause is also not specified the default window frame contains all the rows in the partition. If window order clause is specified the default window frame contains all preceding rows and all rows from the current group.
Window frame unit determines how rows or groups of rows are selected and counted. If ROWS is specified rows are not grouped in any way and relative numbers of rows are used in bounds. If RANGE is specified rows are grouped according window order clause, preceding and following values mean the difference between value in the current row and in the target rows, and CURRENT ROW in bound specification means current group of rows. If GROUPS is specified rows are grouped according window order clause, preceding and following values means relative number of groups of rows, and CURRENT ROW in bound specification means current group of rows.
If only window frame preceding clause is specified it is treated as BETWEEN windowFramePreceding AND CURRENT ROW.
Optional window frame exclusion clause specifies rows that should be excluded from the frame. EXCLUDE CURRENT ROW excludes only the current row regardless the window frame unit. EXCLUDE GROUP excludes the whole current group of rows, including the current row. EXCLUDE TIES excludes the current group of rows, but not the current row. EXCLUDE NO OTHERS is default and it does not exclude anything.
Example:
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES
Window frame preceding
| |||
| |||
|
A window frame preceding clause. If value is specified it should not be negative.
Example:
UNBOUNDED PRECEDING
1 PRECEDING
CURRENT ROW
Window frame bound
A window frame bound clause. If value is specified it should not be negative.
Example:
UNBOUNDED PRECEDING
UNBOUNDED FOLLOWING
1 FOLLOWING
CURRENT ROW
Term
value | ||||||||||
column | ||||||||||
| ||||||||||
| ||||||||||
Function | ||||||||||
| ||||||||||
| ||||||||||
select | ||||||||||
case | ||||||||||
caseWhen | ||||||||||
userDefinedFunctionName |
A value. Parameters can be indexed, for example ?1 meaning the first parameter.
Example:
'Hello'
Column
|
|
A column name with optional table alias and schema. _ROWID_ can be used to access unique row identifier.
Example:
ID