The SQL:2011 standard puts features into two categories, mandatory and optional.
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.
Derby 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).
SQL:2011 features not supported by Derby lists the features in Part 2 that Derby does not support.
The following tables describe support for SQL:2011 mandatory features.
| 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 |
| 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 |
| Feature ID | Feature Name | SQL:2011 Mandatory |
|---|---|---|
| E031-01 | Delimited identifiers | Yes |
| E031-02 | Lower case identifiers | Yes |
| E031-03 | Trailing underscore | Yes |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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. |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 Derby support for SQL:2011 optional features.
| 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 |
| Feature ID | Feature Name | SQL:2011 Optional |
|---|---|---|
| F302-01 | INTERSECT DISTINCT table operator | Yes |
| F302-02 | INTERSECT ALL table operator | Yes |
| 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 |
| Feature ID | Feature Name | SQL:2011 Optional |
|---|---|---|
| F401-01 | NATURAL JOIN | Yes |
| F401-02 | FULL OUTER JOIN | No |
| F401-04 | CROSS JOIN | Yes |
| 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 |
| 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 |
| 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 |
| 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 |