previous : next

Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 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:

batch ::=

; query

query ::=

ddlStatement dmlStatement blockStatement

ddlStatement ::=

alterDatabaseStatement alterDomainStatement alterTableStatement alterIndexStatement alterSchemaStatement alterSequenceStatement alterSessionStatement alterTypeStatement alterViewStatement commentStatement createDatabaseStatement createDomainStatement createTableStatement createTypeStatement createIndexStatement createSchemaStatement createSequenceStatement createViewStatement dropDatabaseStatement dropDomainStatement 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

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 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 dataType CONSTRAINT constraintName NOT NULL DROP NOT NULL SET NOT NULL SET DEFAULT concat DROP DEFAULT RENAME TO AS identifier column ( column ) CONSTRAINT constraintName constraintState 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

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

commentStatement ::=

COMMENT ON COLUMN fieldName TABLE tableName VIEW tableName IS stringLiteral

createDatabaseStatement ::=

CREATE DATABASE IF NOT EXISTS catalogName

createDomainStatement ::=

CREATE DOMAIN IF NOT EXISTS domainName AS dataType DEFAULT concat constraint CONSTRAINT constraintName constraint

createIndexStatement ::=

CREATE UNIQUE INDEX IF NOT EXISTS indexName indexType ON tableName indexType ( sortFields ) indexType INCLUDE COVERING STORING ( identifiers ) WHERE condition EXCLUDE NULL KEYS

createSchemaStatement ::=

CREATE SCHEMA IF NOT EXISTS schemaName AUTHORIZATION userName schemaName AUTHORIZATION userName

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

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 )

createViewStatement ::=

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

dropDatabaseStatement ::=

DROP DATABASE IF EXISTS catalogName

dropDomainStatement ::=

DROP DOMAIN IF EXISTS domainName CASCADE RESTRICT

dropIndexStatement ::=

DROP INDEX IF EXISTS indexName ON tableName CASCADE RESTRICT

dropSequenceStatement ::=

DROP SEQUENCE GENERATOR IF EXISTS sequenceName

dropSchemaStatement ::=

DROP SCHEMA IF EXISTS schemaName CASCADE RESTRICT

dropTableStatement ::=

DROP TEMPORARY TABLE IF EXISTS tableName CASCADE RESTRICT

dropTypeStatement ::=

DROP TYPE IF EXISTS , typeName CASCADE RESTRICT

dropViewStatement ::=

DROP VIEW IF EXISTS tableName

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 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 ) WHERE condition 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 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 concat GENERATED ALWAYS BY DEFAULT ON NULL AS IDENTITY ( identity ) CONSTRAINT constraintName NOT NULL 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 identifier

constraint ::=

NOT NULL PRIMARY KEY indexType CLUSTERED NONCLUSTERED ( fieldNames ) 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

with ::=

WITH RECURSIVE , commonTableExpression

commonTableExpression ::=

identifier ( identifiers ) AS NOT MATERIALIZED ( select )

select ::=

with queryExpressionBody orderBy seekFetch offsetFetch forXML forJSON 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 ROWS uint TO uint

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 ) , ELEMENTS 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 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 JSON_TABLE ( field , field COLUMNS ( , jsonTableColumn ) ) XMLTABLE ( field PASSING BY REF VALUE field COLUMNS , xmlTableColumn )

jsonTableColumn ::=

identifier dataType PATH stringLiteral FOR ORDINALITY

xmlTableColumn ::=

identifier dataType PATH stringLiteral FOR ORDINALITY

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 ) JSON_EXISTS ( field , field TRUE FALSE UNKNOWN ERROR ON ERROR ) XMLEXISTS ( field PASSING BY REF VALUE field ) UNIQUE ( select ) concat comparator ALL ANY SOME ( select fields ) concat IS NOT NULL DOCUMENT 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 =* 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 ? binaryLiteral bitLiteral stringLiteral ANY ( field ) filter over ACOS ( numericOp ) ARRAY_AGG ( DISTINCT field ORDER BY sortFields ) filter over ARRAY_GET ( field , field ) 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 ) CARDINALITY ( 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 ) INSERT ( field , field , field , field ) INSTR ( field , field ) intervalLiteral IF IIF ( condition , field , field ) ISNULL ( field , field ) JSON_ARRAY ( fields NULL ON NULL ABSENT ON NULL ) JSON_ARRAYAGG ( field ORDER BY sortFields NULL ON NULL ABSENT ON NULL ) JSON_OBJECT ( jsonEntries NULL ON NULL ABSENT ON NULL ) JSON_OBJECTAGG ( jsonEntry NULL ON NULL ABSENT ON NULL ) JSON_VALUE ( field , field ERROR NULL DEFAULT field ON EMPTY ON ERROR ) 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 ) OVERLAY ( field PLACING field FROM field FOR 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 REGEXP_REPLACE ( field , field , field ) REGEXP_REPLACE_ALL ( field , field , field ) REGEXP_REPLACE_FIRST ( field , field , field ) 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 , 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 ) XMLAGG ( field ORDER BY sortFields ) filter over XMLCOMMENT ( field ) XMLCONCAT ( , field ) XMLDOCUMENT ( field ) XMLELEMENT ( NAME identifier , XMLATTRIBUTES ( field AS identifier ) , field AS identifier ) XMLFOREST ( field AS identifier , field AS identifier ) XMLPARSE ( DOCUMENT CONTENT field ) XMLQUERY ( field PASSING BY REF VALUE field RETURNING CONTENT ) XMLPI ( NAME identifier , field ) 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 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 ) 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 VARCHAR_IGNORECASE ( uint ) VARCHAR2 ( uint BYTE CHAR ) COLLATE collationName VARBINARY ( uint ) XML arraySuffix

arraySuffix ::=

ARRAY [ uint ]

constraintName ::=

identifier

catalogName ::=

name

domainName ::=

name

schemaName ::=

name

tableName ::=

name

typeName ::=

name

indexName ::=

name

sequenceName ::=

name

userName ::=

name

roleName ::=

name

fieldNames ::=

, fieldName

fieldName ::=

name

collation ::=

name

name ::=

. 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 YEARS QUARTER QUARTERS MONTH MONTHS WEEK WEEKS DAY TO HOUR MINUTE SECOND DAYS HOUR TO MINUTE SECOND HOURS MINUTE TO SECOND 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.


previous : next

Feedback

Do you have any feedback about this page? We'd love to hear it!

↑ Back to top

Community

Support

Legal

Documentation

The jOOQ Logo