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

ddlStatement ::=

alterDatabaseStatement alterDomainStatement alterIndexStatement alterSchemaStatement alterSequenceStatement alterSessionStatement alterTableStatement alterTypeStatement alterViewStatement commentStatement createDatabaseStatement createDomainStatement createFunctionStatement createIndexStatement createProcedureStatement createSchemaStatement createSequenceStatement createTableStatement createTriggerStatement createTypeStatement createViewStatement dropDatabaseStatement dropDomainStatement dropFunctionStatement dropIndexStatement dropProcedureStatement dropTableStatement dropTypeStatement dropSchemaStatement dropSequenceStatement 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 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 = IS 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

commentStatement ::=

COMMENT ON TABLE tableName IS stringLiteral columnName IS stringLiteral , columnName IS stringLiteral COLUMN fieldName 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

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

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 TO EXCLUDED WHERE condition ON DUPLICATE KEY IGNORE ON CONFLICT ON CONSTRAINT constraintName ( fieldNames ) WHERE condition DO NOTHING UPDATE SET setClauses ALL 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 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 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 ) 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 ( numericOp , numericOp , numericOp ) TABLE ( GENERATOR ( ROWCOUNT => unsignedInteger ) )

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 ( 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_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_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 ( field , field )

concat ::=

|| collated

collated ::=

numericOp COLLATE collation

numericOp ::=

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_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 ) filter over ACOS ( numericOp ) ACOSH ( numericOp ) ACOTH ( numericOp ) ARRAY [ fields ] ( select ) ARRAY_AGG ( DISTINCT ALL field ORDER BY sortFields ) filter over ARRAY_APPEND ( field , field ) ARRAY_CONSTRUCT ( fields ) ARRAY_CONSTRUCT_COMPACT ( fields ) ARRAY_GET ( field , field ) ARRAY_OVERLAP ARRAYS_OVERLAP ( field , field ) ARRAY_PREPEND ( field , field ) ARRAY_REMOVE ( field , field ) ARRAY_REPLACE ( field , field , field ) ASC ASCII ASCII_VAL ( field ) ASCII_CHAR ( field ) ASIN ( numericOp ) ASINH ( numericOp ) ATAN ( numericOp ) ATANH ( numericOp ) ATN2 ATAN2 ( numericOp , numericOp ) AVG ( DISTINCT ALL field ) keep filter over BIN_SHL ( field , field ) BIN_SHR ( field , field ) BIT_GET BITGET GET_BIT GETBIT ( field , field ) BIT_SET BITSET SET_BIT SETBIT ( field , field , field ) BIT_LENGTH ( field ) BITCOUNT BIT_COUNT ( field ) BIN_AND BIN_AND_AGG BITAND BITAND_AGG BIT_AND BIT_AND_AGG BITWISE_AND_AGG ( DISTINCT ALL field ) filter over BIN_NAND BITNAND BIT_NAND BITWISE_AND ( field , field ) BIN_NAND BIN_NAND_AGG BITNAND BITNAND_AGG BIT_NAND BIT_NAND_AGG ( DISTINCT ALL field ) filter over BIN_NOR BITNOR BIT_NOR ( field , field ) BIN_NOR BIN_NOR_AGG BITNOR BITNOR_AGG BIT_NOR BIT_NOR_AGG ( DISTINCT ALL field ) filter over BIN_NOT BITNOT BIT_NOT BITWISE_NOT ( field ) BIN_OR BITOR BIT_OR BITWISE_OR ( field , field ) BIN_OR BIN_OR_AGG BITOR BITOR_AGG BIT_OR BIT_OR_AGG BITWISE_OR_AGG ( DISTINCT ALL field ) filter over BIN_XNOR BITXNOR BIT_XNOR ( field , field ) BIN_XNOR BIN_XNOR_AGG BITXNOR BITXNOR_AGG BIT_XNOR BIT_XNOR_AGG ( ALL field ) filter over BIN_XOR BITXOR BIT_XOR BITWISE_XOR ( field , field ) BIN_XOR BIN_XOR_AGG BITXOR BITXOR_AGG BIT_XOR BIT_XOR_AGG ( 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 ) CEIL CEILING ( numericOp ) 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 ( numericOp ) COSH ( numericOp ) COT ( numericOp ) COTH ( numericOp ) COUNT ( DISTINCT ALL tableName . * field rowValueExpression ) keep filter over COUNTIF COUNT_IF ( condition ) over CORR ( ALL numericOp , numericOp ) filter over COVAR_POP ( ALL numericOp , numericOp ) filter over COVAR_SAMP ( ALL numericOp , numericOp ) 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 ( numericOp ) DIGITS ( field ) ELEMENT_AT ( field , field ) ENDS_WITH ( field , field ) EPOCH ( field ) EXTRACT ( datePart FROM field ) EXP ( numericOp ) FLOOR ( numericOp ) 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_BUILD_ARRAY JSONB_BUILD_ARRAY ( fields ) JSON_BUILD_OBJECT JSONB_BUILD_OBJECT ( fields ) JSON_KEYS JSONB_KEYS ( 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 ( field , field , field ) LTRIM ( field , field ) LEFT ( field , field ) LEN ( field ) LENGTH ( field ) LN LOGN ( numericOp ) LOCATE ( field , field , field ) LOCATE_IN_STRING ( field , field , field ) LOG ( numericOp , numericOp ) LOG10 ( numericOp ) LEVEL LEAST MIN MINVALUE ( fields ) LEAD ( field , uint , field RESPECT NULLS IGNORE NULLS ) RESPECT NULLS IGNORE NULLS over LAG ( 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 ( DISTINCT ALL field ) keep filter over MD5 HASH_MD5 ( field ) MEDIAN ( field ) filter over MICROSECOND ( field ) MID ( field , field , field ) MILLISECOND ( field ) MIN ( DISTINCT ALL field ) keep filter over MINUTE ( field ) MOD ( field , field ) MODE ( ) withinGroup over MONTH ( field ) MUL PRODUCT ( DISTINCT ALL field ) keep filter over MULTISET ( select ) MULTISET_AGG ( ALL field ORDER BY sortFields ) filter over 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 ) OVERLAY ( field PLACING field FROM field FOR field ) PI ( ) POSITION ( field IN field ) PERCENT_RANK ( ) over ( ALL fields ) withinGroup PERCENTILE_CONT PERCENTILE_DISC ( ALL field ) withinGroup 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 numericOp , numericOp ) filter over RAD RADIAN RADIANS ( numericOp ) 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 ( field , field , field ) REPLACE_REGEXPR ( field IN field WITH field OCCURRENCE ALL unsignedInteger ) REVERSE STRREVERSE ( field ) RIGHT ( field , field ) ROUND ( field , uint ) ROW_NUMBER ROWNUMBER ( ) over ROWNUM RPAD ( field , field , field ) RTRIM ( field , field ) SCHEMA ( ) SECOND ( field ) SEQ4 SEQ8 ( ) SHL SHIFTLEFT LSHIFT LEFT_SHIFT BIT_LSHIFT BITWISE_LEFT_SHIFT ( field , field ) SHR SHIFTRIGHT RSHIFT RIGHT_SHIFT BIT_RSHIFT BITWISE_RIGHT_SHIFT ( field , field ) SIGN SGN ( field ) SQR SQRT ( numericOp ) SQUARE ( numericOp ) SIN ( numericOp ) SINH ( numericOp ) SPLIT_PART ( field , field , field ) SPACE ( field ) STARTS_WITH ( field , field ) STDDEV STDDEV_POP STDEVP ( ALL field ) over STDDEV_SAMP STDEV STDEV_SAMP ( ALL field ) over STR_REPLACE ( field , field , field ) STRING_AGG ( DISTINCT ALL field , stringLiteral ) withinGroup over ST_AREA ( field ) SDO_GEOM . SDO_AREA ( field , field ) field . STArea ( ) ST_ASBINARY ( field ) field . Get_WKB ( ) STAsBinary ( ) ST_ASTEXT ( field ) field . Get_WKT ( ) STAsText ( ) 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_DISTANCE ( field , field ) SDO_GEOM . SDO_DISTANCE ( field , field , field ) field . STDistance ( field ) ST_ENDPOINT ( field ) field . STEndPoint ( ) ST_EXTERIORRING ( field ) field . STExteriorRing ( ) ST_GEOMETRYN ( 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 ( field ) field . STNumInteriorRing ( ) ST_NUMPOINTS ( field ) field . STNumPoints ( ) ST_POINTN ( field , field ) field . STPointN ( field ) ST_SRID ( field ) field . SDO_SRID field . STSrid ( ) ST_STARTPOINT ( field ) field . STStartPoint ( ) ST_UNION ( field , field ) SDO_GEOM . SDO_UNION ( field , field , field ) field . STUnion ( field ) ST_X ( field ) field . SDO_POINT . X STX ST_Y ( field ) field . SDO_POINT . Y STY ST_Z ( field ) field . SDO_POINT . Z STZ SUBSTR SUBSTRING ( field , numericOp , numericOp ) SUBSTRING ( field FROM numericOp FOR numericOp ) SUBSTRING_INDEX ( field , field , field ) SUM ( DISTINCT ALL field ) keep filter over SYS_CONNECT_BY_PATH ( field , stringLiteral ) TAN ( numericOp ) TANH ( numericOp ) 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 ( field , field ) TRIM ( LEADING L TRAILING T BOTH B field FROM field ) TRUNC ( field , stringLiteral ) TRUNC TRUNCATE TRUNCNUM ( numericOp , numericOp ) truthValue UNIX_TIMESTAMP ( field ) UUID UUID_GENERATE UUID_STRING GENGUID GENERATE_UUID GEN_RANDOM_UUID RANDOM_UUID SYS_GUID SYSUUID ( ) UPPER UCASE ( field ) VAR_POP VARIANCE VARP ( ALL field ) filter over VAR_SAMP VARIANCE_SAMP VAR ( ALL field ) filter over WIDTH_BUCKET ( 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 ) YEAR ( field ) ZEROIFNULL ( field ) unsignedNumericLiteral ( select ) rowValueExpression { d stringLiteral } { t stringLiteral } { fn term } { ts stringLiteral } identifier ( fields )

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

ARRAY 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 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 ) arraySuffix

arraySuffix ::=

ARRAY [ uint ]

constraintName ::=

identifier

catalogName ::=

name

domainName ::=

name

schemaName ::=

name

tableName ::=

name

triggerName ::=

name

typeName ::=

name

functionName ::=

name

indexName ::=

name

parameterName ::=

name

procedureName ::=

name

sequenceName ::=

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


previous : next

References to this page

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