In the tables that follow, the support status of each feature is indicated as follows:

• Yes: The feature is supported.

• Yes*: The feature is supported (for example, through JDBC) but not according to the SQL standard. See Note for details.

• Partial: The feature is partially supported.

• No: The feature is unsupported.

supports many features of the SQL:2011 standard. Most are in Part 2 (Foundation), but some are in other parts. The supported optional features with the prefix J are in Part 13 (JRT). The supported optional features with the prefix X are in Part 14 (SQL/XML).

lists the features in Part 2 that does not support.

The following tables describe support for SQL:2011 mandatory features.

This table describes support for E011, numeric data types. Feature ID Feature Name SQL:2011 Mandatory E011-01 INTEGER and SMALLINT data types (including all spellings) Yes E011-02 REAL, DOUBLE PRECISION, and FLOAT data types Yes E011-03 DECIMAL and NUMERIC data types Yes E011-04 Arithmetic operators Yes E011-05 Numeric comparison Yes E011-06 Implicit casting among the numeric data types Yes

This table describes support for E021, character data types. Feature ID Feature Name SQL:2011 Mandatory Note E021-01 CHARACTER data type (including all its spellings) Yes None E021-02 CHARACTER VARYING data type (including all its spellings) Yes None E021-03 Character literals Yes None E021-04 CHARACTER_LENGTH function Yes* Called LENGTH. {fn LENGTH(...)} is according to JDBC specification. E021-05 OCTET_LENGTH function No None E021-06 SUBSTRING function Yes* Called SUBSTR. {fn SUBSTRING(....)} is according to JDBC specification. E021-07 Character concatenation Yes None E021-08 UPPER and LOWER functions Yes None E021-09 TRIM function Yes None E021-10 Implicit casting among the character data types Yes None E021-11 POSITION function Yes* Called LOCATE. {fn LOCATE(...)} is according to JDBC specification. E021-12 Character comparison Yes None

This table describes support for E031, identifiers. Feature ID Feature Name SQL:2011 Mandatory E031-01 Delimited identifiers Yes E031-02 Lower case identifiers Yes E031-03 Trailing underscore Yes

This table describes support for E051, basic query specification. Feature ID Feature Name SQL:2011 Mandatory E051-01 SELECT DISTINCT Yes E051-02 GROUP BY clause Yes E051-04 GROUP BY can contain columns not in select-list Yes E051-05 Select list items can be renamed Yes E051-06 HAVING clause Yes E051-07 Qualified * in select list Yes E051-08 Correlation names in the FROM clause Yes E051-09 Rename columns in the FROM clause Yes

This table describes support for E061, basic predicates and search conditions. Feature ID Feature Name SQL:2011 Mandatory E061-01 Comparison predicate Yes E061-02 BETWEEN predicate Yes E061-03 IN predicate with list of values Yes E061-04 LIKE predicate Yes E061-05 LIKE predicate: ESCAPE clause Yes E061-06 NULL predicate Yes E061-07 Quantified comparison predicate Yes E061-08 EXISTS predicate Yes E061-09 Subqueries in comparison predicate Yes E061-11 Subqueries in IN predicate Yes E061-12 Subqueries in quantified comparison predicate Yes E061-13 Correlated subqueries Yes E061-14 Search condition Yes

This table describes support for E071, basic query expressions. Feature ID Feature Name SQL:2011 Mandatory E071-01 UNION DISTINCT table operator Yes E071-02 UNION ALL table operator Yes E071-03 EXCEPT DISTINCT table operator Yes E071-05 Columns combined via table operators need not have exactly the same data type Yes E071-06 Table operators in subqueries Yes

This table describes support for E081, basic privileges. Feature ID Feature Name SQL:2011 Mandatory E081-01 SELECT privilege at the table level Yes E081-02 DELETE privilege Yes E081-03 INSERT privilege at the table level Yes E081-04 UPDATE privilege at the table level Yes E081-05 UPDATE privilege at the column level Yes E081-06 REFERENCES privilege at the table level Yes E081-07 REFERENCES privilege at the column level Yes E081-08 WITH GRANT OPTION No E081-09 USAGE privilege No E081-10 EXECUTE privilege Yes

This table describes support for E091, set functions. Feature ID Feature Name SQL:2011 Mandatory E091-01 AVG Yes E091-02 COUNT Yes E091-03 MAX Yes E091-04 MIN Yes E091-05 SUM Yes E091-06 ALL quantifier Yes E091-07 DISTINCT qualifier Yes

This table describes support for E101, basic data manipulation. Feature ID Feature Name SQL:2011 Mandatory Note E101-01 INSERT statement Yes None E101-03 Searched UPDATE statement Partial correlation name not supported E101-04 Searched DELETE statement Partial correlation name not supported

This table describes support for E121, basic cursor support. Feature ID Feature Name SQL:2011 Mandatory Note E121-01 Declare cursor No None E121-02 ORDER BY columns need not be in select list Yes None E121-03 Value expressions in ORDER BY clause Yes None E121-06 Positioned UPDATE statement Partial correlation name not supported E121-07 Positioned DELETE statement Partial correlation name not supported E121-08 CLOSE statement No None E121-10 FETCH statement No None E121-17 WITH HOLD cursors No None

This table describes support for E141, basic integrity constraints. Feature ID Feature Name SQL:2011 Mandatory E141-01 NOT NULL constraints Yes E141-02 UNIQUE constraints of NOT NULL columns Yes E141-03 PRIMARY KEY constraints Yes E141-04 Basic FOREIGN KEY constraint with the NO ACTION default Yes E141-06 CHECK constraints Yes E141-07 Column defaults Yes E141-08 NOT NULL inferred on PRIMARY KEY Yes E141-10 Names in a foreign key can be specified in any order Yes

This table describes support for E151, transaction support. Feature ID Feature Name SQL:2011 Mandatory Note E151-01 COMMIT statement Yes* Through JDBC Connection.commit, ij supports COMMIT statement E151-02 ROLLBACK statement Yes* Through JDBC Connection.rollback, ij supports ROLLBACK statement

This table describes support for E152, basic SET TRANSACTION statement. Feature ID Feature Name SQL:2011 Mandatory Note E152-01 SET TRANSACTION statement: ISOLATION LEVEL SERIALIZABLE clause Yes* SET [CURRENT] ISOLATION SERIALIZABLE. Connection.setTransactionIsolation( Connection.TRANSACTION_SERIALIZABLE) is according to JDBC specification. E152-02 SET TRANSACTION statement: READ ONLY and READ WRITE clauses Yes* No SQL syntax. Connection.setReadWrite() is according to JDBC specification.

This table describes support for F031, basic schema manipulation. Feature ID Feature Name SQL:2011 Mandatory F031-01 CREATE TABLE statement to create persistent base tables Yes F031-02 CREATE VIEW statement Yes F031-03 GRANT statement Yes F031-04 ALTER TABLE statement: ADD COLUMN clause Yes F031-13 DROP TABLE statement: RESTRICT clause Yes (implicit) F031-16 DROP VIEW statement: RESTRICT clause Yes (implicit) F031-19 REVOKE statement: RESTRICT clause Yes

This table describes support for F041, basic joined tables. Feature ID Feature Name SQL:2011 Mandatory F041-01 Inner join (but not necessarily the INNER keyword) Yes F041-02 INNER keyword Yes F041-03 LEFT OUTER JOIN Yes F041-04 RIGHT OUTER JOIN Yes F041-05 Outer joins can be nested Yes F041-07 The inner table in a left or right outer join can also be used in an inner join No F041-08 All comparison operators are supported (rather than just =) Yes

This table describes support for F051, basic date and time. Feature ID Feature Name SQL:2011 Mandatory Note F051-01 DATE data type (including DATE literal) Yes* DATE literal is implemented as built-in function. {d 'yyyy-mm-ff'} is according to JDBC specification. F051-02 TIME data type (including TIME literal) with fractional seconds precision of 0 Yes* TIME literal is implemented as built-in function. No precision in datatype. {t' hh:mm:ss'} is according to JDBC specification. F051-03 TIMESTAMP data type (including TIMESTAMP literal) with fractional seconds precision of 0 and 6 Yes* TIMESTAMP literal is implemented as built-in function. No precision in datatype. No timezone in datatype. {ts 'yyyy-mm-dd hh:mm:ss.f...'} is according to JDBC specification. F051-04 Comparison predicate on DATE, TIME, and TIMESTAMP data types Yes None F051-05 Explicit CAST between datetime types and character types Yes None F051-06 CURRENT_DATE Yes* No time zone in datetime value expression F051-07 LOCALTIME Yes* {fn CURTIME()} is according to JDBC specification F051-08 LOCALTIMESTAMP No None

This table describes support for F131, grouped operations. Feature ID Feature Name SQL:2011 Mandatory F131-01 WHERE, GROUP BY, and HAVING clauses supported in queries with grouped views Yes F131-02 Multiple tables supported in queries with grouped views Yes F131-03 Set functions supported in queries with grouped views Yes F131-04 Subqueries with GROUP BY and HAVING clauses and grouped views Yes F131-05 Single row SELECT with GROUP BY and HAVING clauses and grouped views Yes

This table describes support for F261, CASE expression. Feature ID Feature Name SQL:2011 Mandatory F261-01 Simple CASE Yes F261-02 Searched CASE Yes F261-03 NULLIF function Yes F261-04 COALESCE function Yes

This table describes support for F311, schema definition statement. Feature ID Feature Name SQL:2011 Mandatory F311-01 Create schema Yes F311-02 CREATE TABLE for persistent base tables Yes F311-03 CREATE VIEW Yes F311-04 CREATE VIEW: WITH CHECK OPTION No F311-05 GRANT statement Yes

This table describes support for T321, basic SQL invoked routines. Feature ID Feature Name SQL:2011 Mandatory T321-01 User-defined functions with no overloading Yes T321-02 User-defined stored procedures with no overloading Yes T321-03 Function invocation Yes T321-04 CALL statement Yes T321-05 RETURN statement No

This table describes support for miscellaneous mandatory features. Feature ID Feature Name SQL:2011 Mandatory E111 Single row select statement Yes E131 Null value support (nulls in lieu of values) Yes E161 SQL comments using leading double minus Yes E171 SQLSTATE support Yes F201 CAST function Yes F221 Explicit defaults Yes F471 Scalar subquery values Yes F481 Expanded NULL predicate Yes T631 IN predicate with one list element Yes

The following tables show support for SQL:2011 optional features.

This table describes support for F111, isolation levels other than SERIALIZABLE. Feature ID Feature Name SQL:2011 Optional F111-01 READ UNCOMMITTED isolation level Yes F111-02 READ COMMITTED isolation level Yes F111-03 REPEATABLE READ isolation level Yes

This table describes support for F302, INTERSECT table operator. Feature ID Feature Name SQL:2011 Optional F302-01 INTERSECT DISTINCT table operator Yes F302-02 INTERSECT ALL table operator Yes

This table describes support for F381, extended schema manipulation. Feature ID Feature Name SQL:2011 Optional F381-01 ALTER TABLE statement: ALTER COLUMN clause Partial F381-02 ALTER TABLE statement: ADD CONSTRAINT clause Partial F381-03 ALTER TABLE statement: DROP CONSTRAINT clause Yes

This table describes support for F401, extended joined table. Feature ID Feature Name SQL:2011 Optional F401-01 NATURAL JOIN Yes F401-02 FULL OUTER JOIN No F401-04 CROSS JOIN Yes

This table describes support for F831, full cursor update. Feature ID Feature Name SQL:2011 Optional Note F831-01 Updatable scrollable cursors Partial Insensitive result set cursors F831-02 Updatable ordered cursors No None

This table describes support for T041, basic LOB data type support. Feature ID Feature Name SQL:2011 Optional Note T041-01 BLOB data type Yes None T041-02 CLOB data type Yes None T041-03 POSITION, LENGTH, LOWER, TRIM, UPPER, and SUBSTRING functions for LOB data types Yes* Not standard SQL syntax. See notes on features E021-04, E021-06, E021-09 and E021-11 T041-04 Concatenation of LOB data types Yes None T041-05 LOB locator: non-holdable No None

This table describes support for T211, basic trigger capability. Feature ID Feature Name SQL:2011 Optional Note T211-01 Triggers activated on UPDATE, INSERT, or DELETE of one base table Yes None T211-02 BEFORE triggers Yes* Need to specify non-standard ON CASCADE BEFORE. Before triggers cannot have INSERT, UPDATE or DELETE statements as their action T211-03 AFTER triggers Yes None T211-04 FOR EACH ROW triggers Yes None T211-05 Ability to specify a search condition that shall be True before the trigger is invoked Yes None T211-06 Support for run-time rules for the interaction of triggers and constraints No None T211-07 TRIGGER privilege Yes None T211-08 Multiple triggers for the same event are executed in the order in which they were created in the catalog Yes None

This table describes support for miscellaneous optional features. Feature ID Feature Name SQL:2011 Optional Note F033 ALTER TABLE statement: DROP COLUMN clause Yes None F200 TRUNCATE TABLE statement Yes None F262 Extended CASE expression Yes None F263 Comma-separated predicates in simple CASE expression Yes None F281 LIKE enhancements Yes None F304 EXCEPT ALL table operator Yes None F312 MERGE statement Yes None F313 Enhanced MERGE statement Yes None F314 MERGE statement with DELETE branch Yes None F382 Alter column data type Partial You can alter only VARCHAR, VARCHAR FOR BIT DATA, BLOB, and CLOB columns, and you can change only the length. That is, you can change the data type from VARCHAR(10) to VARCHAR(100), but not from VARCHAR(10) to CLOB(100). F383 Set column not null clause Yes None F391 Long identifiers Yes None F402 Named column joins for LOBs, arrays, and multisets Yes None F431 Read-only scrollable cursors Yes* Through JDBC (only insensitive cursors) F491 Constraint management Yes None F492 Optional table constraint enforcement Yes None F531 Temporary tables Partial Global tables (DECLARE GLOBAL TEMPORARY TABLE statement) F591 Derived tables Yes None F641 Row and table constructors Yes None F690 Collation support Partial Users can create a database with territory-based collation F701 Referential update actions Partial None F721 Deferrable constraints Partial Deferrable NOT NULL constraints are not supported F763 CURRENT_SCHEMA Partial Non-standard syntax (CURRENT SCHEMA instead of CURRENT_SCHEMA), and it is not allowed in a DEFAULT clause F781 Self-referencing operations Yes None F791 Insensitive cursors Yes* Through JDBC F801 Full set function Partial DISTINCT in more than one aggregate function will not work, but SELECT DISTINCT with DISTINCT in one aggregate function will work F850 Top-level <order by clause> in <query expression> Yes None F851 <order by clause> in subqueries Yes None F852 Top-level <order by clause> in views Yes None F855 Nested <order by clause> in <query expression> Yes None F856 Nested <fetch first clause> in <query expression> Yes None F857 Top-level <fetch first clause> in <query expression> Yes None F858 <fetch first clause> in subqueries Yes None F859 Top-level <fetch first clause> in views Yes None F860 Dynamic <fetch first row count> in <fetch first clause> Yes None F861 Top-level <result offset clause> in <query expression> Yes None F862 <result offset clause> in subqueries Yes None F863 Nested <result offset clause> in <query expression> Yes None F864 Top-level <result offset clause> in views Yes None F865 Dynamic <offset row count> in <result offset clause> Yes None J581 Output parameters Yes None J621 External Java routines Yes None J622 External Java types Yes None T021 BINARY and VARBINARY data types Yes* Non-standard type names CHAR FOR BIT DATA and VARCHAR FOR BIT DATA instead of BINARY and VARBINARY T031 BOOLEAN data type Yes None T042 Extended LOB data type support Partial CAST and string value functions implemented. No comparison or ordering. T071 BIGINT data type Yes None T101 Enhanced nullability determination Yes None T174 Identity columns Yes* MAXVALUE and CYCLE not supported. Deviation from standard: A comma (,) is required before INCREMENT. T175 Generated columns Yes None T176 Sequence generator support Yes* ALTER SEQUENCE not supported. Only one NEXT VALUE FOR clause per sequence in each statement T191 Referential action RESTRICT Yes None T212 Enhanced trigger capability Yes None T271 Savepoints Yes None T281 SELECT privilege with column granularity Yes None T323 Explicit security for external routines Yes None T326 Table functions Partial None T331 Basic roles Partial None T332 Extended roles Partial None T351 Bracketed SQL comments (/*...*/ comments) Yes None T431 Extended grouping capabilities Partial Partial support for GROUP BY ROLLUP T441 ABS and MOD functions Yes None T501 Enhanced EXISTS predicate Yes None T591 UNIQUE constraints of possibly null columns Yes None T611 Elementary OLAP operations Partial Partial support for ROW_NUMBER X010 XML type Yes None X016 Persistent XML values Yes None X061 XMLParse: Character string input and DOCUMENT option Partial No support for the STRIP WHITESPACE option X096 XMLExists Partial Support only for XPath queries, not full XQuery X200 XMLQuery Partial Support only for XPath queries, not full XQuery X202 XMLQuery: RETURNING SEQUENCE Yes None X203 XMLQuery: passing a context item Yes None X205 XMLQuery: EMPTY ON EMPTY option Yes None X222 XML passing mechanism BY REF Yes None