Skip to main content

SQL Dialects & Conformance

This page provides details on the SQL dialects supported by Databend, along with its conformity to the SQL standard, particularly focusing on SQL:2011 features and their support status within Databend.

Supported SQL Dialects

A SQL dialect refers to a particular variation or flavor of the Structured Query Language. Databend supports the PostgreSQL dialect by default and offers the flexibility to switch to other supported dialects. Please refer to the table below for details on the supported dialects and their respective brief descriptions:

DialectIntroductionLearn More
PostgreSQLDefault supported dialect commonly used in enterprises
MySQLOpen-source database management system
HiveData warehouse for big data processing
PrqlPRQL is a modern language for transforming data — a simple, powerful, pipelined SQL replacement
ExperimentalExperimental dialect for testing and researchN/A

To switch between the supported SQL dialects or display the current one, use the sql_dialect setting:

-- Set SQL dialect to PRQL
SET sql_dialect = 'Prql';

-- Display current dialect
SHOW SETTINGS LIKE 'sql_dialect';

SQL Conformance Summary

Databend aims to conform to the SQL standard, with particular support for ISO/IEC 9075:2011, also known as SQL:2011. While not an exhaustive statement of conformance, Databend incorporates many features required by the SQL standard, often with slight differences in syntax or function. This page outlines the level of conformity of Databend to the SQL:2011 standard.

Feature IDFeature NameSupported?Note
E011Numeric data typesYes
E011-01INTEGER and SMALLINT data typesYes
E011-02REAL, DOUBLE PRECISION and FLOAT data typesYes
E011-03DECIMAL and NUMERIC data typesYes
E011-04Arithmetic operatorsYes
E011-05Numeric comparisonYes
E011-06Implicit casting among the numeric data typesYes
E021Character string typesPartial
E021-01CHARACTER data typeNoFixed-length string type not supported
E021-02CHARACTER VARYING data typeYes
E021-03Character literalsYes
E021-04CHARACTER_LENGTH functionYes
E021-05OCTET_LENGTH functionYes
E021-07Character concatenationYes
E021-08UPPER and LOWER functionsYes
E021-09TRIM functionYes
E021-10Implicit casting among the fixed-length and variable-length character string typesNoFixed-length string type not supported
E021-11POSITION functionYes
E021-12Character comparisonYes
E031-01Delimited identifiersYes
E031-02Lower case identifiersYes
E031-03Trailing underscoreYes
E051Basic query specificationPartial
E051-02GROUP BY clauseYes
E051-04GROUP BY can contain columns not in SELECT listYes
E051-05Select items can be renamedYes
E051-06HAVING clauseYes
E051-07Qualified * in select listNo
E051-08Correlation name in the FROM clauseYes
E051-09Rename columns in the FROM clauseNo
E061Basic predicates and search conditionsPartial
E061-01Comparison predicateYes
E061-02BETWEEN predicateYes
E061-03IN predicate with list of valuesYes
E061-04LIKE predicateYes
E061-05LIKE predicate: ESCAPE clauseNo
E061-06NULL predicateYes
E061-07Quantified comparison predicateYes
E061-08EXISTS predicateYes
E061-09Subqueries in comparison predicateYes
E061-11Subqueries in IN predicateYes
E061-12Subqueries in quantified comparison predicateYes
E061-13Correlated subqueriesYes
E061-14Search conditionYes
E071Basic query expressionsPartial
E071-01UNION DISTINCT table operatorYes
E071-02UNION ALL table operatorYes
E071-03EXCEPT DISTINCT table operatorYes
E071-05Columns combined via table operators need not have exactly the same data typePartialOnly columns with data types that can be implicitly coerced are allowed to be combined with table operators.
E071-06Table operators in subqueriesYes
E081Basic privilegesPartial
E081-01SELECT privilege at the table levelYes
E081-02DELETE privilegeYes
E081-03INSERT privilege at the table levelYes
E081-04UPDATE privilege at the table levelYes
E081-05UPDATE privilege at the column levelNo
E081-06REFERENCES privilege at the table levelNo
E081-07REFERENCES privilege at the column levelNo
E081-09USAGE privilegeNo
E081-10EXECUTE privilegeNo
E091Set functionsYes
E091-06ALL quantifierYes
E091-07DISTINCT quantifierPartialCurrently, Databend supports COUNT(DISTINCT ...) and SELECT DISTINCT ... queries.
E101Basic data manipulationPartial
E101-01INSERT statementYes
E101-03Searched UPDATE statementYes
E101-04Searched DELETE statementYes
E111Single row SELECT statementYes
E121Basic cursor supportPartial
E121-02ORDER BY columns need not be in select listYes
E121-03Value expressions in ORDER BY clauseYes
E121-04OPEN statementNo
E121-06Positioned UPDATE statementNo
E121-07Positioned DELETE statementNo
E121-08CLOSE statementNo
E121-10FETCH statement: implicit NEXTNo
E121-17WITH HOLD cursorsNo
E131Null value support (nulls in lieu of values)Yes
E141Basic integrity constraintsNo
E141-01NOT NULL constraintsYesDefault in Databend: All columns are nullable.
E141-02UNIQUE constraint of NOT NULL columnsNo
E141-03PRIMARY KEY constraintsNo
E141-04Basic FOREIGN KEY constraint with the NO ACTION default for both referential delete action and referential update actionNo
E141-06CHECK constraintNo
E141-07Column defaultsYes
E141-08NOT NULL inferred on PRIMARY KEYNo
E141-10Names in a foreign key can be specified in any orderNo
E151Transaction supportPartial
E151-01COMMIT statementPartialDatabend only supports implicit transactions for every individual DML statement.
E151-02ROLLBACK statementNo
E152Basic SET TRANSACTION statementNo
E152-02SET TRANSACTION statement: READ ONLY and READ WRITE clausesNo
E153Updatable queries with subqueriesYes
E161SQL comments using leading double minusYes
E171SQLSTATE supportNo
E182Host language bindingNo
F031Basic schema manipulationYes
F031-01CREATE TABLE statement to create persistent base tablesYes
F031-02CREATE VIEW statementYes
F031-03GRANT statementPartial
F031-04ALTER TABLE statement: ADD COLUMN clauseYes
F031-13DROP TABLE statement: RESTRICT clausePartial
F031-16DROP VIEW statement: RESTRICT clausePartial
F031-19REVOKE statement: RESTRICT clausePartial
F041Basic joined tableYes
F041-01Inner join (but not necessarily the INNER keyword)Yes
F041-02INNER keywordYes
F041-05Outer joins can be nestedYes
F041-07The inner table in a left or right outer join can also be used in an inner joinYes
F041-08All comparison operators are supported (rather than just =)Yes
F051Basic date and timePartial
F051-01DATE data type (including support of DATE literal)Yes
F051-02TIME data type (including support of TIME literal) with fractional seconds precision of at least 0No
F051-03TIMESTAMP data type (including support of TIMESTAMP literal) with fractional seconds precision of at least 0 and 6Yes
F051-04Comparison predicate on DATE, TIME, and TIMESTAMP data typesYes
F051-05Explicit CAST between datetime types and character string typesYes
F081UNION and EXCEPT in viewsYes
F131Grouped operationsYes
F131-01WHERE, GROUP BY, and HAVING clauses supported in queries with grouped viewsYes
F131-02Multiple tables supported in queries with grouped viewsYes
F131-03Set functions supported in queries with grouped viewsYes
F131-04Subqueries with GROUP BY and HAVING clauses and grouped viewsYes
F131-05Single row SELECT with GROUP BY and HAVING clauses and grouped viewsYes
F181Multiple module supportNo
F201CAST functionYes
F221Explicit defaultsNo
F261CASE expressionYes
F261-01Simple CASEYes
F261-02Searched CASEYes
F311Schema definition statementPartial
F311-02CREATE TABLE for persistent base tablesYes
F311-05GRANT statementPartial
F471Scalar subquery valuesYes
F481Expanded NULL predicateYes
F812Basic flaggingNo
S011Distinct data typesNo
T321Basic SQL-invoked routinesNo
T321-01User-defined functions with no overloadingYes
T321-02User-defined stored procedures with no overloadingNo
T321-03Function invocationYes
T321-04CALL statementNo
T321-05RETURN statementNo
T631IN predicate with one list elementYes
Did this page help you?
Explore Databend Cloud for FREE
Fast Analytics
Easy Data Ingestion
Elastic Scaling
Try it today