New versions: Dev (3.14) | Latest (3.13) | 3.12 | 3.11 | 3.10

SQL Parser Grammar

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

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

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.

The jOOQ Logo