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
transactionStatement
blockStatement
ddlStatement ::=
alterDatabaseStatement
alterDomainStatement
alterIndexStatement
alterSchemaStatement
alterSequenceStatement
alterSessionStatement
alterTableStatement
alterTypeStatement
alterViewStatement
commentStatement
createDatabaseStatement
createDomainStatement
createFunctionStatement
createIndexStatement
createProcedureStatement
createSchemaStatement
createSequenceStatement
createSynonymStatement
createTableStatement
createTriggerStatement
createTypeStatement
createViewStatement
dropDatabaseStatement
dropDomainStatement
dropFunctionStatement
dropIndexStatement
dropProcedureStatement
dropTableStatement
dropTypeStatement
dropSchemaStatement
dropSequenceStatement
dropSynonymStatement
dropTriggerStatement
dropViewStatement
grantStatement
renameStatement
revokeStatement
setCatalogStatement
setSchemaStatement
truncateStatement
useStatement
dmlStatement ::=
deleteStatement
insertStatement
mergeStatement
resultStatement
updateStatement
transactionStatement ::=
startTransactionStatement
savepointStatement
releaseSavepointStatement
commitStatement
rollbackStatement
proceduralStatements ::=
proceduralStatement
;
proceduralStatement ::=
label
query
assignmentStatement
callStatement
caseStatement
labelName
continueStatement
declareStatement
executeStatement
exitStatement
forStatement
labelName
gotoStatement
ifStatement
labelName
loopStatement
labelName
nullStatement
repeatStatement
labelName
returnStatement
signalStatement
whileStatement
labelName
label ::=
<<
labelName
>>
labelName
:
blockStatement ::=
EXECUTE BLOCK AS
DECLARE
declarationStatement
;
BEGIN
NOT
ATOMIC
proceduralStatements
END
DO
stringLiteral
declarationStatement ::=
variableName
CONSTANT
dataType
NOT
NULL
=
:=
DEFAULT
field
declareStatement ::=
DECLARE
DEFINE
,
variableNames
AS
dataType
=
DEFAULT
field
assignmentStatement ::=
SET
LET
variableName
=
field
SET
(
variableNames
)
=
fields
select
variableName
:=
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
variableName
IN
REVERSE
int
..
int
BY
int
loopStatement
whileStatement ::=
WHILE
condition
loopStatement
DO
proceduralStatements
END WHILE
repeatStatement ::=
REPEAT
proceduralStatements
UNTIL
condition
END REPEAT
executeStatement ::=
EXECUTE
IMMEDIATE
STATEMENT
field
(
field
)
callStatement ::=
CALL
procedureName
(
arguments
)
EXECUTE PROCEDURE
procedureName
(
arguments
)
arguments
procedureName
(
arguments
)
EXEC
procedureName
arguments
arguments ::=
,
argument
argument ::=
parameterName
gotoStatement ::=
GOTO
labelName
continueStatement ::=
CONTINUE
ITERATE
labelName
WHEN
condition
exitStatement ::=
EXIT
LEAVE
label
WHEN
condition
returnStatement ::=
RETURN
field
signalStatement ::=
SIGNAL
SQLSTATE
VALUE
stringLiteral
SET
MESSAGE_TEXT
=
stringLiteral
(
stringLiteral
)
RAISE
EXCEPTION
SQLSTATE
stringLiteral
USING
MESSAGE
=
stringLiteral
nullStatement ::=
NULL
alterDatabaseStatement ::=
ALTER DATABASE
IF EXISTS
catalogName
RENAME
TO
AS
catalogName
OWNER
TO
userName
alterDomainStatement ::=
ALTER DOMAIN
IF EXISTS
domainName
ADD
constraint
CONSTRAINT
constraintName
constraint
DROP
CONSTRAINT
IF EXISTS
constraintName
CASCADE
RESTRICT
RENAME
TO
AS
domainName
RENAME
CONSTRAINT
IF EXISTS
constraintName
TO
AS
constraintName
SET DEFAULT
concat
DROP DEFAULT
SET
DROP
NOT NULL
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
alterTableStatement ::=
ALTER TABLE
IF EXISTS
ONLY
tableName
ADD
WITH CHECK
constraint
WITH CHECK
CONSTRAINT
constraintName
constraint
COLUMN
IF NOT EXISTS
column
FIRST
BEFORE
columnName
AFTER
columnName
(
,
WITH CHECK
CONSTRAINT
constraintName
constraint
constraint
column
)
ALTER
MODIFY
COLUMN
identifier
SET DATA
TYPE
dataType
NOT
NULL
dataType
CONSTRAINT
constraintName
NOT
NULL
DROP NOT NULL
SET
NOT
NULL
SET
DEFAULT
concat
SET OPTIONS
(
DESCRIPTION
=
stringLiteral
)
DROP DEFAULT
RENAME
TO
AS
identifier
column
(
column
)
CONSTRAINT
constraintName
constraintState
COMMENT
=
IS
stringLiteral
COLUMN
stringLiteral
SET OPTIONS
(
DESCRIPTION
=
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
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
(
fieldNames
)
AS
select
createDatabaseStatement ::=
CREATE DATABASE
IF NOT EXISTS
catalogName
createDomainStatement ::=
CREATE DOMAIN
IF NOT EXISTS
domainName
AS
dataType
DEFAULT
concat
constraint
CONSTRAINT
constraintName
constraint
createFunctionStatement ::=
CREATE
OR
ALTER
REPLACE
FUNCTION
functionName
(
parameterDeclarations
)
RETURNS
RETURN
RETURNING
dataType
NO SQL
CONTAINS SQL
READS SQL DATA
MODIFIES SQL DATA
NOT
DETERMINISTIC
RETURNS NULL ON NULL INPUT
CALLED ON NULL INPUT
AS
IS
declarationStatement
;
BEGIN
proceduralStatements
END
proceduralStatement
createIndexStatement ::=
CREATE
UNIQUE
INDEX
IF NOT EXISTS
indexName
indexType
(
sortFields
)
ON
tableName
indexType
(
sortFields
)
indexType
INCLUDE
COVERING
STORING
(
identifiers
)
VISIBLE
WHERE
condition
EXCLUDE NULL KEYS
createProcedureStatement ::=
CREATE
OR
ALTER
REPLACE
PROC
PROCEDURE
procedureName
(
parameterDeclarations
)
NO SQL
CONTAINS SQL
READS SQL DATA
MODIFIES SQL DATA
AS
IS
declarationStatement
;
BEGIN
proceduralStatements
END
proceduralStatement
createSchemaStatement ::=
CREATE SCHEMA
IF NOT EXISTS
schemaName
AUTHORIZATION
userName
schemaName
AUTHORIZATION
userName
createSequenceStatement ::=
CREATE
SEQUENCE
GENERATOR
IF NOT EXISTS
sequenceName
AS
dataType
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
createSynonymStatement ::=
CREATE
OR REPLACE
PUBLIC
PRIVATE
ALIAS
SYNONYM
synonymName
FOR
name
createTableStatement ::=
CREATE
GLOBAL
TEMP
TEMPORARY
VIRTUAL
TABLE
CT
IF NOT EXISTS
tableName
USING
identifier
(
identifiers
)
AS
select
(
,
CONSTRAINT
constraintName
constraint
constraint
index
column
)
ON COMMIT
DELETE ROWS
DROP
PRESERVE ROWS
COMMENT
=
IS
stringLiteral
OPTIONS
(
DESCRIPTION
=
stringLiteral
)
createTriggerStatement ::=
CREATE
OR
ALTER
REPLACE
TRIGGER
ON
table
BEFORE
FOR
AFTER
INSTEAD OF
INSERT
UPDATE
OF
fields
DELETE
OR
,
INSERT
UPDATE
OF
fields
DELETE
ON
table
REFERENCING
OLD
NEW
ROW
TABLE
AS
identifier
FOR
EACH
ROW
STATEMENT
WHEN
(
condition
)
AS
proceduralStatement
createTypeStatement ::=
CREATE TYPE
IF NOT EXISTS
typeName
AS
ENUM
(
,
stringLiteral
)
OBJECT
STRUCT
(
,
attribute
)
FROM
dataType
createViewStatement ::=
CREATE
OR
ALTER
REPLACE
FORCE
MATERIALIZED
VIEW
CV
IF NOT EXISTS
tableName
(
fieldNames
)
AS
select
dropDatabaseStatement ::=
DROP DATABASE
IF EXISTS
catalogName
IF EXISTS
dropDomainStatement ::=
DROP DOMAIN
IF EXISTS
domainName
IF EXISTS
CASCADE
RESTRICT
dropFunctionStatement ::=
DROP FUNCTION
IF EXISTS
functionName
IF EXISTS
dropIndexStatement ::=
DROP INDEX
IF EXISTS
indexName
IF EXISTS
ON
tableName
CASCADE
RESTRICT
dropProcedureStatement ::=
DROP
PROC
PROCEDURE
IF EXISTS
procedureName
IF EXISTS
dropSequenceStatement ::=
DROP
SEQUENCE
GENERATOR
IF EXISTS
sequenceName
IF EXISTS
RESTRICT
dropSequenceStatement ::=
DROP
PUBLIC
ALIAS
SYNONYM
IF EXISTS
synonymName
IF EXISTS
dropSchemaStatement ::=
DROP SCHEMA
IF EXISTS
schemaName
IF EXISTS
CASCADE
RESTRICT
dropTableStatement ::=
DROP
TEMPORARY
TABLE
IF EXISTS
tableName
IF EXISTS
CASCADE
CONSTRAINTS
RESTRICT
dropTriggerStatement ::=
DROP TRIGGER
IF EXISTS
triggerName
IF EXISTS
ON
tableName
dropTypeStatement ::=
DROP TYPE
IF EXISTS
,
typeName
IF EXISTS
CASCADE
RESTRICT
dropViewStatement ::=
DROP
MATERIALIZED
VIEW
IF EXISTS
tableName
IF EXISTS
renameStatement ::=
RENAME
COLUMN
fieldName
TO
AS
fieldName
DATABASE
catalogName
TO
AS
catalogName
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
OPEN
CURRENT
SCHEMA
CURRENT SQLID
=
schemaName
stringLiteral
useStatement ::=
USE
DATABASE
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
resultStatement ::=
selectStatement
insertStatement
updateStatement
deleteStatement
RETURNING
*
fields
selectStatement
=
select
insertStatement ::=
with
INSERT
INS
INTO
tableName
(
select
)
AS
identifier
(
identifiers
)
values
DEFAULT VALUES
SET
setClauses
select
ON DUPLICATE KEY UPDATE
SET
setClauses
ALL
NON KEY
NON PRIMARY KEY
NON CONFLICTING KEY
TO EXCLUDED
WHERE
condition
ON DUPLICATE KEY IGNORE
ON CONFLICT
ON CONSTRAINT
constraintName
(
fieldNames
)
WHERE
condition
DO
NOTHING
UPDATE
SET
setClauses
ALL
NON KEY
NON PRIMARY KEY
NON CONFLICTING KEY
TO EXCLUDED
WHERE
condition
values ::=
VALUES
,
rowValueExpression
,
field
updateStatement ::=
with
UPDATE
UPD
topSimple
tableName
periodPortion
(
select
)
AS
identifier
FROM
tables
SET
setClauses
SET
setClauses
FROM
tables
WHERE
condition
ALL
ORDER BY
sortFields
LIMIT
field
setClauses ::=
,
setClause
setClause ::=
fieldName
=
field
rowValueExpression
=
rowValueExpression
select
deleteStatement ::=
with
DELETE
DEL
topSimple
FROM
tableName
periodPortion
(
select
)
AS
identifier
USING
FROM
tables
WHERE
condition
ALL
ORDER BY
sortFields
LIMIT
field
mergeStatement ::=
with
MERGE
INTO
tableName
AS
identifier
USING
(
select
)
correlationName
ON
condition
WHEN MATCHED
AND
condition
THEN
DELETE
UPDATE SET
setClauses
WHERE
condition
DELETE WHERE
condition
WHEN NOT MATCHED
AND
condition
THEN INSERT
(
identifiers
)
VALUES
(
fields
)
WHERE
condition
column ::=
identifier
dataType
DEFAULT
ON NULL
concat
WITH VALUES
ON UPDATE
field
COMPUTE
COMPUTED
BY
GENERATED
ALWAYS
AS
ALIAS
MATERIALIZED
field
NOT
MATERIALIZED
PERSISTED
STORED
VIRTUAL
GENERATED
ALWAYS
BY DEFAULT
ON NULL
AS IDENTITY
(
identity
)
CONSTRAINT
constraintName
NOT
NULL
PRIMARY KEY
UNIQUE
KEY
INDEX
CHECK
(
condition
)
FOREIGN KEY
REFERENCES
constraintReferenceSpecification
AUTO_INCREMENT
AUTOINCREMENT
IDENTITY
(
int
,
int
)
READONLY
VISIBLE
NOT HIDDEN
INVISIBLE
NOT VISIBLE
IMPLICITLY
HIDDEN
COMMENT
=
IS
stringLiteral
OPTIONS
(
DESCRIPTION
=
stringLiteral
)
attribute ::=
identifier
dataType
startTransactionStatement ::=
START
BEGIN
WORK
TRAN
TRANSACTION
READ
WRITE
BT
savepointStatement ::=
SAVE
TRAN
TRANSACTION
SAVEPOINT
identifier
UNIQUE
ON ROLLBACK RETAIN CURSORS
releaseSavepointStatement ::=
RELEASE
TO
SAVEPOINT
identifier
commitStatement ::=
COMMIT
WORK
TRAN
TRANSACTION
END
TRANSACTION
ET
rollbackStatement ::=
ROLLBACK
WORK
TRAN
TRANSACTION
TO
SAVEPOINT
identifier
ROLLBACK
index ::=
KEY
INDEX
identifier
indexType
(
sortFields
)
indexType
indexType ::=
USING
identifier
constraint ::=
NOT
NULL
PRIMARY KEY
indexType
CLUSTERED
NONCLUSTERED
(
fieldNames
fieldName
AUTOINCREMENT
)
indexType
UNIQUE
KEY
INDEX
identifier
indexType
(
sortFields
)
indexType
FOREIGN KEY
(
fieldNames
)
REFERENCES
constraintReferenceSpecification
CHECK
(
condition
)
constraintState
constraintState ::=
constraintDeferrability
constraintEnforcement
constraintDeferrability ::=
NOT
DEFERRABLE
INITIALLY
DEFERRED
IMMEDIATE
INITIALLY
DEFERRED
IMMEDIATE
NOT
DEFERRABLE
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
parameterDeclarations ::=
,
parameterDeclaration
parameterDeclaration ::=
IN
OUT
IN OUT
INOUT
parameterName
AS
dataType
=
:=
DEFAULT
field
OUT
OUTPUT
READONLY
with ::=
WITH
RECURSIVE
,
commonTableExpression
commonTableExpression ::=
identifier
(
identifiers
)
AS
NOT
MATERIALIZED
(
resultStatement
)
select ::=
with
queryExpressionBody
orderBy
seekFetch
offsetFetch
forXML
forJSON
forUpdate
WITH
READ ONLY
CHECK OPTION
queryExpressionBody ::=
queryTerm
UNION
EXCEPT
MINUS
ALL
DISTINCT
queryTerm
queryTerm ::=
queryPrimary
INTERSECT
ALL
DISTINCT
queryTerm
queryPrimary ::=
(
select
)
SELECT
SEL
distinct
top
selectList
INTO
tableName
variableNames
tableExpression
values
TABLE
tableName
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
ALL
DISTINCT
ROLLUP
(
fields
)
CUBE
(
fields
)
GROUPING SETS
(
,
groupingSet
)
groupingSets
WITH ROLLUP
groupingSets ::=
,
fields
groupingSet
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
fields
FETCH
FIRST
NEXT
field
PERCENT
ROW
ROWS
ONLY
WITH TIES
LIMIT
field
PERCENT
WITH TIES
offsetFetch ::=
OFFSET
field
ROW
ROWS
FETCH
FIRST
NEXT
field
PERCENT
ROW
ROWS
ONLY
WITH TIES
LIMIT
field
PERCENT
WITH TIES
LIMIT
field
PERCENT
WITH TIES
OFFSET
field
,
field
ROWS
field
TO
field
forUpdate ::=
FOR SHARE
FOR KEY SHARE
FOR NO KEY UPDATE
FOR UPDATE
OF
fields
NOWAIT
WAIT
uint
SKIP LOCKED
forXML ::=
FOR XML
RAW
(
stringLiteral
)
AUTO
PATH
(
stringLiteral
)
EXPLICIT
,
ELEMENTS
XSINIL
ABSENT
BINARY BASE64
TYPE
ROOT
(
stringLiteral
)
forJSON ::=
FOR
JSON
JSONB
AUTO
PATH
,
INCLUDE_NULL_VALUES
WITHOUT_ARRAY_WRAPPER
ROOT
(
stringLiteral
)
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
joinHint
JOIN
STRAIGHT_JOIN
table
joinQualification
outerJoin ::=
PARTITION BY
(
fields
)
LEFT
RIGHT
FULL
OUTER
joinHint
JOIN
table
PARTITION BY
(
fields
)
joinQualification
semiAntiJoin ::=
LEFT
SEMI
ANTI
JOIN
table
joinQualification
joinHint ::=
HASH
LOOP
LOOKUP
MERGE
lateral ::=
LATERAL
tableFactor
tableFactor
tableFactor ::=
tableName
versions
versions
WITH ORDINALITY
correlationName
tableHints
(
table
)
WITH ORDINALITY
correlationName
(
select
)
WITH ORDINALITY
correlationName
tableFunction
WITH ORDINALITY
correlationName
UNNEST
(
field
)
WITH ORDINALITY
correlationName
values
WITH ORDINALITY
correlationName
OLD
TABLE
(
updateStatement
deleteStatement
mergeStatement
)
WITH ORDINALITY
correlationName
NEW
FINAL
TABLE
(
insertStatement
updateStatement
mergeStatement
)
WITH ORDINALITY
correlationName
JSON_TABLE
(
field
,
field
COLUMNS
(
,
jsonTableColumn
)
)
OPENJSON
(
field
,
field
)
WITH
(
,
openjsonColumn
)
XMLTABLE
(
field
PASSING
BY
REF
VALUE
field
COLUMNS
,
xmlTableColumn
)
jsonTableColumn ::=
identifier
dataType
PATH
stringLiteral
FOR ORDINALITY
openjsonColumn ::=
identifier
dataType
stringLiteral
xmlTableColumn ::=
identifier
dataType
PATH
stringLiteral
FOR ORDINALITY
tableFunction ::=
GENERATE_SERIES
SYSTEM_RANGE
(
field
,
field
,
field
)
TABLE
(
GENERATOR
(
ROWCOUNT
=>
unsignedInteger
)
)
NUMBERS
(
field
,
field
)
tableHints ::=
WITH
(
,
tableHint
)
USE
FORCE
IGNORE
INDEX
KEY
FOR
JOIN
ORDER BY
GROUP BY
(
identifiers
)
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
TUPLE
(
fields
)
fields ::=
,
field
field ::=
or
condition ::=
or
or ::=
OR
xor
xor ::=
OR
and
and ::=
AND
not
not ::=
NOT
predicate
predicate ::=
EQUAL_NULL
(
field
,
field
)
EXISTS
(
select
)
JSON_EXISTS
(
field
,
field
TRUE
FALSE
UNKNOWN
ERROR
ON ERROR
)
REGEXP_LIKE
(
concat
,
concat
)
ST_COVEREDBY
(
field
,
field
)
SDO_COVEREDBY
(
field
,
field
)
=
'TRUE'
ST_COVERS
(
field
,
field
)
SDO_COVERS
(
field
,
field
)
=
'TRUE'
ST_CONTAINS
(
field
,
field
)
SDO_CONTAINS
(
field
,
field
)
=
'TRUE'
field
.
STContains
(
field
)
ST_CROSSES
(
field
,
field
)
field
.
STCrosses
(
field
)
ST_DISJOINT
(
field
,
field
)
field
.
STDisjoint
(
field
)
ST_EQUALS
(
field
,
field
)
SDO_EQUAL
(
field
,
field
)
=
'TRUE'
field
.
STEquals
(
field
)
ST_INTERSECTS
(
field
,
field
)
field
.
STIntersects
(
field
)
ST_ISCLOSED
(
field
)
field
.
STIsClosed
(
)
ST_ISEMPTY
(
field
)
field
.
STIsEmpty
(
)
ST_ISRING
(
field
)
field
.
STIsRing
(
)
ST_ISSIMPLE
(
field
)
field
.
STIsSimple
(
)
ST_ISVALID
(
field
)
field
.
STIsValid
(
)
field
.
ST_ISVALID
(
)
ST_OVERLAPS
(
field
,
field
)
SDO_OVERLAPS
(
field
,
field
)
=
'TRUE'
field
.
STOverlaps
(
field
)
ST_TOUCHES
(
field
,
field
)
SDO_TOUCH
(
field
,
field
)
=
'TRUE'
field
.
STTouches
(
field
)
ST_WITHIN
(
field
,
field
)
SDO_INSIDE
(
field
,
field
)
=
'TRUE'
field
.
STWithin
(
field
)
UNIQUE
(
select
)
XMLEXISTS
(
field
PASSING
BY
REF
VALUE
field
)
field
.
exist
(
field
)
concat
comparator
ALL
ANY
SOME
(
select
fields
)
concat
IS
NOT
NULL
DOCUMENT
JSON
DISTINCT FROM
concat
concat
NOT
IN
(
select
fields
)
field
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
=*
concat
row2
OVERLAPS
row2
row2 ::=
ROW
TUPLE
(
field
,
field
)
concat ::=
||
collated
collated ::=
op
COLLATE
collation
op ::=
sum
<<
>>
->
->>
?
sum
sum ::=
factor
+
-
factor
factor ::=
exp
*
/
%
MOD
exp
exp ::=
unaryOps
^
**
unaryOps
unaryOps ::=
+
-
~
CONNECT_BY_ROOT
term
(+)
::
dataType
[
field
]
term ::=
:
identifier
?
binaryLiteral
bitLiteral
stringLiteral
ADD
PLUS
(
field
,
field
)
ADD_YEARS
ADD_MONTHS
ADD_DAYS
ADD_HOURS
ADD_MINUTES
ADD_SECONDS
(
field
,
field
)
ANY
(
DISTINCT
ALL
field
)
filter
over
ANY_VALUE
ARBITRARY
(
DISTINCT
ALL
field
HAVING
MIN
MAX
field
)
filter
over
ACOS
(
field
)
ACOSH
(
field
)
ACOTH
(
field
)
AND
(
field
,
field
)
ARRAY
[
fields
]
(
select
)
ARRAY_AGG
groupArray
(
DISTINCT
ALL
field
ORDER BY
sortFields
)
filter
over
ARRAY_APPEND
arrayPushBack
(
field
,
field
)
ARRAY_CAT
ARRAY_CONCAT
arrayConcat
(
field
,
field
)
ARRAY_CONSTRUCT
(
fields
)
ARRAY_CONSTRUCT_COMPACT
(
fields
)
ARRAY_ALL_MATCH
ALL_MATCH
arrayAll
(
field
,
lambda1
lambda1
,
field
)
ARRAY_ANY_MATCH
ANY_MATCH
arrayExists
(
field
,
lambda1
lambda1
,
field
)
ARRAY_FILTER
arrayFilter
(
field
,
lambda1
lambda1
,
field
)
ARRAY_GET
arrayElement
(
field
,
field
)
ARRAY_MAP
arrayMap
ARRAY_TRANSFORM
TRANSFORM
(
field
,
lambda1
lambda1
,
field
)
ARRAY_NONE_MATCH
NONE_MATCH
(
field
,
lambda1
lambda1
,
field
)
ARRAY_OVERLAP
ARRAYS_OVERLAP
(
field
,
field
)
ARRAY_PREPEND
arrayPushFront
(
field
,
field
)
ARRAY_REMOVE
(
field
,
field
)
ARRAY_REPLACE
(
field
,
field
,
field
)
ARRAY_TO_STRING
(
field
,
field
,
field
)
ASC
ASCII
ASCII_VAL
(
field
)
ASCII_CHAR
(
field
)
ASIN
(
field
)
ASINH
(
field
)
ATAN
(
field
)
ATANH
(
field
)
ATN2
ATAN2
(
field
,
field
)
AVG
(
DISTINCT
ALL
field
)
keep
filter
over
BIN_SHL
(
field
,
field
)
BIN_SHR
(
field
,
field
)
BIT_GET
BITGET
GET_BIT
GETBIT
bitTest
(
field
,
field
)
BIT_SET
BITSET
SET_BIT
SETBIT
(
field
,
field
,
field
)
BIT_LENGTH
(
field
)
BITCOUNT
BIT_COUNT
bitCount
(
field
)
BIN_AND
BIN_AND_AGG
BITAND
BITAND_AGG
BIT_AND
BIT_AND_AGG
BITWISE_AND_AGG
groupBitAnd
(
DISTINCT
ALL
field
)
filter
over
BIN_NAND
BITNAND
BIT_NAND
BITWISE_AND
bitAnd
(
field
,
field
)
BIN_NAND
BIN_NAND_AGG
BITNAND
BITNAND_AGG
BIT_NAND
BIT_NAND_AGG
groupBitNand
(
DISTINCT
ALL
field
)
filter
over
BIN_NOR
BITNOR
BIT_NOR
bitNor
(
field
,
field
)
BIN_NOR
BIN_NOR_AGG
BITNOR
BITNOR_AGG
BIT_NOR
BIT_NOR_AGG
groupBitNor
(
DISTINCT
ALL
field
)
filter
over
BIN_NOT
BITNOT
BIT_NOT
BITWISE_NOT
bitNot
(
field
)
BIN_OR
BITOR
BIT_OR
BITWISE_OR
bitOr
(
field
,
field
)
BIN_OR
BIN_OR_AGG
BITOR
BITOR_AGG
BIT_OR
BIT_OR_AGG
BITWISE_OR_AGG
groupBitOr
(
DISTINCT
ALL
field
)
filter
over
BIN_TO_UUID
UUIDNumToString
(
field
)
BIN_XNOR
BITXNOR
BIT_XNOR
bitXnor
(
field
,
field
)
BIN_XNOR
BIN_XNOR_AGG
BITXNOR
BITXNOR_AGG
BIT_XNOR
BIT_XNOR_AGG
groupBitXnor
(
ALL
field
)
filter
over
BIN_XOR
BITXOR
BIT_XOR
BITWISE_XOR
bitXor
(
field
,
field
)
BIN_XOR
BIN_XOR_AGG
BITXOR
BITXOR_AGG
BIT_XOR
BIT_XOR_AGG
groupBitXor
(
ALL
field
)
filter
over
BOOL_AND
BOOLAND_AGG
EVERY
LOGICAL_AND
(
field
)
filter
over
BOOL_OR
BOOLOR_AGG
LOGICAL_OR
(
field
)
filter
over
CARDINALITY
(
field
)
case
CAST
(
field
AS
castDataType
DEFAULT
NULL
ON
CONVERSION
ERROR
)
CBRT
(
field
)
CEIL
CEILING
(
field
)
CENTURY
(
field
)
CHARINDEX
(
field
,
field
,
field
)
CHAR_LENGTH
(
field
)
CHOOSE
ELT
(
fields
)
CHR
CHAR
ASCII_CHAR
(
field
)
COALESCE
(
fields
)
COERCE
(
field
AS
castDataType
)
CONCAT
(
fields
)
CONNECT_BY_ISCYCLE
CONNECT_BY_ISLEAF
CONTAINS
(
field
,
field
)
CONVERT
(
dataType
,
field
,
uint
)
COS
(
field
)
COSH
(
field
)
COT
(
field
)
COTH
(
field
)
COUNT
(
DISTINCT
ALL
tableName
.
*
field
rowValueExpression
)
keep
filter
over
COUNTIF
COUNT_IF
(
condition
)
over
CORR
(
ALL
field
,
field
)
filter
over
COVAR_POP
covarPop
(
ALL
field
,
field
)
filter
over
COVAR_SAMP
covarSamp
(
ALL
field
,
field
)
filter
over
CUME_DIST
(
)
over
(
ALL
fields
)
withinGroup
CURRVAL
(
name
stringLiteral
)
CURRENT_CATALOG
(
)
CURRENT_DATABASE
(
)
CURRENT
DATE
CURRENT_DATE
(
)
CURRENT
SCHEMA
CURRENT_SCHEMA
(
)
CURRENT
TIME
CURRENT_TIME
(
)
CURRENT
TIMESTAMP
CURRENT_TIMESTAMP
(
uint
)
CURRENT
USER
CURRENT_USER
(
)
CURDATE
(
)
CURTIME
(
)
DATABASE
(
)
dateLiteral
DATEADD
(
datePart
,
field
,
field
)
DATEDIFF
(
datePart
,
field
,
field
)
DATE_DIFF
(
field
,
field
,
datePart
)
DATEPART
DATE_PART
(
datePart
,
field
)
DATE_PART_YEAR
(
field
)
DATE_ADD
(
field
,
field
)
DATE_SUB
(
field
,
field
)
DATE_TRUNC
DATETIME_TRUNC
(
stringLiteral
,
field
field
,
datePart
)
DAY
(
field
)
DAYOFMONTH
(
field
)
DAYOFWEEK
(
field
)
DB_NAME
(
)
DBINFO
(
field
)
DECADE
(
field
)
DECODE
MAP
(
field
,
field
,
field
,
field
)
DENSE_RANK
DENSERANK
(
)
over
(
ALL
fields
)
withinGroup
DEG
DEGREE
DEGREES
(
field
)
DIGITS
(
field
)
DIV
DIVIDE
(
field
,
field
)
ELEMENT_AT
(
field
,
field
)
ENDS_WITH
endsWith
(
field
,
field
)
EPOCH
(
field
)
EXTRACT
(
datePart
FROM
field
)
EXP
(
field
)
FLOOR
(
field
)
FIELD
(
field
,
fields
)
FIRST_VALUE
(
field
RESPECT NULLS
IGNORE NULLS
)
RESPECT NULLS
IGNORE NULLS
over
GETDATE
(
)
GREATEST
MAX
MAXVALUE
(
fields
)
GROUP_CONCAT
(
DISTINCT
ALL
field
ORDER BY
sortFields
SEPARATOR
stringLiteral
)
GROUP_ID
(
)
GROUPING_ID
(
fields
)
GROUPING
(
fields
)
HEX
TO_HEX
(
field
)
HOUR
(
field
)
IFNULL
(
field
,
field
)
INSERT
(
field
,
field
,
field
,
field
)
INSTR
(
field
,
field
,
field
)
intervalLiteral
IF
IIF
(
condition
,
field
,
field
)
ISJSON
(
field
)
ISNULL
(
field
,
field
)
JSON
[
fields
]
{
field
:
field
,
field
:
field
}
JSON_AGG
JSONB_AGG
(
DISTINCT
ALL
field
ORDER BY
sortFields
)
filter
over
JSON_ARRAY
(
fields
jsonNull
jsonReturning
)
JSON_ARRAYAGG
(
DISTINCT
ALL
field
ORDER BY
sortFields
jsonNull
jsonReturning
)
JSON_ARRAY_LENGTH
JSON_LENGTH
JSONArrayLength
(
field
)
JSON_BUILD_ARRAY
JSONB_BUILD_ARRAY
(
fields
)
JSON_BUILD_OBJECT
JSONB_BUILD_OBJECT
(
fields
)
JSON_KEY_EXISTS
JSONB_KEY_EXISTS
(
field
,
field
)
JSON_KEYS
JSONB_KEYS
JSONExtractKeys
(
field
)
JSON_INSERT
JSONB_INSERT
(
field
,
field
,
field
)
JSON_OBJECT
(
jsonEntries
jsonNull
jsonReturning
)
JSON_OBJECT_AGG
JSONB_OBJECT_AGG
(
ALL
field
,
field
)
JSON_OBJECTAGG
(
ALL
jsonEntry
jsonNull
jsonReturning
)
JSON_REMOVE
JSONB_REMOVE
(
field
,
field
)
JSON_REPLACE
JSONB_REPLACE
(
field
,
field
,
field
)
JSON_SET
JSONB_SET
(
field
,
field
,
field
)
JSON_VALID
(
field
)
JSON_VALUE
(
field
,
field
ERROR
NULL
DEFAULT
field
ON EMPTY
ON ERROR
jsonReturning
)
LOWER
LCASE
(
field
)
LPAD
leftPad
(
field
,
field
,
field
)
LTRIM
trimLeft
(
field
,
field
)
LEFT
(
field
,
field
)
LEN
(
field
)
LENGTH
(
field
)
LN
LOGN
(
field
)
LOCATE
(
field
,
field
,
field
)
LOCATE_IN_STRING
(
field
,
field
,
field
)
LOG
(
field
,
field
)
LOG10
(
field
)
LEVEL
LEAST
MIN
MINVALUE
(
fields
)
LEAD
leadInFrame
(
field
,
uint
,
field
RESPECT NULLS
IGNORE NULLS
)
RESPECT NULLS
IGNORE NULLS
over
LAG
lagInFrame
(
field
,
uint
,
field
RESPECT NULLS
IGNORE NULLS
)
RESPECT NULLS
IGNORE NULLS
over
LAST_DAY
(
field
)
LAST_VALUE
(
field
RESPECT NULLS
IGNORE NULLS
)
RESPECT NULLS
IGNORE NULLS
over
LISTAGG
(
DISTINCT
ALL
field
,
stringLiteral
)
withinGroup
over
MAX
MAXIMUM
(
DISTINCT
ALL
field
)
keep
filter
over
MAX_BY
ARG_MAX
argMax
(
field
,
field
)
filter
over
MD5
HASH_MD5
(
field
)
MEDIAN
(
field
)
filter
over
MICROSECOND
(
field
)
MID
(
field
,
field
,
field
)
MILLISECOND
(
field
)
MIN
MINIMUM
(
DISTINCT
ALL
field
)
keep
filter
over
MIN_BY
ARG_MIN
argMin
(
field
,
field
)
filter
over
MINUTE
(
field
)
MOD
MODULO
(
field
,
field
)
MODE
(
)
withinGroup
over
MONTH
(
field
)
MUL
PRODUCT
(
DISTINCT
ALL
field
)
keep
filter
over
MUL
MULTIPLY
(
field
,
field
)
MULTISET
(
select
)
MULTISET_AGG
(
ALL
field
ORDER BY
sortFields
)
filter
over
NEG
NEGATE
(
field
)
NEXT VALUE FOR
sequenceName
NEXTVAL
(
name
stringLiteral
)
NEWID
(
field
)
NOW
(
uint
)
NTH_VALUE
(
field
,
uint
FROM FIRST
FROM LAST
RESPECT NULLS
IGNORE NULLS
)
FROM FIRST
FROM LAST
RESPECT NULLS
IGNORE NULLS
over
NTILE
(
uint
)
over
NULLIF
(
field
,
field
)
NVL
(
field
,
field
)
NVL2
(
field
,
field
,
field
)
OBJECT_AGG
(
field
,
field
)
OBJECT_CONSTRUCT
(
fields
)
OBJECT_CONSTRUCT_KEEP_NULL
(
fields
)
OCTET_LENGTH
BYTE_LENGTH
DATALENGTH
LENGTHB
(
field
)
OR
(
field
,
field
)
OVERLAY
(
field
PLACING
field
FROM
field
FOR
field
)
PI
(
)
POSITION
(
field
IN
field
field
,
field
,
field
)
PERCENT_RANK
(
)
over
(
ALL
fields
)
withinGroup
PERCENTILE_CONT
PERCENTILE_DISC
(
ALL
field
)
withinGroup
over
PERCENTILE_CONT
PERCENTILE_DISC
(
field
,
field
IGNORE NULLS
)
over
POW
POWER
(
field
,
field
)
PRIOR
concat
QUARTER
(
field
)
REGR_SLOPE
REGR_INTERCEPT
REGR_COUNT
REGR_R2
REGR_AVGX
REGR_AVGY
REGR_SXX
REGR_SYY
REGR_SXY
(
ALL
field
,
field
)
filter
over
RAD
RADIAN
RADIANS
(
field
)
RANK
(
)
over
(
ALL
fields
)
withinGroup
RATIO_TO_REPORT
(
field
)
over
REGEXP_REPLACE
(
field
,
field
,
field
)
REGEXP_REPLACE_ALL
(
field
,
field
,
field
)
REGEXP_REPLACE_FIRST
(
field
,
field
,
field
)
REPEAT
REPLICATE
(
field
,
field
)
REPLACE
OREPLACE
STR_REPLACE
replaceAll
(
field
,
field
,
field
)
REPLACE_REGEXPR
(
field
IN
field
WITH
field
OCCURRENCE
ALL
unsignedInteger
)
REVERSE
STRREVERSE
(
field
)
RIGHT
(
field
,
field
)
ROOT
(
field
,
field
)
ROUND
(
field
,
uint
)
ROW_NUMBER
ROWNUMBER
(
)
over
ROWNUM
RPAD
rightPad
(
field
,
field
,
field
)
RTRIM
trimRight
(
field
,
field
)
SCHEMA
(
)
SECOND
(
field
)
SEQ4
SEQ8
(
)
SHL
SHIFTLEFT
LSHIFT
LEFT_SHIFT
BIT_LSHIFT
BITWISE_LEFT_SHIFT
bitShiftLeft
(
field
,
field
)
SHR
SHIFTRIGHT
RSHIFT
RIGHT_SHIFT
BIT_RSHIFT
BITWISE_RIGHT_SHIFT
bitShiftRight
(
field
,
field
)
SIGN
SGN
(
field
)
SQR
SQRT
(
field
)
SQUARE
(
field
)
SIN
(
field
)
SINH
(
field
)
SPLIT
(
field
,
field
)
SPLIT_PART
(
field
,
field
,
field
)
SPACE
(
field
)
STARTS_WITH
startsWith
(
field
,
field
)
STDDEV
STDDEV_POP
STDEVP
stddevPop
(
ALL
field
)
over
STDDEV_SAMP
STDEV
STDEV_SAMP
stddevSamp
(
ALL
field
)
over
STR_REPLACE
(
field
,
field
,
field
)
STRING_AGG
(
DISTINCT
ALL
field
,
stringLiteral
ORDER BY
sortFields
)
withinGroup
over
STRING_TO_ARRAY
(
field
,
field
,
field
)
ST_AREA
(
field
)
SDO_GEOM
.
SDO_AREA
(
field
,
field
)
field
.
STArea
(
)
ST_ASBINARY
ST_ASWKB
(
field
)
field
.
Get_WKB
(
)
STAsBinary
(
)
ST_ASTEXT
(
field
)
field
.
Get_WKT
(
)
STAsText
(
)
ST_BOUNDARY
(
field
)
ST_CENTROID
(
field
)
SDO_GEOM
.
SDO_CENTROID
(
field
,
field
)
field
.
STCentroid
(
)
ST_DIFFERENCE
(
field
,
field
)
SDO_GEOM
.
SDO_DIFFERENCE
(
field
,
field
,
field
)
field
.
STDifference
(
field
)
ST_DIMENSION
(
field
)
field
.
STDimension
(
)
ST_DISTANCE
(
field
,
field
)
SDO_GEOM
.
SDO_DISTANCE
(
field
,
field
,
field
)
field
.
STDistance
(
field
)
ST_ENDPOINT
(
field
)
SDO_LRS
.
GEOM_SEGMENT_END_PT
(
field
)
field
.
STEndPoint
(
)
ST_EXTERIORRING
(
field
)
field
.
STExteriorRing
(
)
ST_GEOMETRYN
(
field
,
field
)
SDO_UTIL
.
EXTRACT
(
field
,
field
)
field
.
STGeometryN
(
field
)
ST_GEOMETRYTYPE
(
field
)
field
.
STGeometryType
(
)
ST_GEOMFROMWKB
SDO_GEOMETRY
GEOMETRY
GEOGRAPHY
::
STGeomFromWKB
(
field
,
field
)
ST_GEOMFROMTEXT
SDO_GEOMETRY
GEOMETRY
GEOGRAPHY
::
STGeomFromText
(
field
,
field
)
ST_INTERIORRINGN
(
field
)
field
.
STInteriorRingN
(
)
ST_INTERSECTION
(
field
,
field
)
SDO_GEOM
.
SDO_INTERSECTION
(
field
,
field
,
field
)
field
.
STIntersection
(
field
)
ST_LENGTH
(
field
)
SDO_GEOM
.
SDO_LENGTH
(
field
,
field
)
field
.
STLength
(
)
ST_NUMGEOMETRIES
(
field
)
SDO_UTIL
.
GETNUMELEM
(
field
)
field
.
STNumGeometries
(
)
ST_NUMINTERIORRING
ST_NUMINTERIORRINGS
ST_NINTERIORRINGS
(
field
)
field
.
STNumInteriorRing
(
)
ST_NPOINTS
ST_NUMPOINTS
(
field
)
field
.
STNumPoints
(
)
ST_PERIMETER
(
field
)
ST_POINTN
(
field
,
field
)
field
.
STPointN
(
field
)
ST_SRID
(
field
)
field
.
SDO_SRID
field
.
STSrid
(
)
ST_STARTPOINT
(
field
)
SDO_LRS
.
GEOM_SEGMENT_START_PT
(
field
)
field
.
STStartPoint
(
)
ST_TRANSFORM
(
field
,
field
)
SDO_CS
.
TRANSFORM
(
field
,
field
)
ST_UNION
(
field
,
field
)
SDO_GEOM
.
SDO_UNION
(
field
,
field
,
field
)
field
.
STUnion
(
field
)
ST_X
(
field
)
field
.
SDO_POINT
.
X
STX
ST_XMIN
(
field
)
SDO_GEOM
.
SDO_MIN_MBR_ORDINATE
(
field
,
1
)
ST_XMAX
(
field
)
SDO_GEOM
.
SDO_MAX_MBR_ORDINATE
(
field
,
1
)
ST_Y
(
field
)
field
.
SDO_POINT
.
Y
STY
ST_YMIN
(
field
)
SDO_GEOM
.
SDO_MIN_MBR_ORDINATE
(
field
,
2
)
ST_YMAX
(
field
)
SDO_GEOM
.
SDO_MAX_MBR_ORDINATE
(
field
,
2
)
ST_Z
(
field
)
field
.
SDO_POINT
.
Z
STZ
ST_ZMIN
(
field
)
SDO_GEOM
.
SDO_MIN_MBR_ORDINATE
(
field
,
3
)
ST_ZMAX
(
field
)
SDO_GEOM
.
SDO_MAX_MBR_ORDINATE
(
field
,
3
)
SUB
SUBTRACT
MINUS
(
field
,
field
)
SUBSTR
SUBSTRING
(
field
,
field
,
field
)
SUBSTRING
(
field
FROM
field
FOR
field
)
SUBSTRING_INDEX
substringIndex
(
field
,
field
,
field
)
SUM
(
DISTINCT
ALL
field
)
keep
filter
over
SYS_CONNECT_BY_PATH
(
field
,
stringLiteral
)
TAN
(
field
)
TANH
(
field
)
timeLiteral
timestampLiteral
TIMESTAMP_DIFF
(
field
,
field
,
datePart
)
TIMEZONE
(
field
)
TIMEZONE_HOUR
(
field
)
TIMEZONE_MINUTE
(
field
)
TO_CHAR
(
field
,
field
)
TO_DATE
(
field
,
field
)
TO_NUMBER
(
field
)
TO_TIMESTAMP
(
field
,
field
)
TRANSLATE
OTRANSLATE
(
field
,
field
,
field
)
TRIM
trimBoth
(
field
,
field
)
TRIM
(
LEADING
L
TRAILING
T
BOTH
B
field
FROM
field
)
TRUNC
(
field
,
stringLiteral
)
TRUNC
TRUNCATE
TRUNCNUM
(
field
,
field
)
truthValue
UNIX_TIMESTAMP
(
field
)
UUID
UUID_GENERATE
UUID_STRING
GENGUID
GENERATE_UUID
GEN_RANDOM_UUID
RANDOM_UUID
SYS_GUID
SYSUUID
(
)
UUID_TO_BIN
UUIDStringToNum
(
field
)
UPPER
UCASE
(
field
)
VAR_POP
VARIANCE
VARP
varPop
(
ALL
field
)
filter
over
VAR_SAMP
VARIANCE_SAMP
VAR
varSamp
(
ALL
field
)
filter
over
WIDTH_BUCKET
widthBucket
(
field
,
field
,
field
,
field
)
XMLAGG
(
ALL
field
ORDER BY
sortFields
)
filter
over
XMLCOMMENT
(
field
)
XMLCONCAT
(
fields
)
XMLDOCUMENT
(
field
)
XMLELEMENT
(
NAME
identifier
,
XMLATTRIBUTES
(
field
AS
identifier
)
,
field
AS
identifier
)
XMLFOREST
(
field
AS
identifier
,
field
AS
identifier
)
XMLPARSE
(
DOCUMENT
CONTENT
field
)
XMLPI
(
NAME
identifier
,
field
)
XMLQUERY
(
field
PASSING
BY
REF
VALUE
field
RETURNING CONTENT
)
field
.
query
(
field
)
XMLSERIALIZE
(
DOCUMENT
CONTENT
field
)
XMLTYPE
(
field
)
XMLPI
(
NAME
identifier
,
field
)
XOR
(
field
,
field
)
YEAR
(
field
)
ZEROIFNULL
(
field
)
unsignedNumericLiteral
(
select
)
rowValueExpression
{
d
stringLiteral
}
{
t
stringLiteral
}
{
fn
term
}
{
ts
stringLiteral
}
identifier
(
fields
)
;
lambda1
=
fieldName
->
field
;
lambda2
=
(
fieldName
,
fieldName
)
->
field
;
jsonNull
=
NULL ON NULL
ABSENT ON NULL
;
jsonReturning
=
RETURNING
dataType
;
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
=
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
=
=
==
!=
<>
^=
>=
>
<=>
<=
<
EQ
NE
GT
GE
LT
LE
;
castDataType
=
dataType
SIGNED
INTEGER
UNSIGNED
INTEGER
;
dataType
=
AUTO_INCREMENT
(
fields
)
BIGINT
UNSIGNED
BINARY
VARYING
(
uint
)
BIT
(
uint
)
BLOB
(
uint
)
SUB_TYPE
0
BINARY
1
TEXT
BOOL
BOOLEAN
CHAR
CHARACTER
VARYING
(
uint
BYTE
CHAR
)
FOR BIT DATA
COLLATE
collationName
CHAR
CHARACTER
LARGE OBJECT
CLOB
(
uint
)
COLLATE
collationName
DATE
DEC
DECIMAL
(
*
uint
,
int
)
DOUBLE
PRECISION
(
uint
,
uint
)
ENUM
(
stringLiteral
,
stringLiteral
)
COLLATE
collationName
FLOAT
(
uint
,
uint
)
GEOGRAPHY
GEOMETRY
IDENTITY
(
fields
)
INT
INTEGER
(
uint
)
UNSIGNED
INTERVAL
YEAR
(
uint
)
TO
MONTH
DAY
(
uint
)
TO
SECOND
(
uint
)
JSON
JSONB
LONGBLOB
LONGTEXT
COLLATE
collationName
LONG NVARCHAR
(
uint
)
COLLATE
collationName
LONG VARBINARY
(
uint
)
LONG VARCHAR
(
uint
)
FOR BIT DATA
COLLATE
collationName
MEDIUMBLOB
MEDIUMINT
(
uint
)
UNSIGNED
MEDIUMTEXT
COLLATE
collationName
NATIONAL
CHAR
CHARACTER
VARYING
(
uint
)
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
UBIGINT
UINTEGER
USMALLINT
UTINYINT
UUID
VARCHAR
CHARACTER VARYING
(
uint
BYTE
CHAR
)
FOR BIT DATA
COLLATE
collationName
VARCHAR_IGNORECASE
(
uint
)
VARCHAR2
(
uint
BYTE
CHAR
)
COLLATE
collationName
VARBINARY
(
uint
)
XML
YEAR
(
uint
)
arrayDataType
;
arrayType
=
ARRAY
dataType
ARRAY
[
uint
]
^
dataType
[
]
ARRAY
(
dataType
)
ARRAY
<
dataType
>
;
constraintName
=
identifier
;
catalogName
=
name
;
domainName
=
name
;
schemaName
=
name
;
tableName
=
name
;
triggerName
=
name
;
typeName
=
name
;
functionName
=
name
;
indexName
=
name
;
parameterName
=
name
;
procedureName
=
name
;
sequenceName
=
name
;
synonymName
=
name
;
userName
=
name
;
roleName
=
name
;
fieldNames
=
,
fieldName
;
fieldName
=
name
;
collation
=
name
;
variableNames
=
,
variableName
;
variableName
=
name
;
labelName
=
name
;
name
=
identifier
.
!
identifier
;
binaryLiteral
=
X
'
characters
'
;
bitLiteral
=
B
'
0
1
'
;
stringLiteral
=
N
'
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
TO
MONTH
YEAR_MONTH
YEARS
QUARTER
QUARTERS
MONTH
MONTHS
WEEK
WEEKS
DAY
(
uint
)
TO
HOUR
MINUTE
SECOND
(
uint
)
DAY_HOUR
DAY_MINUTE
DAY_SECOND
DAYS
HOUR
(
uint
)
TO
MINUTE
SECOND
(
uint
)
HOUR_MINUTE
HOUR_SECOND
HOURS
MINUTE
(
uint
)
TO
SECOND
(
uint
)
MINUTE_SECOND
MINUTES
SECOND
(
uint
)
SECONDS
MILLISECOND
(
uint
)
MILLISECONDS
MICROSECOND
(
uint
)
MICROSECONDS
NANOSECOND
(
uint
)
NANOSECONDS
;
int
=
signedInteger
;
signedInteger
=
todo
;
uint
=
unsignedInteger
;
unsignedInteger
=
todo
;
unsignedNumericLiteral
=
todo
;
signedFloatLiteral
=
todo
;
unsignedFloatLiteral
=
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!