Red Hat Summit Red Hat SummitSupportConsoleDevelopersStart a trialContact

A.7. Productions

string ::=

  • Expand
    < string literal >
A string literal value. Use '' to escape ' in the string.
Example: 
'a string'
Copy to Clipboard Toggle word wrap 
'it''s a string'
Copy to Clipboard Toggle word wrap

reserved identifier ::=

  • Expand
    INSTEAD
  • Expand
    VIEW
  • Expand
    ENABLED
  • Expand
    DISABLED
  • Expand
    KEY
  • Expand
    SERIAL
  • Expand
    TEXTAGG
  • Expand
    COUNT
  • Expand
    ROW_NUMBER
  • Expand
    RANK
  • Expand
    DENSE_RANK
  • Expand
    SUM
  • Expand
    AVG
  • Expand
    MIN
  • Expand
    MAX
  • Expand
    EVERY
  • Expand
    STDDEV_POP
  • Expand
    STDDEV_SAMP
  • Expand
    VAR_SAMP
  • Expand
    VAR_POP
  • Expand
    DOCUMENT
  • Expand
    CONTENT
  • Expand
    TRIM
  • Expand
    EMPTY
  • Expand
    ORDINALITY
  • Expand
    PATH
  • Expand
    FIRST
  • Expand
    LAST
  • Expand
    NEXT
  • Expand
    SUBSTRING
  • Expand
    EXTRACT
  • Expand
    TO_CHARS
  • Expand
    TO_BYTES
  • Expand
    TIMESTAMPADD
  • Expand
    TIMESTAMPDIFF
  • Expand
    QUERYSTRING
  • Expand
    NAMESPACE
  • Expand
    RESULT
  • Expand
    INDEX
  • Expand
    ACCESSPATTERN
  • Expand
    AUTO_INCREMENT
  • Expand
    WELLFORMED
  • Expand
    SQL_TSI_FRAC_SECOND
  • Expand
    SQL_TSI_SECOND
  • Expand
    SQL_TSI_MINUTE
  • Expand
    SQL_TSI_HOUR
  • Expand
    SQL_TSI_DAY
  • Expand
    SQL_TSI_WEEK
  • Expand
    SQL_TSI_MONTH
  • Expand
    SQL_TSI_QUARTER
  • Expand
    SQL_TSI_YEAR
  • Expand
    TEXTTABLE
  • Expand
    ARRAYTABLE
  • Expand
    SELECTOR
  • Expand
    SKIP
  • Expand
    WIDTH
  • Expand
    PASSING
  • Expand
    NAME
  • Expand
    ENCODING
  • Expand
    COLUMNS
  • Expand
    DELIMITER
  • Expand
    QUOTE
  • Expand
    HEADER
  • Expand
    NULLS
  • Expand
    OBJECTTABLE
  • Expand
    VERSION
  • Expand
    INCLUDING
  • Expand
    EXCLUDING
  • Expand
    XMLDECLARATION
  • Expand
    VARIADIC
  • Expand
    RAISE
  • Expand
    EXCEPTION
  • Expand
    CHAIN
  • Expand
    JSONARRAY_AGG
  • Expand
    JSONOBJECT
Allows non-reserved keywords to be parsed as identifiers
Example: SELECT COUNT FROM ...

identifier ::=

  • Expand
    < identifier >
  • Expand
    < non-reserved identifier >
Partial or full name of a single entity.
Example: 
tbl.col
Copy to Clipboard Toggle word wrap 
"tbl"."col"
Copy to Clipboard Toggle word wrap

create trigger ::=

  • Expand
    CREATE TRIGGER ON < identifier > INSTEAD OF ( INSERT | UPDATE | DELETE ) AS < for each row trigger action >
Creates a trigger action on the given target.
Example: 
CREATE TRIGGER ON vw INSTEAD OF INSERT AS FOR EACH ROW BEGIN ATOMIC ... END
Copy to Clipboard Toggle word wrap

alter ::=

  • Expand
    ALTER ( ( VIEW < identifier > AS < query expression > ) | ( PROCEDURE < identifier > AS < statement > ) | ( TRIGGER ON < identifier > INSTEAD OF ( INSERT | UPDATE | DELETE ) ( ( AS < for each row trigger action > ) | ENABLED | DISABLED ) ) )
Alter the given target.
Example: 
ALTER VIEW vw AS SELECT col FROM tbl
Copy to Clipboard Toggle word wrap

for each row trigger action ::=

  • Expand
    FOR EACH ROW ( ( BEGIN ( ATOMIC )? ( < statement > )* END ) | < statement > )
Defines an action to perform on each row.
Example: 
FOR EACH ROW BEGIN ATOMIC ... END
Copy to Clipboard Toggle word wrap

directly executable statement ::=

  • Expand
    < query expression >
  • Expand
    < call statement >
  • Expand
    < insert statement >
  • Expand
    < update statement >
  • Expand
    < delete statement >
  • Expand
    < drop table >
  • Expand
    < create temporary table >
  • Expand
    < create foreign temp table >
  • Expand
    < alter >
  • Expand
    < create trigger >
A statement that can be executed at runtime.
Example: 
SELECT * FROM tbl
Copy to Clipboard Toggle word wrap

drop table ::=

  • Expand
    DROP TABLE < identifier >
Creates a trigger action on the given target.
Example: 
CREATE TRIGGER ON vw INSTEAD OF INSERT AS FOR EACH ROW BEGIN ATOMIC ... END
Copy to Clipboard Toggle word wrap

create temporary table ::=

  • Expand
    CREATE LOCAL TEMPORARY TABLE < identifier > < lparen > < temporary table element > ( < comma > < temporary table element > )* ( < comma > PRIMARY KEY < column list > )? < rparen >
Creates a temporary table.
Example: 
CREATE LOCAL TEMPORARY TABLE tmp (col integer)
Copy to Clipboard Toggle word wrap

temporary table element ::=

  • Expand
    < identifier > ( < data type > | SERIAL ) ( NOT NULL )?
Defines a temporary table column.
Example: 
col string NOT NULL
Copy to Clipboard Toggle word wrap

raise error statement ::=

  • Expand
    ERROR < expression >
Raises an error with the given message.
Example: 
ERROR 'something went wrong'
Copy to Clipboard Toggle word wrap

raise statement ::=

  • Expand
    RAISE ( SQLWARNING )? < exception reference >
Raises an error or warning with the given message.
Example: 
RAISE SQLEXCEPTION 'something went wrong'
Copy to Clipboard Toggle word wrap

exception reference ::=

  • Expand
    < identifier >
  • Expand
    < sql exception >
a reference to an exception
Example: 
SQLEXCEPTION 'something went wrong' SQLSTATE '00X', 2
Copy to Clipboard Toggle word wrap

sql exception ::=

  • Expand
    SQLEXCEPTION < common value expression > ( SQLSTATE < common value expression > ( < comma > < common value expression > )? )? ( CHAIN < exception reference > )?
creates a sql exception or warning with the specified message, state, and code
Example: 
SQLEXCEPTION 'something went wrong' SQLSTATE '00X', 2
Copy to Clipboard Toggle word wrap

statement ::=

  • Expand
    ( ( < identifier > < colon > )? ( < loop statement > | < while statement > | < compound statement > ) )
  • Expand
    < if statement > | < delimited statement >
A procedure statement.
Example: 
IF (x = 5) BEGIN ... END
Copy to Clipboard Toggle word wrap

delimited statement ::=

  • Expand
    ( < assignment statement > | < data statement > | < raise error statement > | < raise statement > | < declare statement > | < branching statement > | < return statement > ) < semicolon >
A procedure statement terminated by ;.
Example: 
SELECT * FROM tbl;
Copy to Clipboard Toggle word wrap

compound statement ::=

  • Expand
    BEGIN ( ( NOT )? ATOMIC )? ( < statement > )* ( EXCEPTION < identifier > ( < statement > )* )? END
A procedure statement block contained in BEGIN END.
Example: 
BEGIN NOT ATOMIC ... END
Copy to Clipboard Toggle word wrap

branching statement ::=

  • Expand
    ( ( BREAK | CONTINUE ) ( < identifier > )? )
  • Expand
    ( LEAVE < identifier > )
A procedure branching control statement, which typically specifies a label to return control to.
Example: 
BREAK x
Copy to Clipboard Toggle word wrap

return statement ::=

  • Expand
    RETURN ( < expression > )?
A return statement.
Example: 
RETURN 1
Copy to Clipboard Toggle word wrap

while statement ::=

  • Expand
    WHILE < lparen > < condition > < rparen > < statement >
A procedure while statement that executes until its condition is false.
Example: 
WHILE (var) BEGIN ... END
Copy to Clipboard Toggle word wrap

loop statement ::=

  • Expand
    LOOP ON < lparen > < query expression > < rparen > AS < identifier > < statement >
A procedure loop statement that executes over the given cursor.
Example: 
IF (boolVal) BEGIN variables.x = 1 END ELSE BEGIN variables.x = 2 END
Copy to Clipboard Toggle word wrap

if statement ::=

  • Expand
    IF < lparen > < condition > < rparen > < statement > ( ELSE < statement > )?
A procedure loop statement that executes over the given cursor.
Example: 
LOOP ON (SELECT * FROM tbl) AS x BEGIN ... END
Copy to Clipboard Toggle word wrap

declare statement ::=

  • Expand
    DECLARE ( < data type > | EXCEPTION ) < identifier > ( < eq > < assignment statement operand > )?
A procedure declaration statement that creates a variable and optionally assigns a value.
Example: 
DECLARE STRING x = 'a'
Copy to Clipboard Toggle word wrap

assignment statement ::=

  • Expand
    < identifier > < eq > ( < assignment statement operand > | ( < call statement > ( ( WITH | WITHOUT ) RETURN )? ) )
Assigns a variable a value in a procedure.
Example: 
x = 'b'
Copy to Clipboard Toggle word wrap

assignment statement operand ::=

  • Expand
    < insert statement >
  • Expand
    < update statement >
  • Expand
    < delete statement >
  • Expand
    < expression >
  • Expand
    < query expression >
  • Expand
    < sql exception >
A value or command that can be used in an assignment.

Note

All assignments except for expression are deprecated.

data statement ::=

  • Expand
    ( < directly executable statement > | < dynamic data statement > ) ( ( WITH | WITHOUT ) RETURN )?
A procedure statement that executes a SQL statement. An update statement can have its update count accessed via the ROWCOUNT variable.

procedure body definition ::=

  • Expand
    ( CREATE ( VIRTUAL )? PROCEDURE )? < statement >
Defines a procedure body on a Procedure metadata object.
Example: 
CREATE VIRTUAL PROCEDURE BEGIN ... END
Copy to Clipboard Toggle word wrap

dynamic data statement ::=

  • Expand
    ( EXECUTE | EXEC ) ( STRING | IMMEDIATE )? < expression > ( AS < typed element list > ( INTO < identifier > )? )? ( USING < set clause list > )? ( UPDATE ( < unsigned integer > | < star > ) )?
A procedure statement that can execute arbitrary sql.
Example: 
EXECUTE IMMEDIATE 'SELECT * FROM tbl' AS x STRING INTO #temp
Copy to Clipboard Toggle word wrap

set clause list ::=

  • Expand
    < identifier > < eq > < expression > ( < comma > < identifier > < eq > < expression > )*
A list of value assignments.
Example: 
col1 = 'x', col2 = 'y' ...
Copy to Clipboard Toggle word wrap

typed element list ::=

  • Expand
    < identifier > < data type > ( < comma > < identifier > < data type > )*
A list of typed elements.
Example: 
col1 string, col2 integer ...
Copy to Clipboard Toggle word wrap

callable statement ::=

  • Expand
    < lbrace > ( < qmark > < eq > )? CALL < identifier > ( < lparen > ( < expression list > )? < rparen > )? < rbrace > ( < option clause > )?
A callable statement defined using JDBC escape syntax.
Example: 
{? = CALL proc}
Copy to Clipboard Toggle word wrap

call statement ::=

  • Expand
    ( ( EXEC | EXECUTE | CALL ) < identifier > < lparen > ( < named parameter list > | ( < expression list > )? ) < rparen > ) ( < option clause > )?
Executes the procedure with the given parameters.
Example: 
CALL proc('a', 1)
Copy to Clipboard Toggle word wrap

named parameter list ::=

  • Expand
    ( < identifier > < eq > ( < gt > )? < expression > ( < comma > < identifier > < eq > ( < gt > )? < expression > )* )
A list of named parameters.
Example: 
param1 => 'x', param2 => 1
Copy to Clipboard Toggle word wrap

insert statement ::=

  • Expand
    ( INSERT | MERGE ) INTO < identifier > ( < column list > )? ( ( VALUES < lparen > < expression list > < rparen > ) | < query expression > ) ( < option clause > )?
Inserts values into the given target.
Example: 
INSERT INTO tbl (col1, col2) VALUES ('a', 1)
Copy to Clipboard Toggle word wrap

expression list ::=

  • Expand
    < expression > ( < comma > < expression > )*
A list of expressions.
Example: 
col1, 'a', ...
Copy to Clipboard Toggle word wrap

update statement ::=

  • Expand
    UPDATE < identifier > SET < set clause list > ( < where clause > )? ( < option clause > )?
Update values in the given target.
Example: 
UPDATE tbl SET (col1 = 'a') WHERE col2 = 1
Copy to Clipboard Toggle word wrap

delete statement ::=

  • Expand
    DELETE FROM < identifier > ( < where clause > )? ( < option clause > )?
Delete rows from the given target.
Example: 
DELETE FROM tbl WHERE col2 = 1
Copy to Clipboard Toggle word wrap

query expression ::=

  • Expand
    ( WITH < with list element > ( < comma > < with list element > )* )? < query expression body >
A declarative query for data.
Example: 
SELECT * FROM tbl WHERE col2 = 1
Copy to Clipboard Toggle word wrap

with list element ::=

  • Expand
    < identifier > ( < column list > )? AS < lparen > < query expression > < rparen >
A query expression for use in the enclosing query.
Example: 
X (Y, Z) AS (SELECT 1, 2)
Copy to Clipboard Toggle word wrap

query expression body ::=

  • Expand
    < query term > ( ( UNION | EXCEPT ) ( ALL | DISTINCT )? < query term > )* ( < order by clause > )? ( < limit clause > )? ( < option clause > )?
The body of a query expression, which can optionally be ordered and limited.
Example: 
SELECT * FROM tbl ORDER BY col1 LIMIT 1
Copy to Clipboard Toggle word wrap

query term ::=

  • Expand
    < query primary > ( INTERSECT ( ALL | DISTINCT )? < query primary > )*
Used to establish INTERSECT precedence.
Example: 
SELECT * FROM tbl
Copy to Clipboard Toggle word wrap 
SELECT * FROM tbl1 INTERSECT SELECT * FROM tbl2
Copy to Clipboard Toggle word wrap

query primary ::=

  • Expand
    < query >
  • Expand
    ( TABLE < identifier > )
  • Expand
    ( < lparen > < query expression body > < rparen > )
A declarative source of rows.
Example: 
TABLE tbl
Copy to Clipboard Toggle word wrap 
SELECT * FROM tbl1
Copy to Clipboard Toggle word wrap

query ::=

  • Expand
    < select clause > ( < into clause > )? ( < from clause > ( < where clause > )? ( < group by clause > )? ( < having clause > )? )?
A SELECT query.
Example: 
SELECT col1, max(col2) FROM tbl GROUP BY col1
Copy to Clipboard Toggle word wrap

into clause ::=

  • Expand
    INTO < identifier >
Used to direct the query into a table.

Note

This is deprecated. Use INSERT INTO with a query expression instead.
Example: 
INTO tbl
Copy to Clipboard Toggle word wrap

select clause ::=

  • Expand
    SELECT ( ALL | DISTINCT )? ( < star > | ( < select sublist > ( < comma > < select sublist > )* ) )
The columns returned by a query. Can optionally be distinct.
Example: 
SELECT *
Copy to Clipboard Toggle word wrap 
SELECT DISTINCT a, b, c
Copy to Clipboard Toggle word wrap

select sublist ::=

  • Expand
    < select derived column >
  • Expand
    < all in group >
An element in the select clause
Example: 
tbl.*
Copy to Clipboard Toggle word wrap 
tbl.col AS x
Copy to Clipboard Toggle word wrap

select derived column ::=

  • Expand
    ( < expression > ( ( AS )? < identifier > )? )
A select clause item that selects a single column.

Note

This is slightly different than a derived column in that the AS keyword is optional.
Example: 
tbl.col AS x
Copy to Clipboard Toggle word wrap

derived column ::=

  • Expand
    ( < expression > ( AS < identifier > )? )
An optionally named expression.
Example: 
tbl.col AS x
Copy to Clipboard Toggle word wrap

all in group ::=

  • Expand
    < all in group identifier >
A select sublist that can select all columns from the given group.
Example: 
tbl.*
Copy to Clipboard Toggle word wrap

ordered aggreate function ::=

  • Expand
    ( XMLAGG | ARRAY_AGG | JSONARRAY_AGG ) < lparen > < expression > ( < order by clause > )? < rparen >
An aggregate function that can optionally be ordered.
Example: 
XMLAGG(col1) ORDER BY col2
Copy to Clipboard Toggle word wrap 
ARRAY_AGG(col1)
Copy to Clipboard Toggle word wrap

text aggreate function ::=

  • Expand
    TEXTAGG < lparen > ( FOR )? < derived column > ( < comma > < derived column > )* ( DELIMITER < character > )? ( QUOTE < character > )? ( HEADER )? ( ENCODING < identifier > )? ( < order by clause > )? < rparen >
An aggregate function for creating separated value clobs.
Example: 
TEXTAGG (col1 as t1, col2 as t2 DELIMITER ',' HEADER)
Copy to Clipboard Toggle word wrap

standard aggregate function ::=

  • Expand
    ( COUNT < lparen > < star > < rparen > )
  • Expand
    ( ( COUNT | SUM | AVG | MIN | MAX | EVERY | STDDEV_POP | STDDEV_SAMP | VAR_SAMP | VAR_POP | SOME | ANY ) < lparen > ( DISTINCT | ALL )? < expression > < rparen > )
A standard aggregate function.
Example: 
COUNT(*)
Copy to Clipboard Toggle word wrap

analytic aggregate function ::=

  • Expand
    ( ROW_NUMBER | RANK | DENSE_RANK ) < lparen > < rparen >
An analytic aggregate function.
Example: 
ROW_NUMBER()
Copy to Clipboard Toggle word wrap

filter clause ::=

  • Expand
    FILTER < lparen > WHERE < boolean primary > < rparen >
An aggregate filter clause applied prior to accumulating the value.
Example: 
FILTER (WHERE col1='a')
Copy to Clipboard Toggle word wrap

from clause ::=

  • Expand
    FROM ( < table reference > ( < comma > < table reference > )* )
A query from clause containing a list of table references.
Example: 
FROM a, b
Copy to Clipboard Toggle word wrap 
FROM a right outer join b, c, d join e".</p>
Copy to Clipboard Toggle word wrap

table reference ::=

  • Expand
    ( < escaped join > < joined table > < rbrace > )
  • Expand
    < joined table >
An optionally escaped joined table.
Example: 
a
Copy to Clipboard Toggle word wrap 
a inner join b
Copy to Clipboard Toggle word wrap

joined table ::=

  • Expand
    < table primary > ( < cross join > | < qualified table > )*
A table or join.
Example: 
a
Copy to Clipboard Toggle word wrap 
a inner join b
Copy to Clipboard Toggle word wrap

cross join ::=

  • Expand
    ( ( CROSS | UNION ) JOIN < table primary > )
A cross join.
Example: 
a CROSS JOIN b
Copy to Clipboard Toggle word wrap

qualified table ::=

  • Expand
    ( ( ( RIGHT ( OUTER )? ) | ( LEFT ( OUTER )? ) | ( FULL ( OUTER )? ) | INNER )? JOIN < table reference > ON < condition > )
An INNER or OUTER join.
Example: 
a inner join b
Copy to Clipboard Toggle word wrap

table primary ::=

  • Expand
    ( < text table > | < array table > | < xml table > | < object table > | < table name > | < table subquery > | ( < lparen > < joined table > < rparen > ) ) ( MAKEDEP | MAKENOTDEP )?
A single source of rows.
Example: 
a
Copy to Clipboard Toggle word wrap

xml serialize ::=

  • Expand
    XMLSERIALIZE < lparen > ( DOCUMENT | CONTENT )? < expression > ( AS ( STRING | VARCHAR | CLOB | VARBINARY | BLOB ) )? ( ENCODING < identifier > )? ( VERSION < string > )? ( ( INCLUDING | EXCLUDING ) XMLDECLARATION )? < rparen >
Serializes an XML value.
Example: 
XMLSERIALIZE(col1 AS CLOB)
Copy to Clipboard Toggle word wrap

array table ::=

  • Expand
    ARRAYTABLE < lparen > < value expression primary > COLUMNS < typed element list > < rparen > ( AS )? < identifier >
The ARRAYTABLE table function creates tabular results from arrays. It can be used as a nested table reference.
Example: 
ARRAYTABLE (col1 COLUMNS x STRING) AS y
Copy to Clipboard Toggle word wrap

text table ::=

  • Expand
    TEXTTABLE < lparen > < common value expression > ( SELECTOR < string > )? COLUMNS < text table column > ( < comma > < text table column > )* ( NO ROW DELIMITER )? ( DELIMITER < character > )? ( ( ESCAPE < character > ) | ( QUOTE < character > ) )? ( HEADER ( < unsigned integer > )? )? ( SKIP < unsigned integer > )? < rparen > ( AS )? < identifier >
The TEXTTABLE table function creates tabular results from text. It can be used as a nested table reference.
Example: 
TEXTTABLE (file COLUMNS x STRING) AS y
Copy to Clipboard Toggle word wrap

text table column ::=

  • Expand
    < identifier > < data type > ( WIDTH < unsigned integer > ( NO TRIM )? )? ( SELECTOR < string > < unsigned integer > )?
A text table column.
Example: 
x INTEGER WIDTH 6
Copy to Clipboard Toggle word wrap

xml query ::=

  • Expand
    XMLQUERY < lparen > ( < xml namespaces > < comma > )? < string > ( PASSING < derived column > ( < comma > < derived column > )* )? ( ( NULL | EMPTY ) ON EMPTY )? < rparen >
Executes an XQuery to return an XML result.
Example: 
XMLQUERY('<a>...</a>' PASSING doc)
Copy to Clipboard Toggle word wrap

object table ::=

  • Expand
    OBJECTTABLE < lparen > ( LANGUAGE < string > )? < string > ( PASSING < derived column > ( < comma > < derived column > )* )? COLUMNS < object table column > ( < comma > < object table column > )* < rparen > ( AS )? < identifier >
Returns table results by processing a script.
Example: 
OBJECTTABLE('z' PASSING val AS z COLUMNS col OBJECT 'teiid_row') AS X
Copy to Clipboard Toggle word wrap

object table column ::=

  • Expand
    < identifier > < data type > < string > ( DEFAULT < expression > )?
object table column.
Example: 
y integer 'teiid_row_number'
Copy to Clipboard Toggle word wrap

xml table ::=

  • Expand
    XMLTABLE < lparen > ( < xml namespaces > < comma > )? < string > ( PASSING < derived column > ( < comma > < derived column > )* )? ( COLUMNS < xml table column > ( < comma > < xml table column > )* )? < rparen > ( AS )? < identifier >
Returns table results by processing an XQuery.
Example: 
XMLTABLE('/a/b' PASSING doc COLUMNS col XML PATH '.') AS X
Copy to Clipboard Toggle word wrap

xml table column ::=

  • Expand
    < identifier > ( ( FOR ORDINALITY ) | ( < data type > ( DEFAULT < expression > )? ( PATH < string > )? ) )
XML table column.
Example: 
y FOR ORDINALITY
Copy to Clipboard Toggle word wrap

unsigned integer ::=

  • Expand
    < unsigned integer literal >
An unsigned interger value.
Example: 
12345
Copy to Clipboard Toggle word wrap

table subquery ::=

  • Expand
    ( TABLE | LATERAL )? < lparen > ( < query expression > | < call statement > ) < rparen > ( AS )? < identifier >
A table defined by a subquery.
Example: 
(SELECT * FROM tbl) AS x
Copy to Clipboard Toggle word wrap

table name ::=

  • Expand
    ( < identifier > ( ( AS )? < identifier > )? )
A table named in the FROM clause.
Example: 
tbl AS x
Copy to Clipboard Toggle word wrap

where clause ::=

  • Expand
    WHERE < condition >
Specifies a search condition
Example: 
WHERE x = 'a'
Copy to Clipboard Toggle word wrap

condition ::=

  • Expand
    < boolean value expression >
A boolean expression.

boolean value expression ::=

  • Expand
    < boolean term > ( OR < boolean term > )*
An optionally ORed boolean expression.

boolean term ::=

  • Expand
    < boolean factor > ( AND < boolean factor > )*
An optional ANDed boolean factor.

boolean factor ::=

  • Expand
    ( NOT )? < boolean primary >
A boolean factor.
Example: 
NOT x = 'a'
Copy to Clipboard Toggle word wrap

boolean primary ::=

  • Expand
    ( < common value expression > ( < between predicate > | < match predicate > | < like regex predicate > | < in predicate > | < is null predicate > | < quantified comparison predicate > | < comparison predicate > )? )
  • Expand
    < exists predicate >
A boolean predicate or simple expression.
Example: 
col LIKE 'a%'
Copy to Clipboard Toggle word wrap

comparison operator ::=

  • Expand
    < eq >
  • Expand
    < ne >
  • Expand
    < ne2 >
  • Expand
    < lt >
  • Expand
    < le >
  • Expand
    < gt >
  • Expand
    < ge >
A comparison operator.
Example: 
=
Copy to Clipboard Toggle word wrap

comparison predicate ::=

  • Expand
    < comparison operator > < common value expression >
A value comparison.
Example: 
= 'a'
Copy to Clipboard Toggle word wrap

subquery ::=

  • Expand
    < lparen > ( < query expression > | < call statement > ) < rparen >
A subquery.
Example: 
(SELECT * FROM tbl)
Copy to Clipboard Toggle word wrap

quantified comparison predicate ::=

  • Expand
    < comparison operator > ( ANY | SOME | ALL ) < subquery >
A subquery comparison.
Example: 
= ANY (SELECT col FROM tbl)
Copy to Clipboard Toggle word wrap

match predicate ::=

  • Expand
    ( NOT )? ( LIKE | ( SIMILAR TO ) ) < common value expression > ( ESCAPE < character > | ( < lbrace > ESCAPE < character > < rbrace > ) )?
Matches based upon a pattern.
Example: 
LIKE 'a_'
Copy to Clipboard Toggle word wrap

like regex predicate ::=

  • Expand
    ( NOT )? LIKE_REGEX < common value expression >
A regular expression match.
Example: 
LIKE_REGEX 'a.*b'
Copy to Clipboard Toggle word wrap

character ::=

  • Expand
    < string >
A single character.
Example: 
'a'
Copy to Clipboard Toggle word wrap

between predicate ::=

  • Expand
    ( NOT )? BETWEEN < common value expression > AND < common value expression >
A comparison between two values.
Example: 
BETWEEN 1 AND 5
Copy to Clipboard Toggle word wrap

is null predicate ::=

  • Expand
    IS ( NOT )? NULL
A null test.
Example: 
IS NOT NULL
Copy to Clipboard Toggle word wrap

in predicate ::=

  • Expand
    ( NOT )? IN ( < subquery > | ( < lparen > < common value expression > ( < comma > < common value expression > )* < rparen > ) )
A comparison with multiple values.
Example: 
IN (1, 5)
Copy to Clipboard Toggle word wrap

exists predicate ::=

  • Expand
    EXISTS < subquery >
A test if rows exist.
Example: 
EXISTS (SELECT col FROM tbl)
Copy to Clipboard Toggle word wrap

group by clause ::=

  • Expand
    GROUP BY < expression list >
Defines the grouping columns
Example: 
GROUP BY col1, col2
Copy to Clipboard Toggle word wrap

having clause ::=

  • Expand
    HAVING < condition >
Search condition applied after grouping.
Example: 
HAVING max(col1) = 5
Copy to Clipboard Toggle word wrap

order by clause ::=

  • Expand
    ORDER BY < sort specification > ( < comma > < sort specification > )*
Specifies row ordering.
Example: 
ORDER BY x, y DESC
Copy to Clipboard Toggle word wrap

sort specification ::=

  • Expand
    < sort key > ( ASC | DESC )? ( NULLS ( FIRST | LAST ) )?
Defines how to sort on a particular expression
Example: 
col1 NULLS FIRST
Copy to Clipboard Toggle word wrap

sort key ::=

  • Expand
    < expression >
A sort expression.
Example: 
col1
Copy to Clipboard Toggle word wrap

integer parameter ::=

  • Expand
    < unsigned integer >
  • Expand
    < qmark >
A literal integer or parameter reference to an integer.
Example: 
?
Copy to Clipboard Toggle word wrap

limit clause ::=

  • Expand
    ( LIMIT < integer parameter > ( < comma > < integer parameter > )? )
  • Expand
    ( OFFSET < integer parameter > ( ROW | ROWS ) ( < fetch clause > )? )
  • Expand
    < fetch clause >
Limits and/or offsets the resultant rows.
Example: 
LIMIT 2
Copy to Clipboard Toggle word wrap

fetch clause ::=

  • Expand
    FETCH ( FIRST | NEXT ) ( < integer parameter > )? ( ROW | ROWS ) ONLY
ANSI limit.
Example: 
FETCH FIRST 1 ROWS ONLY
Copy to Clipboard Toggle word wrap

option clause ::=

  • Expand
    OPTION ( MAKEDEP < identifier > ( < comma > < identifier > )* | MAKENOTDEP < identifier > ( < comma > < identifier > )* | NOCACHE ( < identifier > ( < comma > < identifier > )* )? )*
Specifies query options.
Example: 
OPTION MAKEDEP tbl
Copy to Clipboard Toggle word wrap

expression ::=

  • Expand
    < condition >
A value.
Example: 
col1
Copy to Clipboard Toggle word wrap

common value expression ::=

  • Expand
    ( < numeric value expression > ( < concat_op > < numeric value expression > )* )
Establishes the precedence of concat.
Example: 
'a' || 'b'
Copy to Clipboard Toggle word wrap

numeric value expression ::=

  • Expand
    ( < term > ( < plus or minus > < term > )* )
Example: 
1 + 2
Copy to Clipboard Toggle word wrap

plus or minus ::=

  • Expand
    < plus >
  • Expand
    < minus >
The + or - operator.
Example: 
+
Copy to Clipboard Toggle word wrap

term ::=

  • Expand
    ( < value expression primary > ( < star or slash > < value expression primary > )* )
A numeric term
Example: 
1 * 2
Copy to Clipboard Toggle word wrap

star or slash ::=

  • Expand
    < star >
  • Expand
    < slash >
The * or / operator.
Example: 
/
Copy to Clipboard Toggle word wrap

value expression primary ::=

  • Expand
    < non numeric literal >
  • Expand
    ( < plus or minus > )? ( < unsigned numeric literal > | < unsigned value expression primary > )
A simple value expression.
Example: 
+col1
Copy to Clipboard Toggle word wrap

unsigned value expression primary ::=

  • Expand
    < qmark >
  • Expand
    ( < dollar > < unsigned integer > )
  • Expand
    ( < escaped function > < function > < rbrace > )
  • Expand
    ( ( < text aggreate function > | < standard aggregate function > | < ordered aggreate function > ) ( < filter clause > )? ( < window specification > )? )
  • Expand
    ( < analytic aggregate function > ( < filter clause > )? < window specification > )
  • Expand
    ( < function > ( < window specification > )? )
  • Expand
    ( ( < identifier > | < non-reserved identifier > ) ( < lsbrace > < common value expression > < rsbrace > )? )
  • Expand
    < subquery >
  • Expand
    ( < lparen > < expression > < rparen > ( < lsbrace > < common value expression > < rsbrace > )? )
  • Expand
    < searched case expression >
  • Expand
    < case expression >
An unsigned simple value expression.
Example: 
col1
Copy to Clipboard Toggle word wrap

window specification ::=

  • Expand
    OVER < lparen > ( PARTITION BY < expression list > )? ( < order by clause > )? < rparen >
The window specification for an analytical or windowed aggregate function.
Example: 
OVER (PARTION BY col1)
Copy to Clipboard Toggle word wrap

case expression ::=

  • Expand
    CASE < expression > ( WHEN < expression > THEN < expression > )+ ( ELSE < expression > )? END
If/then/else chain using a common search predicand.
Example: 
CASE col1 WHEN 'a' THEN 1 ELSE 2
Copy to Clipboard Toggle word wrap

searched case expression ::=

  • Expand
    CASE ( WHEN < condition > THEN < expression > )+ ( ELSE < expression > )? END
If/then/else chain using multiple search conditions.
Example: 
CASE WHEN x = 'a' THEN 1 WHEN y = 'b' THEN 2
Copy to Clipboard Toggle word wrap

function ::=

  • Expand
    ( CONVERT < lparen > < expression > < comma > < data type > < rparen > )
  • Expand
    ( CAST < lparen > < expression > AS < data type > < rparen > )
  • Expand
    ( SUBSTRING < lparen > < expression > ( ( FROM < expression > ( FOR < expression > )? ) | ( < comma > < expression list > ) ) < rparen > )
  • Expand
    ( EXTRACT < lparen > ( YEAR | MONTH | DAY | HOUR | MINUTE | SECOND ) FROM < expression > < rparen > )
  • Expand
    ( TRIM < lparen > ( ( ( ( LEADING | TRAILING | BOTH ) ( < expression > )? ) | < expression > ) FROM )? < expression > < rparen > )
  • Expand
    ( ( TO_CHARS | TO_BYTES ) < lparen > < expression > < comma > < string > < rparen > )
  • Expand
    ( ( TIMESTAMPADD | TIMESTAMPDIFF ) < lparen > < time interval > < comma > < expression > < comma > < expression > < rparen > )
  • Expand
    < querystring function >
  • Expand
    ( ( LEFT | RIGHT | CHAR | USER | YEAR | MONTH | HOUR | MINUTE | SECOND | XMLCONCAT | XMLCOMMENT ) < lparen > ( < expression list > )? < rparen > )
  • Expand
    ( ( TRANSLATE | INSERT ) < lparen > ( < expression list > )? < rparen > )
  • Expand
    < xml parse >
  • Expand
    < xml element >
  • Expand
    ( XMLPI < lparen > ( ( NAME )? < identifier > ) ( < comma > < expression > )? < rparen > )
  • Expand
    < xml forest >
  • Expand
    < json object >
  • Expand
    < xml serialize >
  • Expand
    < xml query >
  • Expand
    ( < identifier > < lparen > ( ALL | DISTINCT )? ( < expression list > )? ( < order by clause > )? < rparen > ( < filter clause > )? )
Calls a scalar function.
Example: 
func('1', col1)
Copy to Clipboard Toggle word wrap

xml parse ::=

  • Expand
    XMLPARSE < lparen > ( DOCUMENT | CONTENT ) < expression > ( WELLFORMED )? < rparen >
Parses the given value as XML.
Example: 
XMLPARSE(DOCUMENT doc WELLFORMED)
Copy to Clipboard Toggle word wrap

querystring function ::=

  • Expand
    QUERYSTRING < lparen > < expression > ( < comma > < derived column > )* < rparen >
Produces a URL query string from the given arguments.
Example: 
QUERYSTRING(col1 AS opt, col2 AS val)
Copy to Clipboard Toggle word wrap

xml element ::=

  • Expand
    XMLELEMENT < lparen > ( ( NAME )? < identifier > ) ( < comma > < xml namespaces > )? ( < comma > < xml attributes > )? ( < comma > < expression > )* < rparen >
Creates an XML element.
Example: 
XMLELEMENT(NAME "root", child)
Copy to Clipboard Toggle word wrap

xml attributes ::=

  • Expand
    XMLATTRIBUTES < lparen > < derived column > ( < comma > < derived column > )* < rparen >
Creates attributes for the containing element.
Example: 
XMLATTRIBUTES(col1 AS attr1, col2 AS attr2)
Copy to Clipboard Toggle word wrap

json object ::=

  • Expand
    JSONOBJECT < lparen > < derived column list > < rparen >
Produces a JSON object containing name value pairs.
Example: 
JSONOBJECT(col1 AS val1, col2 AS val2)
Copy to Clipboard Toggle word wrap

derived column list ::=

  • Expand
    < derived column > ( < comma > < derived column > )*
a list of name value pairs
Example: 
col1 AS val1, col2 AS val2
Copy to Clipboard Toggle word wrap

xml forest ::=

  • Expand
    XMLFOREST < lparen > ( < xml namespaces > < comma > )? < derived column list > < rparen >
Produces an element for each derived column.
Example: 
XMLFOREST(col1 AS ELEM1, col2 AS ELEM2)
Copy to Clipboard Toggle word wrap

xml namespaces ::=

  • Expand
    XMLNAMESPACES < lparen > < xml namespace element > ( < comma > < xml namespace element > )* < rparen >
Defines XML namespace URI/prefix combinations
Example: 
XMLNAMESPACES('http://foo' AS foo)
Copy to Clipboard Toggle word wrap

xml namespace element ::=

  • Expand
    ( < string > AS < identifier > )
  • Expand
    ( NO DEFAULT )
  • Expand
    ( DEFAULT < string > )
An xml namespace
Example: 
NO DEFAULT
Copy to Clipboard Toggle word wrap

data type ::=

  • Expand
    ( STRING ( < lparen > < unsigned integer > < rparen > )? )
  • Expand
    ( VARCHAR ( < lparen > < unsigned integer > < rparen > )? )
  • Expand
    BOOLEAN
  • Expand
    BYTE
  • Expand
    TINYINT
  • Expand
    SHORT
  • Expand
    SMALLINT
  • Expand
    ( CHAR ( < lparen > < unsigned integer > < rparen > )? )
  • Expand
    INTEGER
  • Expand
    LONG
  • Expand
    BIGINT
  • Expand
    ( BIGINTEGER ( < lparen > < unsigned integer > < rparen > )? )
  • Expand
    FLOAT
  • Expand
    REAL
  • Expand
    DOUBLE
  • Expand
    ( BIGDECIMAL ( < lparen > < unsigned integer > ( < comma > < unsigned integer > )? < rparen > )? )
  • Expand
    ( DECIMAL ( < lparen > < unsigned integer > ( < comma > < unsigned integer > )? < rparen > )? )
  • Expand
    DATE
  • Expand
    TIME
  • Expand
    TIMESTAMP
  • Expand
    OBJECT
  • Expand
    ( BLOB ( < lparen > < unsigned integer > < rparen > )? )
  • Expand
    ( CLOB ( < lparen > < unsigned integer > < rparen > )? )
  • Expand
    ( VARBINARY ( < lparen > < unsigned integer > < rparen > )? )
  • Expand
    XML
A data type.
Example: 
STRING
Copy to Clipboard Toggle word wrap

time interval ::=

  • Expand
    SQL_TSI_FRAC_SECOND
  • Expand
    SQL_TSI_SECOND
  • Expand
    SQL_TSI_MINUTE
  • Expand
    SQL_TSI_HOUR
  • Expand
    SQL_TSI_DAY
  • Expand
    SQL_TSI_WEEK
  • Expand
    SQL_TSI_MONTH
  • Expand
    SQL_TSI_QUARTER
  • Expand
    SQL_TSI_YEAR
A time interval keyword.
Example: 
SQL_TSI_HOUR
Copy to Clipboard Toggle word wrap

non numeric literal ::=

  • Expand
    < string >
  • Expand
    < binary string literal >
  • Expand
    FALSE
  • Expand
    TRUE
  • Expand
    UNKNOWN
  • Expand
    NULL
  • Expand
    ( < escaped type > < string > < rbrace > )
An escaped or simple non numeric literal.
Example: 
'a'
Copy to Clipboard Toggle word wrap

unsigned numeric literal ::=

  • Expand
    < unsigned integer literal >
  • Expand
    < approximate numeric literal >
  • Expand
    < decimal numeric literal >
An unsigned numeric literal value.
Example: 
1.234
Copy to Clipboard Toggle word wrap

ddl statement ::=

  • Expand
    ( < create table > | < create procedure > | < option namespace > | < alter options > | < create trigger > ) ( < semicolon > )?
A data definition statement.
Example: 
CREATE FOREIGN TABLE X (Y STRING)
Copy to Clipboard Toggle word wrap

option namespace ::=

  • Expand
    SET NAMESPACE < string > AS < identifier >
A namespace used to shorten the full name of an option key.
Example: 
SET NAMESPACE 'http://foo' AS foo
Copy to Clipboard Toggle word wrap

create procedure ::=

  • Expand
    CREATE ( VIRTUAL | FOREIGN )? ( PROCEDURE | FUNCTION ) ( < identifier > < lparen > ( < procedure parameter > ( < comma > < procedure parameter > )* )? < rparen > ( RETURNS ( ( ( TABLE )? < lparen > < procedure result column > ( < comma > < procedure result column > )* < rparen > ) | < data type > ) )? ( < options clause > )? ( AS < statement > )? )
Defines a procedure or function invocation.
Example: 
CREATE FOREIGN PROCEDURE proc (param STRING) RETURNS STRING
Copy to Clipboard Toggle word wrap

procedure parameter ::=

  • Expand
    ( IN | OUT | INOUT | VARIADIC )? < identifier > < data type > ( NOT NULL )? ( RESULT )? ( DEFAULT < string > )? ( < options clause > )?
A procedure or function parameter
Example: 
OUT x INTEGER
Copy to Clipboard Toggle word wrap

procedure result column ::=

  • Expand
    < identifier > < data type > ( NOT NULL )? ( < options clause > )?
A procedure result column.
Example: 
x INTEGER
Copy to Clipboard Toggle word wrap

create table ::=

  • Expand
    CREATE ( FOREIGN TABLE | ( VIRTUAL )? VIEW ) < identifier > ( < create table body > | ( < options clause > )? ) ( AS < query expression > )?
Defines a table or view.
Example: 
CREATE VIEW vw AS SELECT 1
Copy to Clipboard Toggle word wrap

create foreign temp table ::=

  • Expand
    CREATE FOREIGN TEMPORARY TABLE < identifier > < create table body > ON < identifier >
Defines a foreign temp table
Example: 
CREATE FOREIGN TEMPORARY TABLE t (x string) ON z
Copy to Clipboard Toggle word wrap

create table body ::=

  • Expand
    ( < lparen > < table element > ( < comma > < table element > )* ( < comma > ( CONSTRAINT < identifier > )? ( < primary key > | < other constraints > | < foreign key > ) ( < options clause > )? )* < rparen > )? ( < options clause > )?
Defines a table.
Example: 
(x string) OPTIONS (CARDINALITY 100)
Copy to Clipboard Toggle word wrap

foreign key ::=

  • Expand
    FOREIGN KEY < column list > REFERENCES < identifier > ( < column list > )?
Defines the foreign key referential constraint.
Example: 
FOREIGN KEY (a, b) REFERENCES tbl (x, y)
Copy to Clipboard Toggle word wrap

primary key ::=

  • Expand
    PRIMARY KEY < column list >
Defines the primary key.
Example: 
PRIMARY KEY (a, b)
Copy to Clipboard Toggle word wrap

other constraints ::=

  • Expand
    ( ( UNIQUE | ACCESSPATTERN ) < column list > )
  • Expand
    ( INDEX < lparen > < expression list > < rparen > )
Defines ACCESSPATTERN and UNIQUE constraints and INDEXes.
Example: 
UNIQUE (a)
Copy to Clipboard Toggle word wrap

column list ::=

  • Expand
    < lparen > < identifier > ( < comma > < identifier > )* < rparen >
A list of column names.
Example: 
(a, b)
Copy to Clipboard Toggle word wrap

table element ::=

  • Expand
    < identifier > < data type > ( NOT NULL )? ( AUTO_INCREMENT )? ( ( PRIMARY KEY ) | ( ( UNIQUE )? ( INDEX )? ) ) ( DEFAULT < string > )? ( < options clause > )?
Defines a table column.
Example: 
x INTEGER NOT NULL
Copy to Clipboard Toggle word wrap

options clause ::=

  • Expand
    OPTIONS < lparen > < option pair > ( < comma > < option pair > )* < rparen >
A list of statement options.
Example: 
OPTIONS ('x' 'y', 'a' 'b')
Copy to Clipboard Toggle word wrap

option pair ::=

  • Expand
    < identifier > ( < non numeric literal > | ( < plus or minus > )? < unsigned numeric literal > )
An option key/value pair.
Example: 
'key' 'value'
Copy to Clipboard Toggle word wrap

alter options ::=

  • Expand
    ALTER ( VIRTUAL | FOREIGN )? ( TABLE | VIEW | PROCEDURE ) < identifier > ( < alter options list > | < alter column options > )
alters options of tables/procedure
Example: 
ALTER FOREIGN TABLE foo OPTIONS (ADD cardinality 100)
Copy to Clipboard Toggle word wrap

alter options list ::=

  • Expand
    OPTIONS < lparen > ( < add set option > | < drop option > ) ( < comma > ( < add set option > | < drop option > ) )* < rparen >
a list of alterations to options
Example: 
OPTIONS (ADD updatable true)
Copy to Clipboard Toggle word wrap

drop option ::=

  • Expand
    DROP < identifier >
drop option
Example: 
DROP updatable
Copy to Clipboard Toggle word wrap

add set option ::=

  • Expand
    ( ADD | SET ) < option pair >
add or set an option pair
Example: 
ADD updatable true
Copy to Clipboard Toggle word wrap

alter column options ::=

  • Expand
    ALTER ( COLUMN | PARAMETER )? < identifier > < alter options list >
alters a set of column options
Example: 
ALTER COLUMN bar OPTIONS (ADD updatable true)
Copy to Clipboard Toggle word wrap
Back to top
Red Hat logoGithubredditYoutubeTwitter

Learn

Try, buy, & sell

Communities

About Red Hat Documentation

We help Red Hat users innovate and achieve their goals with our products and services with content they can trust. Explore our recent updates.

Making open source more inclusive

Red Hat is committed to replacing problematic language in our code, documentation, and web properties. For more details, see the Red Hat Blog.

About Red Hat

We deliver hardened solutions that make it easier for enterprises to work across platforms and environments, from the core datacenter to the network edge.

Theme

© 2025 Red Hat