SQL statements

Learn about the syntax and usage of all SQL statements that are implemented in Exasol.

All SQL statements described in this section are supported in the latest version of Exasol. Some statements or features may not be supported in earlier versions. For more information, see the Release notes or contact Support.

Syntax diagrams

The syntax for each statement is presented as a railroad diagram that uses the following conventions:

  • Terminals (elements that must be entered literally in the SQL statement) are represented by rounded boxes with no fill.
  • Non-terminals (placeholders for values or references to other syntax diagrams) are represented by rectangular boxes with a grey fill.

Syntax diagram conventions

Data definition (DDL)

The structure of the database is defined using Data Definition Language (DDL). DDL statements can be used to create, modify, and remove schemas and all schema objects such as tables, views, functions, and scripts.

You can use the following statements to create or modify schemas: 

CREATE SCHEMA

DROP SCHEMA

ALTER SCHEMA

CREATE TABLE

SELECT INTO

DROP TABLE

ALTER TABLE(column)

ALTER TABLE(distribution/partitioning)

ALTER TABLE(constraints)

CREATE VIEW

DROP VIEW

CREATE FUNCTION

DROP FUNCTION

CREATE SCRIPT

DROP SCRIPT

RENAME

COMMENT

Data manipulation (DML)

You can use the following statements to change the content of a table:

INSERT

UPDATE

MERGE

DELETE

TRUNCATE

IMPORT

EXPORT

Data access control (DCL)

The SQL statements of the Data Control Language (DCL) are used to control the database access rights. Through the management of users and roles as well as the granting of privileges, you can determine who is permitted to perform what actions in the database.

For an introduction to the basic concepts of rights management, and further details such as a list of all privileges, a summary of the access rights for SQL statements, as well as the system tables relevant to rights management, refer to the Database users and roles, and Privileges sections.

Additionally, within the SQL reference for each SQL statement, the prerequisites regarding which privileges are necessary for the respective statement are specified.

You can use the following statements for access control:

CREATE USER

ALTER USER

DROP USER

CREATE ROLE

DROP ROLE

CREATE CONNECTION

ALTER CONNECTION

DROP CONNECTION

GRANT

REVOKE

Query language (DQL)

Contents from the database can be queried and analyzed with the Data Query Language (DQL).

You can use the following statements to query the database: 

SELECT Statement

Table Operators - UNION [ALL], INTERSECT, MINUS

Verification of data quality

You can use the following statements to analyze and ensure the quality of the data in the database:

Verification of the Primary Key Property (PRIMARY KEY)

Verification of the Uniqueness (UNIQUE)

Verification of the Foreign Key Property (FOREIGN KEY)

Other statements

SQL statements that cannot be categorized in any of the above sections:

COMMIT

CONSUMER GROUP

ROLLBACK

EXECUTE SCRIPT

KILL

ALTER SESSION

ALTER SYSTEM

OPEN SCHEMA

CLOSE SCHEMA

DESC[RIBE]

EXPLAIN VIRTUAL

RECOMPRESS

REORGANIZE

TRUNCATE AUDIT LOGS

FLUSH STATISTICS

PRELOAD

IMPERSONATE