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
ddlStatement ::=
alterTableStatement
alterIndexStatement
alterSchemaStatement
alterSequenceStatement
alterViewStatement
createTableStatement
createIndexStatement
createSchemaStatement
createSequenceStatement
createViewStatement
dropTableStatement
dropIndexStatement
dropViewStatement
dropSequenceStatement
dropSchemaStatement
renameStatement
truncateStatement
dmlStatement ::=
deleteStatement
insertStatement
mergeStatement
selectStatement
updateStatement
alterTableStatement ::=
ALTER TABLE
IF EXISTS
tableName
ADD CONSTRAINT
constraintName
constraint
ADD
constraint
ADD COLUMN
identifier
dataType
NOT
NULL
DEFAULT
concat
ALTER
MODIFY
COLUMN
identifier
dataType
NOT
NULL
SET
DROP
NOT NULL
DROP COLUMN
identifier
CASCADE
RESTRICT
DROP CONSTRAINT
constraintName
RENAME
COLUMN
INDEX
CONSTRAINT
identifier
TO
identifier
alterIndexStatement ::=
ALTER INDEX
IF EXISTS
indexName
RENAME TO
indexName
alterSchemaStatement ::=
ALTER SCHEMA
IF EXISTS
schemaName
RENAME TO
schemaName
alterSequenceStatement ::=
ALTER SEQUENCE
IF EXISTS
sequenceName
RENAME TO
sequenceName
RESTART
WITH
unsignedInteger
alterViewStatement ::=
ALTER VIEW
IF EXISTS
tableName
RENAME TO
tableName
createTableStatement ::=
CREATE
GLOBAL
TEMPORARY
TABLE
IF NOT EXISTS
tableName
AS
select
(
,
column
,
constraint
)
ON COMMIT
DELETE ROWS
DROP
PRESERVE ROWS
createIndexStatement ::=
CREATE
UNIQUE
INDEX
IF NOT EXISTS
indexName
ON
tableName
(
identifiers
)
WHERE
condition
createSchemaStatement ::=
CREATE SCHEMA
IF NOT EXISTS
schemaName
createSequenceStatement ::=
CREATE SEQUENCE
IF NOT EXISTS
sequenceName
createViewStatement ::=
CREATE VIEW
IF NOT EXISTS
tableName
(
fieldNames
)
AS
select
dropTableStatement ::=
DROP TABLE
IF EXISTS
tableName
CASCADE
RESTRICT
dropIndexStatement ::=
DROP INDEX
IF EXISTS
indexName
ON
tableName
dropViewStatement ::=
DROP VIEW
IF EXISTS
tableName
dropSequenceStatement ::=
DROP SEQUENCE
IF EXISTS
sequenceName
dropSchemaStatement ::=
DROP SCHEMA
IF EXISTS
schemaName
CASCADE
RESTRICT
renameStatement ::=
RENAME
COLUMN
fieldName
TO
fieldName
INDEX
indexName
TO
indexName
SCHEMA
schemaName
TO
schemaName
SEQUENCE
sequenceName
TO
sequenceName
VIEW
tableName
TO
tableName
TABLE
tableName
TO
tableName
truncateStatement ::=
TRUNCATE TABLE
tableName
CONTINUE IDENTITY
RESTART IDENTITY
CASCADE
RESTRICT
selectStatement ::=
select
values
correlationName
insertStatement ::=
INSERT INTO
tableName
(
identifiers
)
values
DEFAULT VALUES
SET
setClauses
select
ON DUPLICATE KEY UPDATE
SET
setClauses
ON DUPLICATE KEY IGNORE
ON CONFLICT
(
fieldNames
)
DO
NOTHING
UPDATE
SET
setClauses
WHERE
condition
RETURNING
*
fields
values ::=
VALUES
,
(
fields
)
updateStatement ::=
UPDATE
tableName
SET
setClauses
WHERE
condition
RETURNING
*
fields
setClauses ::=
,
setClause
setClause ::=
fieldName
=
field
deleteStatement ::=
DELETE
FROM
tableName
WHERE
condition
RETURNING
*
fields
mergeStatement ::=
MERGE INTO
tableName
USING
(
select
)
AS
identifier
ON
condition
WHEN MATCHED THEN UPDATE
SET
setClauses
WHEN NOT MATCHED THEN INSERT
(
identifiers
)
VALUES
(
fields
)
column ::=
identifier
dataType
NOT
NULL
DEFAULT
ON NULL
concat
GENERATED
ALWAYS
BY DEFAULT
ON NULL
AS IDENTITY
(
identity
)
PRIMARY KEY
UNIQUE
CHECK
(
condition
)
AUTO_INCREMENT
AUTOINCREMENT
constraint ::=
PRIMARY KEY
(
fieldNames
)
UNIQUE
(
fieldNames
)
FOREIGN KEY
(
fieldNames
)
REFERENCS
(
fieldNames
)
ON
DELETE
UPDATE
CASCADE
NO ACTION
RESTRICT
SET DEFAULT
SET NULL
CHECK
(
condition
)
identity ::=
START WITH
LIMIT VALUE
unsignedInteger
INCREMENT BY
unsignedInteger
MAXVALUE
unsignedInteger
NOMAXVALUE
MINVALUE
unsignedInteger
NOMINVALUE
CACHE
unsignedInteger
NOCACHE
CYCLE
NOCYCLE
ORDER
NOORDER
with ::=
WITH
RECURSIVE
,
commonTableExpression
commonTableExpression ::=
identifier
(
identifiers
)
AS
(
select
)
select ::=
with
select
queryExpressionBody
orderBy
offsetFetch
forUpdate
queryExpressionBody ::=
queryTerm
UNION
EXCEPT
MINUS
ALL
DISTINCT
queryTerm
queryTerm ::=
queryPrimary
INTERSECT
ALL
DISTINCT
queryTerm
queryPrimary ::=
(
select
)
SELECT
distinct
top
selectList
INTO
tableName
tableExpression
distinct ::=
DISTINCT
UNIQUE
ON
(
fields
)
ALL
top ::=
TOP
unsignedInteger
START AT
unsignedInteger
WITH TIES
SKIP
unsignedInteger
FIRST
unsignedInteger
selectList ::=
*
selectField
selectField ::=
field
AS
identifier
tableExpression ::=
FROM
tables
connectBy
WHERE
condition
groupBy
HAVING
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
)
orderBy ::=
ORDER
SIBLINGS
BY
sortFields
offsetFetch ::=
OFFSET
unsignedInteger
ROW
ROWS
FETCH
FIRST
NEXT
unsignedInteger
ROW
ROWS
ONLY
WITH TIES
LIMIT
unsignedInteger
WITH TIES
LIMIT
unsignedInteger
WITH TIES
OFFSET
unsignedInteger
,
unsignedInteger
forUpdate ::=
FOR SHARE
FOR UPDATE
OF
fields
NOWAIT
WAIT
unsignedInteger
SKIP LOCKED
sortFields ::=
,
sortField
sortField ::=
field
ASC
DESC
NULLS FIRST
NULLS LAST
tables ::=
,
table
table ::=
tableFactor
unqualifiedJoin
innerJoin
outerJoin
semiAntiJoin
unqualifiedJoin ::=
CROSS JOIN
CROSS APPLY
OUTER APPLY
NATURAL
LEFT
RIGHT
OUTER
JOIN
tableFactor
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
tableFactor ::=
LATERAL
(
select
)
correlationName
(
select
)
correlationName
values
correlationName
tableName
versions
correlationName
(
table
)
correlationName
pivot ::=
todo
versions ::=
VERSIONS BETWEEN
SCN
TIMESTAMP
MINVALUE
field
AND
MAXVALUE
field
AS OF
SCN
TIMESTAMP
joinQualification ::=
ON
condition
USING
(
identifiers
)
correlationName ::=
AS
identifier
(
identifiers
)
fields ::=
,
field
field ::=
or
condition ::=
or
or ::=
OR
and
and ::=
AND
not
not ::=
NOT
predicate
predicate ::=
EXISTS
(
select
)
concat
comparator
ALL
ANY
SOME
(
select
)
concat
IS
NOT
NULL
DISTINCT FROM
concat
IN
(
select
fields
)
BETWEEN
SYMMETRIC
concat
AND
concat
LIKE
concat
ESCAPE
characterLiteral
row2
OVERLAPS
row2
row2 ::=
ROW
(
field
,
field
)
concat ::=
||
sum
sum ::=
factor
+
-
factor
factor ::=
exp
*
/
%
exp
exp ::=
^
unaryOps
unaryOps ::=
+
-
term
::
dataType
term ::=
:
identifier
?
stringLiteral
ASCII
(
field
)
ACOS
(
sum
)
ASIN
(
sum
)
ATAN
(
sum
)
ATN2
ATAN2
(
sum
,
sum
)
ARRAY
[
fields
]
AVG
(
DISTINCT
ALL
field
)
keep
filter
over
BIT_LENGTH
(
field
)
CONCAT
(
fields
)
CURRENT_SCHEMA
CURRENT_USER
CHARINDEX
(
field
,
field
)
CHAR_LENGTH
(
field
)
CEIL
CEILING
(
sum
)
COSH
(
sum
)
COS
(
sum
)
COTH
(
sum
)
COT
(
sum
)
COUNT
(
*
DISTINCT
ALL
field
)
keep
filter
over
CURRVAL
(
name
stringLiteral
)
CURRENT_TIMESTAMP
CURRENT_TIME
CURRENT_DATE
case
CAST
(
field
AS
dataType
)
COALESCE
(
fields
)
CUME_DIST
(
)
over
(
fields
)
withinGroup
dateLiteral
DENSE_RANK
(
)
over
(
fields
)
withinGroup
DAY
(
field
)
DEG
DEGREE
(
sum
)
EXTRACT
(
datePart
FROM
field
)
EXP
(
sum
)
EVERY
(
field
)
filter
over
ANY
(
field
)
filter
over
FLOOR
(
sum
)
FIRST_VALUE
(
field
)
over
GREATEST
(
fields
)
GROUP_CONCAT
(
DISTINCT
field
ORDER BY
sortFields
SEPARATOR
stringLiteral
)
GROUP_ID
(
)
GROUPING_ID
(
fields
)
GROUPING
(
fields
)
HOUR
(
field
)
INSTR
(
field
,
field
)
IFNULL
(
field
,
field
)
ISNULL
(
field
,
field
)
LOWER
LCASE
(
field
)
LPAD
(
field
,
field
,
field
)
LTRIM
(
field
)
LEFT
(
field
,
field
)
LENGTH
(
field
)
LN
(
sum
)
LOG
(
sum
,
unsignedInteger
)
LEVEL
LEAST
(
fields
)
LEAD
(
field
,
unsignedInteger
,
field
)
over
LAG
(
field
,
unsignedInteger
,
field
)
over
LAST_VALUE
(
field
)
over
LISTAGG
(
field
,
stringLiteral
)
withinGroup
over
MIN
(
DISTINCT
ALL
field
)
keep
filter
over
MAX
(
DISTINCT
ALL
field
)
keep
filter
over
MEDIAN
(
field
)
filter
over
MOD
(
field
,
field
)
MODE
(
)
withinGroup
over
MONTH
(
field
)
MINUTE
(
field
)
MID
(
field
,
field
,
field
)
MD5
(
field
)
NVL
(
field
,
field
)
NVL2
(
field
,
field
,
field
)
NULLIF
(
field
,
field
)
NTILE
(
unsignedInteger
)
over
NTH_VALUE
(
field
,
unsignedInteger
)
over
NEXT VALUE FOR
sequenceName
NEXTVAL
(
name
stringLiteral
)
OCTET_LENGTH
(
field
)
POSITION
(
field
IN
field
)
PERCENT_RANK
(
)
over
(
fields
)
withinGroup
PERCENTILE_CONT
PERCENTILE_DISC
(
unsignedNumericLiteral
)
withinGroup
over
POW
POWER
(
field
,
field
)
PRIOR
concat
REGR_SLOPE
REGR_INTERCEPT
REGR_COUNT
REGR_R2
REGR_AVGX
REGR_AVGY
REGR_SXX
REGR_SYY
REGR_SXY
(
sum
,
sum
)
REPLACE
(
field
,
field
,
field
)
REPEAT
(
field
,
field
)
REVERSE
(
field
)
RPAD
(
field
,
field
,
field
)
RTRIM
(
field
)
RIGHT
(
field
,
field
)
ROW_NUMBER
(
)
over
RANK
(
)
over
(
fields
)
withinGroup
ROUND
(
field
,
unsignedInteger
)
ROWNUM
RAD
RADIAN
(
sum
)
ROW
(
fields
)
SUBSTR
SUBSTRING
(
field
,
sum
,
sum
)
SUBSTRING
(
field
FROM
sum
FOR
sum
)
SPACE
(
field
)
SECOND
(
field
)
SIGN
(
field
)
SQR
SQRT
(
sum
)
SIN
(
sum
)
SINH
(
sum
)
STDDEV_POP
(
field
)
over
STDDEV_SAMP
(
field
)
over
SUM
(
DISTINCT
ALL
field
)
keep
filter
truthValue
TRIM
(
field
)
TRUNC
(
sum
,
sum
)
TAN
(
sum
)
TANH
(
sum
)
timeLiteral
timestampLiteral
UPPER
UCASE
(
field
)
VAR_POP
(
field
)
over
VAR_SAMP
(
field
)
over
WIDTH_BUCKET
(
field
,
field
,
field
,
field
)
binaryLiteral
YEAR
(
field
)
unsignedNumericLiteral
(
select
)
(
fields
)
truthValue ::=
TRUE
FALSE
NULL
datePart ::=
YEAR
MONTH
DAY
HOUR
MINUTE
SECOND
keep ::=
KEEP
(
DENSE_RANK
FIRST
LAST
ORDER BY
sortFields
)
filter ::=
FILTER
(
WHERE
condition
)
over ::=
RESPECT NULLS
IGNORE NULLS
OVER
identifier
(
PARTITION BY
fields
ORDER BY
sortFields
ROWS
RANGE
BETWEEN
rangeBound
AND
rangeBound
rangeBound
)
withinGroup ::=
WITHIN GROUP
(
ORDER BY
sortFields
)
rangeBound ::=
UNBOUNDED
unsignedInteger
PRECEDING
FOLLOWING
CURRENT ROW
case ::=
CASE
WHEN
condition
THEN
field
field
WHEN
field
THEN
field
ELSE
field
END
comparator ::=
=
!=
<>
>=
>
<=>
<=
<
dataType ::=
BIGINT
UNSIGNED
BINARY
(
unsignedInteger
)
BIT
BLOB
BOOLEAN
CHAR
(
unsignedInteger
)
CLOB
(
unsignedInteger
)
DATE
DECIMAL
(
unsignedInteger
,
unsignedInteger
)
DOUBLE
PRECISION
FLOAT
INT
UNSIGNED
INTEGER
UNSIGNED
LONGBLOB
LONGTEXT
LONG NVARCHAR
(
unsignedInteger
)
LONG VARBINARY
(
unsignedInteger
)
LONG VARCHAR
(
unsignedInteger
)
MEDIUMBLOB
MEDIUMINT
UNSIGNED
MEDIUMTEXT
NCHAR
(
unsignedInteger
)
NCLOB
NUMBER
(
unsignedInteger
,
unsignedInteger
)
NUMERIC
(
unsignedInteger
,
unsignedInteger
)
NVARCHAR
(
unsignedInteger
)
REAL
(
unsignedInteger
)
SERIAL
SERIAL4
SERIAL8
SMALLINT
UNSIGNED
TEXT
TIMESTAMP
WITH TIME ZONE
TIMESTAMPTZ
TIME
WITH TIME ZONE
TIMETZ
TINYBLOB
TINYINT
UNSIGNED
TINYTEXT
UUID
VARCHAR
(
unsignedInteger
)
VARCHAR2
(
unsignedInteger
)
CHARACTER VARYING
(
unsignedInteger
)
VARBINARY
(
unsignedInteger
)
constraintName ::=
identifier
catalogName ::=
name
schemaName ::=
name
tableName ::=
name
indexName ::=
name
sequenceName ::=
name
fieldNames ::=
,
fieldName
fieldName ::=
name
name ::=
.
identifier
stringLiteral ::=
'
character
'
q'[
characters
]'
q'{
characters
}'
q'(
characters
)'
q'<
characters
>'
q'
nonSpaceCharacter
characters
nonSpaceCharacter
'
characterLiteral ::=
'
character
'
dateLiteral ::=
DATE
stringLiteral
timeLiteral ::=
TIME
stringLiteral
timestampLiteral ::=
TIMESTAMP
stringLiteral
signedInteger ::=
todo
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!