This article lists the mandatory and optional features of the SQL standard that are supported by Exasol.
SQL:2023 mandatory features
The following table lists all mandatory features in the SQL standard ISO/IEC 9075:2023 that are fully and partially supported in Exasol.
| Feature | Support in Exasol | |
|---|---|---|
| FULL | PARTIAL | |
| E011 Numeric data types |
|
|
| E011-01 INTEGER and SMALLINT data types |
|
|
| E011-02 REAL, DOUBLE PRECISION, and FLOAT data types |
|
|
| E011-03 DECIMAL and NUMERIC data types |
|
|
| E011-04 Arithmetic operators |
|
|
| E011-05 Numeric comparison |
|
|
| E011-06 Implicit casting among numeric data types |
|
|
| E021 Character string types |
|
|
| E021-01 CHARACTER data type |
|
|
| E021-02 CHARACTER VARYING data type |
|
|
| E021-03 Character literals |
|
|
| E021-04 CHARACTER_LENGTH function |
|
|
| E021-05 OCTET_LENGTH function |
|
|
| E021-06 SUBSTRING function |
|
|
| E021-07 Character concatenation |
|
|
| E021-08 UPPER and LOWER function |
|
|
| E021-09 TRIM function |
|
|
| E021-10 Implicit casting among fixed-length and variable-length character string types |
|
|
| E021-11 POSITION function |
|
|
| E021-12 Character comparison |
|
|
| E031 Identifiers |
|
|
| E031-01 Delimited identifiers |
|
|
| E031-02 lower-case identifiers |
|
|
| E031-03 Trailing underscore |
|
|
| E051 Basic query specification |
|
|
| E051-01 SELECT DISTINCT |
|
|
| E051-02 GROUP BY clause |
|
|
| E051-04 GROUP BY can contain columns not in <select list>. |
|
|
| E051-05 Select list items can be renamed |
|
|
| E051-06 HAVING clause |
|
|
| E051-07 Qualified * in select list |
|
|
| E051-08 Correlation names in the FROM clause |
|
|
| E051-09 Rename columns in the FROM clause |
|
|
| E061 Basic predicates and search conditions |
|
|
| E061-01 Comparison predicate |
|
|
| E061-02 BETWEEN predicate |
|
|
| E061-03 IN predicate with list of values |
|
|
| E061-04 LIKE predicate |
|
|
| E061-05 LIKE predicate: ESCAPE clause |
|
|
| E061-06 NULL predicate |
|
|
| E061-07 Quantified comparison predicate | ||
| E061-08 EXISTS predicate |
|
|
| E061-09 Subqueries in comparison predicate |
|
|
| E061-11 Subqueries in IN predicate |
|
|
| E061-12 Subqueries in quantified comparison predicate | ||
| E061-13 Correlated Subqueries |
|
|
| E061-14 Search condition |
|
|
| E071 Basic query expressions |
|
|
| E071-01 UNION DISTINCT table operator |
|
|
| E071-02 UNION ALL table operator |
|
|
| E071-03 EXCEPT DISTINCT table operator |
|
|
| E071-05 Columns combined via table operators need not have exactly the same data type. |
|
|
| E071-06 Table operators in subqueries |
|
|
| E081 Basic privileges |
|
|
| E081-01 SELECT privilege at the table level |
|
|
| E081-02 DELETE privilege |
|
|
| E081-03 INSERT privilege at the table level |
|
|
| E081-04 UPDATE privilege at the table level |
|
|
| E081-05 UPDATE privilege at the column level | ||
| E081-06 REFERENCES privilege at the table level |
|
|
| E081-07 REFERENCES privilege at the column level | ||
| E081-08 WITH GRANT OPTION | ||
| E081-09 USAGE privilege | ||
| E081-10 EXECUTE privilege |
|
|
| E091 Set functions |
|
|
| E091-01 AVG |
|
|
| E091-02 COUNT |
|
|
| E091-03 MAX |
|
|
| E091-04 MIN |
|
|
| E091-05 SUM |
|
|
| E091-06 ALL quantifier |
|
|
| E091-07 DISTINCT quantifier |
|
|
| E101 Basic data manipulation |
|
|
| E101-01 INSERT statement |
|
|
| E101-03 Searched UPDATE statement |
|
|
| E101-04 Searched DELETE statement |
|
|
| E111 Single row SELECT statement | ||
| E121 Basic cursor support | ||
| E121-01 DECLARE CURSOR | ||
| E121-02 ORDER BY columns need not be in select list | ||
| E121-03 Value expressions in ORDER BY clause | ||
| E121-04 OPEN statement | ||
| E121-06 Positioned UPDATE statement | ||
| E121-07 Positioned DELETE statement | ||
| E121-08 CLOSE statement | ||
| E121-10 FETCH statement: implicit NEXT | ||
| E121-17 WITH HOLD cursors | ||
| E131 Null value support (nulls in lieu of values) |
|
|
| E141 Basic integrity constraints |
|
|
| E141-01 NOT NULL constraint |
|
|
| E141-02 UNIQUE constraints of NOT NULL columns | ||
| E141-03 PRIMARY KEY constraint |
|
|
| E141-04 Basic FOREIGN KEY constraint with the NO ACTION default for both referential delete action and referential update action |
|
|
| E141-06 CHECK constraint | ||
| E141-07 Column defaults |
|
|
| E141-08 NOT NULL inferred on PRIMARY KEY |
|
|
| E141-10 Names in a foreign key can be specified in any order. | ||
| E151 Transaction support |
|
|
| E151-01 COMMIT statement |
|
|
| E151-02 ROLLBACK statement |
|
|
| E152 Basic SET TRANSACTION statement | ||
| E152-01 SET TRANSACTION statement: ISOLATION LEVEL SERIALIZABLE clause | ||
| E152-02 SET TRANSACTION statement: READ ONLY and READ WRITE clauses | ||
| E153 Updatable queries with subqueries | ||
| E161 SQL comments using leading double minus |
|
|
| E171 SQLSTATE support | ||
| E182 Module language | ||
| F031 Basic schema manipulation |
|
|
| F031-01 CREATE TABLE statement to create persistent base tables |
|
|
| F031-02 CREATE VIEW statement |
|
|
| F031-03 GRANT statement |
|
|
| F031-04 ALTER TABLE statement: ADD COLUMN clause |
|
|
| F031-13 DROP TABLE statement: RESTRICT clause |
|
|
| F031-16 DROP VIEW statement: RESTRICT clause |
|
|
| F031-19 REVOKE statement: RESTRICT clause |
|
|
| F041 Basic joined table |
|
|
| F041-01 Inner join (but not necessarily the INNER keyword) |
|
|
| F041-02 INNER keyword |
|
|
| F041-03 LEFT OUTER JOIN |
|
|
| F041-04 RIGHT OUTER JOIN |
|
|
| F041-05 Outer joins can be nested |
|
|
| F041-07 The inner table in a left or right outer join can also be used in an inner join. |
|
|
| F041-08 All comparison operators are supported (rather than just =). | ||
| F051 Basic date and time |
|
|
| F051-01 DATE data type (including support of DATE literal) |
|
|
| F051-02 TIME data type (including the support of TIME literal) with fractional seconds precision of at least 0. | ||
| F051-03 TIMESTAMP data type (including the support of TIMESTAMP literal) with fractional seconds precision of at least 0 and 6. |
|
|
| F051-04 Comparison predicate on DATE, TIME, and TIMESTAMP data types |
|
|
| F051-05 Explicit CAST between datetime types and character string types |
|
|
| F051-06 CURRENT_DATE |
|
|
| F051-07 LOCALTIME | ||
| F051-08 LOCALTIMESTAMP |
|
|
| F081 UNION and EXCEPT in views |
|
|
| F131 Grouped operations |
|
|
| F131-01 WHERE, GROUP BY, and HAVING clauses supported in queries with grouped views |
|
|
| F131-02 Multiple tables supported in queries with grouped views |
|
|
| F131-03 Set functions supported in queries with grouped views |
|
|
| F131-04 Subqueries with GROUP BY and HAVING clauses and grouped views |
|
|
| F131-05 Single row SELECT with GROUP BY and HAVING clauses and grouped views | ||
| F181 Multiple module support |
|
|
| F201 CAST function |
|
|
| F221 Explicit defaults |
|
|
| F261 CASE expression |
|
|
| F261-01 Simple CASE |
|
|
| F261-02 Searched CASE |
|
|
| F261-03 NULLIF |
|
|
| F261-04 COALESCE |
|
|
| F311 Schema definition statement |
|
|
| F311-01 CREATE SCHEMA |
|
|
| F311-02 CREATE TABLE for persistent base tables (within CREATE SCHEMA) | ||
| F311-03 CREATE VIEW (within CREATE SCHEMA) | ||
| F311-04 CREATE VIEW: WITH CHECK OPTION (within CREATE SCHEMA) | ||
| F311-05 GRANT STATEMENT (within CREATE SCHEMA) | ||
| F471 Scalar subquery values |
|
|
| F481 Expanded NULL predicate |
|
|
| S011 Distinct data types | ||
| T321 Basic SQL-invoked routines |
|
|
| T321-01 User-defined functions with no overloading |
|
|
| T321-02 User-defined stored procedures with no overloading | ||
| T321-03 Function invocation |
|
|
| T321-04 CALL statement | ||
| T321-05 RETURN statement |
|
|
| T631 IN predicate with one list element |
|
|
SQL:2023 optional features
The following table lists the optional features in the SQL standard ISO/IEC 9075:2023 that are supported in Exasol.
| Feature ID | Feature |
|---|---|
| F033 | ALTER TABLE statement: DROP COLUMN clause |
| F052 | Intervals and datetime arithmetic |
| F171 | Multiple schemas per user |
| F200 | TRUNCATE TABLE statement |
| F222 | INSERT statement: DEFAULT VALUES clause |
| F303 | INTERSECT DISTINCT table operator |
| F312 | MERGE statement |
| F321 | User authorization |
| F381 | Extended schema manipulation |
| F387 | ALTER TABLE statement: ALTER COLUMN clause |
| F388 | ALTER TABLE statement: ADD/DROP CONSTRAINT clause |
| F391 | Long identifiers |
| F406 | FULL OUTER JOIN |
| F407 | CROSS JOIN |
| F555 | Enhanced seconds precision |
| F641 | Row and table constructors |
| F851 | ORDER BY in subqueries |
| F852 | Top-level ORDER BY in views |
| T031 | BOOLEAN data type |
| T121 | WITH (excluding RECURSIVE) in query expression |
| T171 | LIKE clause in table definition |
| T172 | AS subquery clause in table definition |
| T173 | Extended LIKE clause in table definition |
| T174 | Identity columns |
| T331 | Basic roles |
| T351 | Bracketed comments (/* ... */ comments) |
| T431 | Extended grouping capabilities |
| T432 | Nested and concatenated GROUPING SETS |
| T433 | Multiargument GROUPING function |
| T434 | GROUP BY DISTINCT |
| T441 | ABS and MOD functions |
| T461 | Symmetric BETWEEN predicate |
| T551 | Optional key words for default syntax |
| T614 | NTILE function |
| T615 | LEAD and LAG functions |
| T616 | Null treatment option for LEAD and LAG functions |
| T617 | FIRST_VALUE and LAST_VALUE functions |
| T618 | NTH_VALUE function |
| T620 | WINDOW clause: GROUPS option |
| T621 | Enhanced numeric functions |
| T622 | Trigonometric functions |
| T623 | General logarithm functions |
| T624 | Common logarithm functions |
| T625 | LISTAGG |
| T627 | Window framed COUNT DISTINCT |
