# Derby support for SQL:2011 features

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.

## Support for mandatory features

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

Table 1. 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

Table 2. 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

Table 3. 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

Table 4. 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

Table 5. 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

Table 6. 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

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

Table 8. 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

Table 9. 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

Table 10. E121: Basic cursor support (through JDBC)
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

Table 11. 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

Table 12. 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

Table 13. 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.

Table 14. 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

Table 15. 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

Table 16. 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

Table 17. 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

Table 18. 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

Table 19. 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

Table 20. 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

Table 21. 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

## Support for optional features

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

Table 22. 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

Table 23. 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

Table 24. 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

Table 25. 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

Table 26. 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

Table 27. 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

Table 28. 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

Table 29. 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

- SQL:2011 features not supported by Derby
  Some mandatory and optional features in Part 2 of the SQL:2011 standard are not supported by Derby.