The existing implementation of the SQL parser is a hand-written, recursive descent parser. There are great advantages of this approach over formal grammar-based, generated parsers (e.g. by using ANTLR). These advantages are, among others:
They can be tuned easily for performance
They are very simple and easy to maintain
It's easy to implement corner cases of the grammar, which might require context (that's a big plus with SQL)
It's the easiest way to bind a grammar to an existing backing expression tree implementation (which is what jOOQ really is)
Nevertheless, there is a grammar available for documentation purposes and it is included in the manual here:
batch ::=
;
query
query ::=
ddlStatement
dmlStatement
blockStatement
ddlStatement ::=
alterTableStatement
alterIndexStatement
alterSchemaStatement
alterSequenceStatement
alterSessionStatement
alterTypeStatement
alterViewStatement
commentStatement
createTableStatement
createTypeStatement
createIndexStatement
createSchemaStatement
createSequenceStatement
createViewStatement
dropTableStatement
dropTypeStatement
dropIndexStatement
dropViewStatement
dropSequenceStatement
dropSchemaStatement
grantStatement
renameStatement
revokeStatement
setCatalogStatement
setSchemaStatement
truncateStatement
useStatement
dmlStatement ::=
deleteStatement
insertStatement
mergeStatement
selectStatement
updateStatement
proceduralStatements ::=
proceduralStatement
;
proceduralStatement ::=
label
query
declareStatement
assignmentStatement
ifStatement
labelReference
caseStatement
labelReference
loopStatement
labelReference
forStatement
labelReference
whileStatement
labelReference
repeatStatement
labelReference
gotoStatement
continueStatement
exitStatement
nullStatement
label ::=
<<
identifier
>>
identifier
:
labelReference ::=
identifier
blockStatement ::=
EXECUTE BLOCK AS
DECLARE
declarationStatement
;
BEGIN
proceduralStatements
END
DO
stringLiteral
declarationStatement ::=
identifier
CONSTANT
dataType
NOT
NULL
=
:=
DEFAULT
field
declareStatement ::=
DECLARE
,
,
identifier
AS
dataType
=
DEFAULT
field
assignmentStatement ::=
SET
identifier
=
field
identifier
:=
field
ifStatement ::=
IF
condition
ifSimpleBody
ifBlockBody
ifSimpleBody ::=
proceduralStatement
ELSE
proceduralStatement
ifBlockBody ::=
THEN
proceduralStatements
ELSIF
ELSEIF
condition
THEN
proceduralStatement
ELSE
proceduralStatements
END IF
caseStatement ::=
CASE
field
WHEN
field
THEN
proceduralStatements
WHEN
condition
THEN
proceduralStatements
ELSE
proceduralStatements
END CASE
loopStatement ::=
LOOP
proceduralStatements
END LOOP
forStatement ::=
FOR
identifier
IN
REVERSE
int
..
int
BY
int
loopStatement
whileStatement ::=
WHILE
condition
loopStatement
DO
proceduralStatements
END WHILE
repeatStatement ::=
REPEAT
proceduralStatements
UNTIL
condition
END REPEAT
gotoStatement ::=
GOTO
identifier
continueStatement ::=
CONTINUE
ITERATE
identifier
WHEN
condition
exitStatement ::=
EXIT
LEAVE
identifier
WHEN
condition
nullStatement ::=
NULL
alterTableStatement ::=
ALTER TABLE
IF EXISTS
ONLY
tableName
ADD
constraint
CONSTRAINT
constraintName
constraint
COLUMN
IF NOT EXISTS
column
FIRST
BEFORE
columnName
AFTER
columnName
(
,
CONSTRAINT
constraintName
constraint
constraint
column
)
ALTER
MODIFY
COLUMN
identifier
SET DATA
TYPE
dataType
NOT
NULL
DROP NOT NULL
SET
NOT
NULL
SET DEFAULT
concat
DROP DEFAULT
RENAME
TO
AS
identifier
column
(
column
)
CONSTRAINT
constraintName
constraintEnforcement
COMMENT
=
stringLiteral
DROP
COLUMN
IF EXISTS
identifier
CASCADE
RESTRICT
CONSTRAINT
IF EXISTS
constraintName
CASCADE
RESTRICT
UNIQUE
constraintName
(
sortFields
)
CASCADE
RESTRICT
FOREIGN KEY
constraintName
PRIMARY KEY
constraintName
CASCADE
RESTRICT
RENAME
COLUMN
INDEX
CONSTRAINT
identifier
TO
AS
identifier
OWNER
TO
userName
alterIndexStatement ::=
ALTER INDEX
IF EXISTS
indexName
RENAME
TO
AS
indexName
alterSchemaStatement ::=
ALTER SCHEMA
IF EXISTS
schemaName
RENAME
TO
AS
schemaName
OWNER
TO
userName
alterSequenceStatement ::=
ALTER SEQUENCE
IF EXISTS
sequenceName
RENAME
TO
AS
sequenceName
OWNER
TO
userName
RESTART
WITH
uint
START
WITH
=
int
INCREMENT
BY
=
int
MINVALUE
=
int
NO MINVALUE
NOMINVALUE
MAXVALUE
=
int
NO MAXVALUE
NOMAXVALUE
CYCLE
NO CYCLE
NOCYCLE
CACHE
=
uint
NO CACHE
NOCACHE
SET GENERATOR
sequenceName
TO
uint
alterSessionStatement ::=
ALTER SESSION
SET CURRENT_SCHEMA
=
schemaName
alterTypeStatement ::=
ALTER TYPE
typeName
RENAME TO
typeName
SET SCHEMA
schemaName
ADD VALUE
stringLiteral
RENAME VALUE
stringLiteral
TO
stringLiteral
OWNER
TO
userName
alterViewStatement ::=
ALTER VIEW
IF EXISTS
tableName
RENAME
TO
AS
tableName
OWNER
TO
userName
createTableStatement ::=
CREATE
GLOBAL
TEMP
TEMPORARY
TABLE
IF NOT EXISTS
tableName
(
identifiers
)
AS
select
(
,
CONSTRAINT
constraintName
constraint
constraint
index
column
)
ON COMMIT
DELETE ROWS
DROP
PRESERVE ROWS
COMMENT
=
stringLiteral
createTypeStatement ::=
CREATE TYPE
typeName
AS ENUM
(
,
stringLiteral
)
createIndexStatement ::=
CREATE
UNIQUE
INDEX
IF NOT EXISTS
indexName
indexType
ON
tableName
(
sortFields
)
indexType
INCLUDE
COVERING
STORING
(
identifiers
)
WHERE
condition
createSchemaStatement ::=
CREATE SCHEMA
IF NOT EXISTS
schemaName
createSequenceStatement ::=
CREATE
SEQUENCE
GENERATOR
IF NOT EXISTS
sequenceName
START
WITH
=
int
INCREMENT
BY
=
int
MINVALUE
=
int
NO MINVALUE
NOMINVALUE
MAXVALUE
=
int
NO MAXVALUE
NOMAXVALUE
CYCLE
NO CYCLE
NOCYCLE
CACHE
=
uint
NO CACHE
NOCACHE
createViewStatement ::=
CREATE
OR
ALTER
REPLACE
FORCE
VIEW
IF NOT EXISTS
tableName
(
fieldNames
)
AS
select
dropTableStatement ::=
DROP
TEMPORARY
TABLE
IF EXISTS
tableName
CASCADE
RESTRICT
dropTypeStatement ::=
DROP TYPE
IF EXISTS
,
typeName
CASCADE
RESTRICT
dropIndexStatement ::=
DROP INDEX
IF EXISTS
indexName
ON
tableName
CASCADE
RESTRICT
dropViewStatement ::=
DROP VIEW
IF EXISTS
tableName
dropSequenceStatement ::=
DROP
SEQUENCE
GENERATOR
IF EXISTS
sequenceName
dropSchemaStatement ::=
DROP SCHEMA
IF EXISTS
schemaName
CASCADE
RESTRICT
renameStatement ::=
RENAME
COLUMN
fieldName
TO
AS
fieldName
INDEX
indexName
TO
AS
indexName
SCHEMA
schemaName
TO
AS
schemaName
SEQUENCE
sequenceName
TO
AS
sequenceName
VIEW
tableName
TO
AS
tableName
TABLE
tableName
TO
AS
tableName
setCatalogStatement ::=
SET CATALOG
catalogName
setSchemaStatement ::=
SET
CURRENT
SCHEMA
CURRENT SQLID
=
schemaName
stringLiteral
useStatement ::=
USE
catalogName
schemaName
truncateStatement ::=
TRUNCATE TABLE
tableName
CONTINUE IDENTITY
RESTART IDENTITY
CASCADE
RESTRICT
grantStatement ::=
GRANT
SELECT
INSERT
UPDATE
DELETE
ON
tableName
TO
userName
roleName
PUBLIC
WITH GRANT OPTION
revokeStatement ::=
REVOKE
GRANT OPTION FOR
SELECT
INSERT
UPDATE
DELETE
ON
tableName
FROM
userName
roleName
PUBLIC
selectStatement ::=
select
insertStatement ::=
with
INSERT
INS
INTO
tableName
(
select
)
AS
identifier
(
identifiers
)
values
DEFAULT VALUES
SET
setClauses
select
ON DUPLICATE KEY UPDATE
SET
setClauses
WHERE
condition
ON DUPLICATE KEY IGNORE
ON CONFLICT
ON CONSTRAINT
constraintName
(
fieldNames
)
DO
NOTHING
UPDATE
SET
setClauses
WHERE
condition
RETURNING
*
fields
values ::=
VALUES
,
(
fields
)
updateStatement ::=
with
UPDATE
UPD
topSimple
tableName
periodPortion
(
select
)
AS
identifier
SET
setClauses
FROM
tables
WHERE
condition
ORDER BY
sortFields
LIMIT
uint
RETURNING
*
fields
setClauses ::=
,
setClause
setClause ::=
fieldName
=
field
deleteStatement ::=
with
DELETE
DEL
topSimple
FROM
tableName
periodPortion
(
select
)
AS
identifier
USING
tables
WHERE
condition
ORDER BY
sortFields
LIMIT
uint
RETURNING
*
fields
mergeStatement ::=
with
MERGE
INTO
tableName
AS
identifier
USING
(
select
)
AS
identifier
ON
condition
WHEN
MATCHED
AND
condition
THEN
UPDATE
SET
setClauses
WHERE
condition
WHEN
NOT
MATCHED
AND
condition
THEN
INSERT
(
identifiers
)
VALUES
(
fields
)
WHERE
condition
column ::=
identifier
dataType
NOT
NULL
DEFAULT
ON NULL
concat
ON UPDATE
concat
GENERATED
ALWAYS
BY DEFAULT
ON NULL
AS IDENTITY
(
identity
)
CONSTRAINT
constraintName
PRIMARY KEY
UNIQUE
KEY
INDEX
CHECK
(
condition
)
REFERENCES
constraintReferenceSpecification
AUTO_INCREMENT
AUTOINCREMENT
IDENTITY
(
int
,
int
)
COMMENT
stringLiteral
index ::=
KEY
INDEX
identifier
indexType
(
sortFields
)
indexType
indexType ::=
USING
BTREE
HASH
constraint ::=
PRIMARY KEY
indexType
CLUSTERED
NONCLUSTERED
(
fieldNames
)
indexType
UNIQUE
KEY
INDEX
identifier
indexType
(
sortFields
)
indexType
FOREIGN KEY
(
fieldNames
)
REFERENCES
constraintReferenceSpecification
CHECK
(
condition
)
constraintEnforcement
constraintEnforcement ::=
ENABLE
DISABLE
NOT
ENFORCED
constraintReferenceSpecification ::=
tableName
(
fieldNames
)
ON
DELETE
UPDATE
CASCADE
NO ACTION
RESTRICT
SET DEFAULT
SET NULL
identity ::=
identityProperty
,
identityProperty
identityProperty ::=
START WITH
LIMIT VALUE
uint
INCREMENT BY
uint
MAXVALUE
uint
NOMAXVALUE
MINVALUE
uint
NOMINVALUE
CACHE
uint
NOCACHE
CYCLE
NOCYCLE
ORDER
NOORDER
with ::=
WITH
RECURSIVE
,
commonTableExpression
commonTableExpression ::=
identifier
(
identifiers
)
AS
(
select
)
select ::=
with
queryExpressionBody
orderBy
seekFetch
offsetFetch
forUpdate
values
queryExpressionBody ::=
queryTerm
UNION
EXCEPT
MINUS
ALL
DISTINCT
queryTerm
queryTerm ::=
queryPrimary
INTERSECT
ALL
DISTINCT
queryTerm
queryPrimary ::=
(
select
)
SELECT
SEL
distinct
top
selectList
INTO
tableName
tableExpression
distinct ::=
DISTINCT
UNIQUE
ON
(
fields
)
ALL
topSimple ::=
TOP
uint
PERCENT
top ::=
TOP
uint
PERCENT
START AT
uint
WITH TIES
SKIP
uint
FIRST
uint
selectList ::=
,
selectField
selectField ::=
*
EXCEPT
(
fields
)
tableName
.
*
EXCEPT
(
fields
)
field
AS
identifier
tableExpression ::=
FROM
tables
WHERE
condition
connectBy
groupBy
HAVING
condition
WINDOW
windows
QUALIFY
condition
connectBy ::=
START WITH
condition
CONNECT BY
NOCYCLE
condition
CONNECT BY
NOCYCLE
condition
START WITH
condition
groupBy ::=
GROUP BY
()
ROLLUP
(
fields
)
CUBE
(
fields
)
GROUPING SETS
(
,
groupingSet
)
fields
WITH ROLLUP
groupingSet ::=
(
fields
)
windows ::=
,
window
window ::=
identifier
AS
(
windowSpecification
)
windowSpecification ::=
PARTITION BY
fields
ORDER BY
sortFields
ROWS
RANGE
GROUPS
BETWEEN
rangeBound
AND
rangeBound
rangeBound
EXCLUDE
CURRENT ROW
TIES
GROUPS
NO OTHERS
orderBy ::=
ORDER
SIBLINGS
BY
sortFields
seekFetch ::=
SEEK
,
field
FETCH
FIRST
NEXT
uint
PERCENT
ROW
ROWS
ONLY
WITH TIES
LIMIT
uint
PERCENT
WITH TIES
offsetFetch ::=
OFFSET
uint
ROW
ROWS
FETCH
FIRST
NEXT
uint
PERCENT
ROW
ROWS
ONLY
WITH TIES
LIMIT
uint
PERCENT
WITH TIES
LIMIT
uint
PERCENT
WITH TIES
OFFSET
uint
,
uint
forUpdate ::=
FOR SHARE
FOR KEY SHARE
FOR NO KEY UPDATE
FOR UPDATE
OF
fields
NOWAIT
WAIT
uint
SKIP LOCKED
sortFields ::=
,
sortField
sortField ::=
field
ASC
DESC
NULLS FIRST
NULLS LAST
tables ::=
,
table
table ::=
lateral
unqualifiedJoin
innerJoin
outerJoin
semiAntiJoin
optionallyQualifiedJoin ::=
CROSS JOIN
table
joinQualification
lateral
unqualifiedJoin ::=
CROSS APPLY
OUTER APPLY
NATURAL
LEFT
RIGHT
FULL
OUTER
JOIN
lateral
innerJoin ::=
INNER
JOIN
STRAIGHT_JOIN
table
joinQualification
outerJoin ::=
LEFT
RIGHT
FULL
OUTER
JOIN
table
PARTITION BY
(
fields
)
joinQualification
semiAntiJoin ::=
LEFT
SEMI
ANTI
JOIN
table
joinQualification
lateral ::=
LATERAL
tableFactor
tableFactor
tableFactor ::=
tableName
versions
correlationName
tableHints
(
table
)
correlationName
(
select
)
correlationName
tableFunction
correlationName
UNNEST
(
field
)
values
correlationName
tableFunction ::=
GENERATE_SERIES
(
numericOp
,
numericOp
,
numericOp
)
tableHints ::=
WITH
(
,
tableHint
)
pivot ::=
todo
versions ::=
VERSIONS BETWEEN
SCN
TIMESTAMP
MINVALUE
field
AND
MAXVALUE
field
AS OF
SCN
TIMESTAMP
FOR
periodSpecification
periodSpecification ::=
periodName
AS OF
field
BETWEEN
field
AND
field
periodSpecificationFromTo
CONTAINED IN
(
field
,
field
)
ALL
periodPortion ::=
FOR PORTION OF
periodSpecificationFromTo
periodSpecificationFromTo ::=
FROM
field
TO
field
periodName ::=
name
joinQualification ::=
ON
condition
USING
(
identifiers
)
correlationName ::=
AS
identifier
(
identifiers
)
rowValueExpression ::=
ROW
(
fields
)
fields ::=
,
field
field ::=
or
condition ::=
or
or ::=
OR
and
and ::=
AND
not
not ::=
NOT
predicate
predicate ::=
EXISTS
(
select
)
UNIQUE
(
select
)
concat
comparator
ALL
ANY
SOME
(
select
)
concat
IS
NOT
NULL
JSON
DISTINCT FROM
concat
NOT
IN
(
select
fields
)
BETWEEN
SYMMETRIC
concat
AND
concat
REGEXP
RLIKE
LIKE_REGEX
concat
LIKE
ANY
ALL
(
,
concat
)
ESCAPE
characterLiteral
LIKE
ILIKE
concat
ESCAPE
characterLiteral
SIMILAR
TO
concat
ESCAPE
characterLiteral
@>
concat
concat
*=
concat
concat
=*
concat
row2
OVERLAPS
row2
REGEXP_LIKE
(
concat
,
concat
)
row2 ::=
ROW
(
field
,
field
)
concat ::=
||
collated
collated ::=
numericOp
COLLATE
collation
numericOp ::=
sum
<<
>>
sum
sum ::=
factor
+
-
factor
factor ::=
exp
*
/
%
MOD
exp
exp ::=
^
unaryOps
unaryOps ::=
+
-
CONNECT_BY_ROOT
term
(+)
::
dataType
term ::=
:
identifier
?
stringLiteral
ANY
(
field
)
filter
over
ACOS
(
numericOp
)
ARRAY_AGG
(
DISTINCT
field
ORDER BY
sortFields
)
filter
over
ASCII
(
field
)
ASIN
(
numericOp
)
ATAN
(
numericOp
)
ATN2
ATAN2
(
numericOp
,
numericOp
)
ARRAY
[
fields
]
AVG
(
DISTINCT
ALL
field
)
keep
filter
over
BIN_AND
(
field
,
field
)
BIN_SHL
(
field
,
field
)
BIN_SHR
(
field
,
field
)
BIN_OR
(
field
,
field
)
BIN_XOR
(
field
,
field
)
BITAND
(
field
,
field
)
BITOR
(
field
,
field
)
BITXOR
(
field
,
field
)
BIT_AND
(
field
,
field
)
BIT_COUNT
(
field
)
BIT_LENGTH
(
field
)
BIT_NAND
(
field
,
field
)
BIT_NOR
(
field
,
field
)
BIT_NOT
(
field
,
field
)
BIT_OR
(
field
,
field
)
BIT_XNOR
(
field
,
field
)
BITXOR
(
field
,
field
)
BIT_XOR
(
field
,
field
)
case
CAST
(
field
AS
castDataType
)
CEIL
CEILING
(
numericOp
)
CENTURY
(
field
)
CHARINDEX
(
field
,
field
)
CHAR_LENGTH
(
field
)
CHOOSE
(
,
field
)
COALESCE
(
fields
)
CONCAT
(
fields
)
CONNECT_BY_ISCYCLE
CONNECT_BY_ISLEAF
CONVERT
(
dataType
,
field
,
uint
)
COS
(
numericOp
)
COSH
(
numericOp
)
COT
(
numericOp
)
COTH
(
numericOp
)
COUNT
(
DISTINCT
ALL
tableName
.
*
field
rowValueExpression
)
keep
filter
over
CUME_DIST
(
)
over
(
fields
)
withinGroup
CURRVAL
(
name
stringLiteral
)
CURRENT
DATE
CURRENT_DATE
(
)
CURRENT
SCHEMA
CURRENT_SCHEMA
(
)
CURRENT
TIME
CURRENT_TIME
(
)
CURRENT
TIMESTAMP
CURRENT_TIMESTAMP
(
uint
)
CURRENT
USER
CURRENT_USER
(
)
CURDATE
(
)
CURTIME
(
)
dateLiteral
DATEADD
(
datePart
,
field
,
field
)
DATEDIFF
(
datePart
,
field
,
field
)
DATEPART
(
datePart
,
field
)
DATE_TRUNC
(
stringLiteral
,
field
)
DAY
(
field
)
DAYOFMONTH
(
field
)
DAYOFWEEK
(
field
)
DECADE
(
field
)
DECODE
(
field
,
field
,
,
field
)
DENSE_RANK
(
)
over
(
fields
)
withinGroup
DEG
DEGREE
DEGREES
(
numericOp
)
EPOCH
(
field
)
EXTRACT
(
datePart
FROM
field
)
EXP
(
numericOp
)
EVERY
(
field
)
filter
over
FLOOR
(
numericOp
)
FIELD
(
field
,
,
field
)
FIRST_VALUE
(
field
RESPECT NULLS
IGNORE NULLS
)
over
GETDATE
(
)
GREATEST
(
fields
)
GROUP_CONCAT
(
DISTINCT
field
ORDER BY
sortFields
SEPARATOR
stringLiteral
)
GROUP_ID
(
)
GROUPING_ID
(
fields
)
GROUPING
(
fields
)
HOUR
(
field
)
IFNULL
(
field
,
field
)
INSTR
(
field
,
field
)
intervalLiteral
IIF
(
condition
,
field
,
field
)
ISNULL
(
field
,
field
)
JSON_ARRAY
(
fields
)
JSON_OBJECT
(
jsonEntries
)
LOWER
LCASE
(
field
)
LPAD
(
field
,
field
,
field
)
LTRIM
(
field
,
field
)
LEFT
(
field
,
field
)
LEN
(
field
)
LENGTH
(
field
)
LN
(
numericOp
)
LOG
(
numericOp
,
numericOp
)
LEVEL
LEAST
(
fields
)
LEAD
(
field
,
uint
,
field
RESPECT NULLS
IGNORE NULLS
)
over
LAG
(
field
,
uint
,
field
RESPECT NULLS
IGNORE NULLS
)
over
LAST_VALUE
(
field
RESPECT NULLS
IGNORE NULLS
)
over
LISTAGG
(
field
,
stringLiteral
)
withinGroup
over
MAX
(
DISTINCT
ALL
field
)
keep
filter
over
MEDIAN
(
field
)
filter
over
MICROSECOND
(
field
)
MILLISECOND
(
field
)
MIN
(
DISTINCT
ALL
field
)
keep
filter
over
MOD
(
field
,
field
)
MODE
(
)
withinGroup
over
MONTH
(
field
)
MINUTE
(
field
)
MID
(
field
,
field
,
field
)
MD5
(
field
)
NEXT VALUE FOR
sequenceName
NEXTVAL
(
name
stringLiteral
)
NOW
(
uint
)
NTH_VALUE
(
field
,
uint
FROM FIRST
FROM LAST
RESPECT NULLS
IGNORE NULLS
)
over
NTILE
(
uint
)
over
NULLIF
(
field
,
field
)
NVL
(
field
,
field
)
NVL2
(
field
,
field
,
field
)
OCTET_LENGTH
(
field
)
PI
(
)
POSITION
(
field
IN
field
)
PERCENT_RANK
(
)
over
(
fields
)
withinGroup
PERCENTILE_CONT
PERCENTILE_DISC
(
unsignedNumericLiteral
)
withinGroup
over
POW
POWER
(
field
,
field
)
PRIOR
concat
PRODUCT
(
DISTINCT
ALL
field
)
keep
filter
over
QUARTER
(
field
)
REGR_SLOPE
REGR_INTERCEPT
REGR_COUNT
REGR_R2
REGR_AVGX
REGR_AVGY
REGR_SXX
REGR_SYY
REGR_SXY
(
numericOp
,
numericOp
)
RAD
RADIAN
RADIANS
(
numericOp
)
RANK
(
)
over
(
fields
)
withinGroup
RATIO_TO_REPORT
(
field
)
over
REPEAT
(
field
,
field
)
REPLACE
OREPLACE
(
field
,
field
,
field
)
REVERSE
(
field
)
RIGHT
(
field
,
field
)
ROUND
(
field
,
uint
)
ROW_NUMBER
(
)
over
ROWNUM
RPAD
(
field
,
field
,
field
)
RTRIM
(
field
,
field
)
SCHEMA
(
)
SECOND
(
field
)
SHL
SHIFTLEFT
LSHIFT
(
field
,
field
)
SHR
SHIFTRIGHT
RSHIFT
(
field
,
field
)
SIGN
(
field
)
SQR
SQRT
(
numericOp
)
SIN
(
numericOp
)
SINH
(
numericOp
)
SPACE
(
field
)
STDDEV_POP
STDEVP
(
field
)
over
STDDEV_SAMP
STDEV
(
field
)
over
STR_REPLACE
(
field
,
field
,
field
)
SUBSTR
SUBSTRING
(
field
,
numericOp
,
numericOp
)
SUBSTRING
(
field
FROM
numericOp
FOR
numericOp
)
SUM
(
DISTINCT
ALL
field
)
keep
filter
over
SYS_CONNECT_BY_PATH
(
field
,
stringLiteral
)
TAN
(
numericOp
)
TANH
(
numericOp
)
timeLiteral
timestampLiteral
TIMEZONE
(
field
)
TIMEZONE_HOUR
(
field
)
TIMEZONE_MINUTE
(
field
)
TO_CHAR
(
field
)
TO_DATE
(
field
,
field
)
TO_NUMBER
(
field
)
TO_TIMESTAMP
(
field
,
field
)
TRANSLATE
OTRANSLATE
(
field
,
field
,
field
)
TRIM
(
field
,
field
)
TRIM
(
LEADING
L
TRAILING
T
BOTH
B
field
FROM
field
)
TRUNC
(
field
,
stringLiteral
)
TRUNC
(
numericOp
,
numericOp
)
truthValue
UNIX_TIMESTAMP
(
field
)
UPPER
UCASE
(
field
)
VAR_POP
(
field
)
over
VAR_SAMP
(
field
)
over
WIDTH_BUCKET
(
field
,
field
,
field
,
field
)
binaryLiteral
YEAR
(
field
)
unsignedNumericLiteral
(
select
)
rowValueExpression
{
d
stringLiteral
}
{
t
stringLiteral
}
{
fn
term
}
{
ts
stringLiteral
}
identifier
(
,
field
)
jsonEntries ::=
,
jsonEntry
jsonEntry ::=
KEY
field
VALUE
field
field
,
field
truthValue ::=
TRUE
FALSE
NULL
datePart ::=
YEAR
YYYY
YY
MONTH
MM
M
DAY
DD
D
HOUR
HH
MINUTE
MI
N
SECOND
SS
S
MILLISECOND
MS
MICROSECOND
MCS
NANOSECOND
NS
EPOCH
QUARTER
QQ
Q
WEEK
WW
WK
ISO_DAY_OF_WEEK
ISODOW
DAY_OF_WEEK
DAYOFWEEK
WEEKDAY
W
DAY_OF_YEAR
DAYOFYEAR
DOY
DY
Y
keep ::=
KEEP
(
DENSE_RANK
FIRST
LAST
ORDER BY
sortFields
)
filter ::=
FILTER
(
WHERE
condition
)
over ::=
FROM FIRST
FROM LAST
RESPECT NULLS
IGNORE NULLS
OVER
identifier
(
windowSpecification
)
withinGroup ::=
WITHIN GROUP
(
ORDER BY
sortFields
)
rangeBound ::=
UNBOUNDED
uint
PRECEDING
FOLLOWING
CURRENT ROW
case ::=
CASE
WHEN
condition
THEN
field
field
WHEN
field
THEN
field
ELSE
field
END
comparator ::=
=
!=
<>
^=
>=
>
<=>
<=
<
castDataType ::=
dataType
SIGNED
INTEGER
UNSIGNED
INTEGER
dataType ::=
ARRAY
BIGINT
UNSIGNED
BINARY
(
uint
)
BIT
(
uint
)
BLOB
(
uint
)
BOOL
BOOLEAN
CHAR
CHARACTER
(
uint
BYTE
CHAR
)
COLLATE
collationName
CLOB
(
uint
)
COLLATE
collationName
DATE
DECIMAL
(
*
uint
,
int
)
DOUBLE
PRECISION
(
uint
,
uint
)
ENUM
(
stringLiteral
,
stringLiteral
)
COLLATE
collationName
FLOAT
(
uint
,
uint
)
INT
INTEGER
(
uint
)
UNSIGNED
LONGBLOB
LONGTEXT
COLLATE
collationName
LONG NVARCHAR
(
uint
)
COLLATE
collationName
LONG VARBINARY
(
uint
)
LONG VARCHAR
(
uint
)
COLLATE
collationName
MEDIUMBLOB
MEDIUMINT
(
uint
)
UNSIGNED
MEDIUMTEXT
COLLATE
collationName
NCHAR
(
uint
)
COLLATE
collationName
NCLOB
COLLATE
collationName
NUMBER
NUMERIC
(
*
uint
,
int
)
NVARCHAR
(
uint
)
COLLATE
collationName
OTHER
REAL
(
uint
,
uint
)
SERIAL
SERIAL4
SERIAL8
SET
(
stringLiteral
,
stringLiteral
)
COLLATE
collationName
SMALLINT
(
uint
)
UNSIGNED
TEXT
(
uint
)
COLLATE
collationName
TIMESTAMP
(
uint
)
WITH
WITHOUT
TIME ZONE
TIMESTAMPTZ
(
uint
)
TIME
(
uint
)
WITH
WITHOUT
TIME ZONE
TIMETZ
(
uint
)
TINYBLOB
TINYINT
(
uint
)
UNSIGNED
TINYTEXT
COLLATE
collationName
UUID
VARCHAR
CHARACTER VARYING
(
uint
BYTE
CHAR
)
COLLATE
collationName
VARCHAR2
(
uint
BYTE
CHAR
)
COLLATE
collationName
VARBINARY
(
uint
)
arraySuffix
arraySuffix ::=
ARRAY
[
uint
]
constraintName ::=
identifier
catalogName ::=
name
schemaName ::=
name
tableName ::=
name
typeName ::=
name
indexName ::=
name
sequenceName ::=
name
userName ::=
name
roleName ::=
name
fieldNames ::=
,
fieldName
fieldName ::=
name
collation ::=
name
name ::=
.
identifier
stringLiteral ::=
'
character
'
$
nonSpaceCharacter
$
character
$
nonSpaceCharacter
$
q'[
characters
]'
q'{
characters
}'
q'(
characters
)'
q'<
characters
>'
q'
nonSpaceCharacter
characters
nonSpaceCharacter
'
characterLiteral ::=
'
character
'
dateLiteral ::=
DATE
stringLiteral
timeLiteral ::=
TIME
stringLiteral
timestampLiteral ::=
TIMESTAMP
stringLiteral
intervalLiteral ::=
INTERVAL
stringLiteral
field
YEAR
YEARS
QUARTER
QUARTERS
MONTH
MONTHS
WEEK
WEEKS
DAY
DAYS
HOUR
HOURS
MINUTE
MINUTES
SECOND
SECONDS
MILLISECOND
MILLISECONDS
MICROSECOND
MICROSECONDS
NANOSECOND
NANOSECONDS
int ::=
signedInteger
signedInteger ::=
todo
uint ::=
unsignedInteger
unsignedInteger ::=
todo
unsignedNumericLiteral ::=
todo
identifiers ::=
,
identifier
identifier ::=
identifierStart
identifierPart
"
doubleQuotedIdentifierPart
"
`
backtickQuotedIdentifierPart
`
[
brackedQuotedIdentifierPart
]
identifierStart ::=
todo
identifierPart ::=
todo
doubleQuotedIdentifierPart ::=
nonDoubleQuoteCharacter
doubleQuote
backtickQuotedIdentifierPart ::=
nonBacktickCharacter
doubleBacktick
brackedQuotedIdentifierPart ::=
nonClosingBracketCharacter
doubleClosingBracket
nonDoubleQuoteCharacter ::=
todo
nonBacktickCharacter ::=
todo
nonClosingBracketCharacter ::=
todo
doubleQuote ::=
""
doubleBacktick ::=
``
doubleClosingBracket ::=
]]
The diagrams have been created with the neat RRDiagram library by Christopher Deckers .
Feedback
Do you have any feedback about this page? We'd love to hear it!