Compliance to SQL Standard
This section 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 |
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 |