Compliance to SQL Standard

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