All versions: 3.11 | 3.10 | Development versions: 3.12

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:

(The layout of the grammar and the grammar itself is still work in progress)

batch ::=

; query

query ::=

ddlStatement dmlStatement blockStatement

ddlStatement ::=

alterTableStatement alterIndexStatement alterSchemaStatement alterSequenceStatement alterSessionStatement alterViewStatement commentStatement createTableStatement createIndexStatement createSchemaStatement createSequenceStatement createViewStatement dropTableStatement dropIndexStatement dropViewStatement dropSequenceStatement dropSchemaStatement grantStatement renameStatement revokeStatement setCatalogStatement setSchemaStatement truncateStatement useStatement

dmlStatement ::=

deleteStatement insertStatement mergeStatement selectStatement updateStatement

proceduralStatement ::=

query nullStatement

blockStatement ::=

EXECUTE BLOCK AS BEGIN proceduralStatement ; proceduralStatement ; END DO stringLiteral

nullStatement ::=

NULL

alterTableStatement ::=

ALTER TABLE IF EXISTS tableName ADD CONSTRAINT constraintName constraint ADD constraint ADD COLUMN column ADD ( CONSTRAINT constraintName constraint constraint column , CONSTRAINT constraintName constraint constraint column ) ALTER MODIFY COLUMN identifier SET DATA TYPE dataType NOT NULL SET DROP NOT NULL RENAME TO AS identifier COMMENT = stringLiteral DROP COLUMN identifier CASCADE RESTRICT DROP CONSTRAINT constraintName RENAME COLUMN INDEX CONSTRAINT identifier TO AS identifier

alterIndexStatement ::=

ALTER INDEX IF EXISTS indexName RENAME TO AS indexName

alterSchemaStatement ::=

ALTER SCHEMA IF EXISTS schemaName RENAME TO AS schemaName

alterSequenceStatement ::=

ALTER SEQUENCE IF EXISTS sequenceName RENAME TO AS sequenceName RESTART WITH unsignedInteger SET GENERATOR sequenceName TO unsignedInteger

alterSessionStatement ::=

ALTER SESSION SET CURRENT_SCHEMA = schemaName

alterViewStatement ::=

ALTER VIEW IF EXISTS tableName RENAME TO AS tableName

commentStatement ::=

COMMENT ON COLUMN fieldName TABLE tableName VIEW tableName IS stringLiteral

createTableStatement ::=

CREATE GLOBAL TEMPORARY TABLE IF NOT EXISTS tableName AS select ( CONSTRAINT constraintName constraint constraint index column , CONSTRAINT constraintName constraint constraint index column ) ON COMMIT DELETE ROWS DROP PRESERVE ROWS COMMENT = stringLiteral

createIndexStatement ::=

CREATE UNIQUE INDEX IF NOT EXISTS indexName ON tableName ( sortFields ) INCLUDE ( identifiers ) WHERE condition

createSchemaStatement ::=

CREATE SCHEMA IF NOT EXISTS schemaName

createSequenceStatement ::=

CREATE SEQUENCE GENERATOR IF NOT EXISTS sequenceName

createViewStatement ::=

CREATE OR ALTER REPLACE VIEW IF NOT EXISTS tableName ( fieldNames ) AS select

dropTableStatement ::=

DROP TEMPORARY TABLE IF EXISTS tableName CASCADE RESTRICT

dropIndexStatement ::=

DROP INDEX IF EXISTS indexName ON tableName

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 ( identifiers ) values DEFAULT VALUES SET setClauses select ON DUPLICATE KEY UPDATE SET setClauses ON DUPLICATE KEY IGNORE ON CONFLICT ON CONSTRAINT constraintName ( fieldNames ) DO NOTHING UPDATE SET setClauses WHERE condition RETURNING * fields

values ::=

VALUES ( fields ) , ( fields )

updateStatement ::=

with UPDATE UPD tableName SET setClauses WHERE condition RETURNING * fields

setClauses ::=

, setClause

setClause ::=

fieldName = field

deleteStatement ::=

with DELETE DEL FROM tableName WHERE condition 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 ) PRIMARY KEY UNIQUE KEY INDEX CHECK ( condition ) REFERENCES constraintReferenceSpecification AUTO_INCREMENT AUTOINCREMENT IDENTITY ( signedInteger , signedInteger ) COMMENT stringLiteral

index ::=

KEY INDEX identifier ( sortFields )

constraint ::=

PRIMARY KEY ( fieldNames ) UNIQUE KEY INDEX ( fieldNames ) FOREIGN KEY ( fieldNames ) REFERENCES constraintReferenceSpecification CHECK ( condition ) ENABLE

constraintReferenceSpecification ::=

tableName ( fieldNames ) ON DELETE UPDATE CASCADE NO ACTION RESTRICT SET DEFAULT SET NULL

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 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

top ::=

TOP unsignedInteger PERCENT START AT unsignedInteger WITH TIES SKIP unsignedInteger FIRST unsignedInteger

selectList ::=

* selectField

selectField ::=

field AS identifier

tableExpression ::=

FROM tables WHERE condition connectBy groupBy HAVING condition WINDOW windows

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 BETWEEN rangeBound AND rangeBound rangeBound

orderBy ::=

ORDER SIBLINGS BY sortFields

seekFetch ::=

SEEK , field FETCH FIRST NEXT unsignedInteger PERCENT ROW ROWS ONLY WITH TIES LIMIT unsignedInteger PERCENT WITH TIES

offsetFetch ::=

OFFSET unsignedInteger ROW ROWS FETCH FIRST NEXT unsignedInteger PERCENT ROW ROWS ONLY WITH TIES LIMIT unsignedInteger PERCENT WITH TIES LIMIT unsignedInteger PERCENT 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 ::=

lateral unqualifiedJoin innerJoin outerJoin semiAntiJoin

unqualifiedJoin ::=

CROSS JOIN CROSS APPLY OUTER APPLY NATURAL LEFT RIGHT 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 ( sum , sum , sum )

tableHints ::=

WITH ( , tableHint )

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 @> concat concat *= concat concat =* concat row2 OVERLAPS row2

row2 ::=

ROW ( field , field )

concat ::=

|| collated

collated ::=

sum COLLATE collation

sum ::=

factor + - factor

factor ::=

exp * / % exp

exp ::=

^ unaryOps

unaryOps ::=

+ - term (+) :: dataType

term ::=

: identifier ? stringLiteral ANY ( field ) filter over ACOS ( sum ) ARRAY_AGG ( DISTINCT field ORDER BY sortFields ) filter over ASCII ( field ) ASIN ( sum ) ATAN ( sum ) ATN2 ATAN2 ( sum , sum ) 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 ( sum ) CHARINDEX ( field , field ) CHAR_LENGTH ( field ) CHOOSE ( , field ) COALESCE ( fields ) CONCAT ( fields ) CONVERT ( dataType , field ) COS ( sum ) COSH ( sum ) COT ( sum ) COTH ( sum ) COUNT ( * DISTINCT ALL field ) keep filter over CUME_DIST ( ) over ( fields ) withinGroup CURRVAL ( name stringLiteral ) CURRENT_DATE CURRENT_SCHEMA CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER dateLiteral DATEADD ( datePart , field , field ) DATE_TRUNC ( stringLiteral , field ) DAY ( field ) DECODE ( field , field , , field ) DENSE_RANK ( ) over ( fields ) withinGroup DEG DEGREE ( sum ) EXTRACT ( datePart FROM field ) EXP ( sum ) EVERY ( field ) filter over FLOOR ( sum ) FIELD ( field , , field ) FIRST_VALUE ( field ) 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 ) LOWER LCASE ( field ) LPAD ( field , field , field ) LTRIM ( field ) LEFT ( field , field ) LEN ( 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 OREPLACE ( 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 ) SECOND ( field ) SHL SHIFTLEFT ( field , field ) SHR SHIFTRIGHT ( field , field ) SIGN ( field ) SQR SQRT ( sum ) SIN ( sum ) SINH ( sum ) SPACE ( field ) STDDEV_POP ( field ) over STDDEV_SAMP ( field ) over STR_REPLACE ( field , field , field ) SUBSTRING ( field FROM sum FOR sum ) SUM ( DISTINCT ALL field ) keep filter TAN ( sum ) TANH ( sum ) timeLiteral timestampLiteral TO_CHAR ( field ) TO_DATE ( field , field ) TO_NUMBER ( field ) TO_TIMESTAMP ( field , field ) TRANSLATE OTRANSLATE ( field , field , field ) TRIM ( field ) TRUNC ( field , stringLiteral ) TRUNC ( sum , sum ) truthValue UPPER UCASE ( field ) VAR_POP ( field ) over VAR_SAMP ( field ) over WIDTH_BUCKET ( field , field , field , field ) binaryLiteral YEAR ( field ) unsignedNumericLiteral ( select ) ( fields ) { d stringLiteral } { t stringLiteral } { fn term } { ts stringLiteral }

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 ::=

FROM FIRST FROM LAST RESPECT NULLS IGNORE NULLS OVER identifier ( windowSpecification )

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 ::=

= != <> >= > <=> <= <

castDataType ::=

dataType SIGNED INTEGER UNSIGNED INTEGER

dataType ::=

BIGINT UNSIGNED BINARY ( unsignedInteger ) BIT ( unsignedInteger ) BLOB ( unsignedInteger ) BOOLEAN CHAR ( unsignedInteger ) BYTE CHAR COLLATE collationName CHARACTER ( unsignedInteger ) BYTE CHAR COLLATE collationName CLOB ( unsignedInteger ) COLLATE collationName DATE DECIMAL ( unsignedInteger , unsignedInteger ) DOUBLE PRECISION ( unsignedInteger , unsignedInteger ) ENUM ( stringLiteral , stringLiteral ) COLLATE collationName FLOAT ( unsignedInteger , unsignedInteger ) INT ( unsignedInteger ) UNSIGNED INTEGER ( unsignedInteger ) UNSIGNED LONGBLOB LONGTEXT COLLATE collationName LONG NVARCHAR ( unsignedInteger ) COLLATE collationName LONG VARBINARY ( unsignedInteger ) LONG VARCHAR ( unsignedInteger ) COLLATE collationName MEDIUMBLOB MEDIUMINT ( unsignedInteger ) UNSIGNED MEDIUMTEXT COLLATE collationName NCHAR ( unsignedInteger ) COLLATE collationName NCLOB COLLATE collationName NUMBER ( unsignedInteger , unsignedInteger ) NUMERIC ( unsignedInteger , unsignedInteger ) NVARCHAR ( unsignedInteger ) COLLATE collationName REAL ( unsignedInteger , unsignedInteger ) SERIAL SERIAL4 SERIAL8 SET ( stringLiteral , stringLiteral ) COLLATE collationName SMALLINT ( unsignedInteger ) UNSIGNED TEXT ( unsignedInteger ) COLLATE collationName TIMESTAMP ( unsignedInteger ) WITH WITHOUT TIME ZONE TIMESTAMPTZ ( unsignedInteger ) TIME ( unsignedInteger ) WITH WITHOUT TIME ZONE TIMETZ ( unsignedInteger ) TINYBLOB TINYINT ( unsignedInteger ) UNSIGNED TINYTEXT COLLATE collationName UUID VARCHAR ( unsignedInteger BYTE CHAR ) COLLATE collationName VARCHAR2 ( unsignedInteger ) BYTE CHAR COLLATE collationName CHARACTER VARYING ( unsignedInteger ) BYTE CHAR COLLATE collationName VARBINARY ( unsignedInteger )

constraintName ::=

identifier

catalogName ::=

name

schemaName ::=

name

tableName ::=

name

indexName ::=

name

sequenceName ::=

name

userName ::=

name

roleName ::=

name

fieldNames ::=

, fieldName

fieldName ::=

name

collation ::=

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

intervalLiteral ::=

INTERVAL stringLiteral

signedInteger ::=

todo

unsignedInteger ::=

todo

unsignedNumericLiteral ::=

todo

identifiers ::=

, identifier

identifier ::=

identifierStart identifierPart " doubleQuotedIdentifierPart doubleQuotedIdentifierPart " ` backtickQuotedIdentifierPart backtickQuotedIdentifierPart ` [ brackedQuotedIdentifierPart 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.

The jOOQ Logo