Difference between DDL and DML in Tabular Form

calendarOct 26, 2022 · 3 min read  ·
Share on: linkedincopy
Key Difference: DDL, which stands for Data Definition Language, is mainly used to define the schema within a database. It also defines the relationship between the entries within the database. DML, which stands for Data Manipulation Language, is also a Database Language. Nevertheless, unlike DDL, DML can be utilized to insert, delete, revive, and modify the data, rather than just specify it.

DDL and DML are two types of Database Languages. They are called sub-languages, as they have fewer facilities than full-fledged programming languages. Both belong to a family of syntax elements that are primarily used in reference to databases. Both utilize statements and commands of Structured Query Language (SQL), which are used to retrieve and manipulate data in a relational database.

DDL, which stands for Data Definition Language, is mainly used to define the schema within a database. It also defines the relationship between the entries within the database. DDL can also be used to define certain security constraints. As previously stated DDL primarily utilizes SQL commands which are used to create, alter, and destroy database objects.

Once executed, the DDL commands results in new tables which are stored in "system catalog". This catalog is also called a data dictionary or data directory. However, the changes can only define the data within the directory and their relationship to one another. Whereas, DML can be used to modify the data itself.

DML, which stands for Data Manipulation Language, is also a Database Language. Like DDL, it’s primarily role is within database management. Nevertheless, unlike DDL, DML can be utilized to insert, delete, revive, and modify the data, rather than just specify it.

These data manipulations can be applied at internal, conceptual and external levels of schemas. However, it should be noted that the level of complexity at each schema level varies from one another. For example, complex low-level procedures allow efficient access, but procedures focus on ease of usage, thus they involve low complexity.

DML is also often used in retrieval of data. It can be divided into two segments as based on its retrieval constructs: Procedural DML, and Non-procedural DML. Procedural DML specifies what data needs to be retrieved and how the required data should be retrieved. Non-procedural DML specifies what data needs to be retrieved but not how the required data should be retrieved.

Comparison between DDL and DML in Tabular Form:

DDL

DML

Full Form

Data Definition Language

Data Manipulation Language

Type of

Database Language

Database Language

Subset of

Structured Query Language (SQL)

Structured Query Language (SQL)

Uses

Used for defining the data structures, especially database schemas

Used for data manipulation of data structures

Types

Related to

Defining data

Inserting, deleting, reviving, and modifying data

Commands

CREATE - to create objects in the database

ALTER - alters the structure of the database

DROP - delete objects from the database

TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed

COMMENT - add comments to the data dictionary

RENAME - rename an object

SELECT - retrieve data from the a database

INSERT - insert data into a table

UPDATE - updates existing data within a table

DELETE - deletes all records from a table, the space for the records remain

MERGE - UPSERT operation (insert or update)

CALL - call a PL/SQL or Java subprogram

EXPLAIN PLAN - explain access path to data

LOCK TABLE - control concurrency

Image Courtesy: sqa.org.uk, sqlinstructor.com

ncG1vJloZrCvp2OxqrLFnqmeppOar6bA1p6cp2aZo7Owe8OinZ%2Bdopq7pLGMm5ytr5Wau26ww6VkmqaUYrGuuIyipWaskZfCra3RZp2oqp0%3D