LLI Wiki — My Digital Knowledge Base

SQL command types: DDL, DQL, DML, DCL and TCL

The five SQL statement categories, their purpose and the typical commands of each.

SQL statements are traditionally grouped into five categories according to what they do. The names are not strict standards, but they are widely used to organise and teach SQL.

Category Meaning Typical commands
DDL Data Definition Language CREATE, ALTER, DROP, TRUNCATE, RENAME, COMMENT
DQL Data Query Language SELECT
DML Data Manipulation Language INSERT, UPDATE, DELETE, MERGE, CALL, EXPLAIN PLAN, LOCK TABLE
DCL Data Control Language GRANT, REVOKE
TCL Transaction Control Language COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION

Some classifications place SELECT under DML instead of a separate DQL category; both views exist. The exact command list also varies between database engines (for example RENAME, COMMENT, EXPLAIN PLAN and SAVEPOINT are not available or are spelled differently in every product).

DDL: Data Definition Language

DDL commands define or change the structure of database objects (tables, indexes, views, schemas): CREATE creates an object, ALTER modifies it, DROP removes it, TRUNCATE removes all rows of a table quickly, and RENAME or COMMENT change its name or description.

SQL commands grouped into DDL, DML, DCL and TCL

DQL: Data Query Language

DQL is used to read data without changing it. It is essentially the SELECT statement, with its filters, joins, grouping and sorting.

DML: Data Manipulation Language

DML commands change the data stored in objects: INSERT adds rows, UPDATE modifies them, DELETE removes them and MERGE combines insert, update and delete in one statement. Commands such as CALL, EXPLAIN PLAN and LOCK TABLE are also listed here in some classifications.

DCL: Data Control Language

DCL manages access rights: GRANT gives a user or role a permission on an object and REVOKE takes it back.

TCL: Transaction Control Language

TCL manages transactions, that is, groups of statements that succeed or fail together: COMMIT makes the changes permanent, ROLLBACK cancels them, SAVEPOINT defines an intermediate point to which a transaction can be partially rolled back, and SET TRANSACTION sets the characteristics of a transaction.

Infographic: types of SQL commands (DML, DDL, DCL, TCL)

Mastodon LLIMastodon SocialFosstodon