Understanding the Differences Between DDL and DML

Understanding the Differences Between DDL and DML

3 mins read1K Views Comment
Vikram
Vikram Singh
Assistant Manager - Content
Updated on Oct 3, 2023 11:52 IST

DDL commands (statement) is used to create or define the database schema, structure, and constraints, while DML command (or statement) is used to add, retrieve, or update the data, i.e., use to manage the data within the schema objects. In this article, we will learn the difference between DDL and DML with examples.

2023_01_MicrosoftTeams-image-122.jpg

SQL is a programming language used to Create, Read, Update, and Delete the information in a relational database. The commands in SQL are mainly categorized into four different categories: DDL, DML, DCL, and DQL. This article will learn the difference between DDL and DML commands.

So, without further delay, let’s move on to learn the difference between these commands.

Must Read: Introduction to SQL

Must Read: SQL Tutorial: Basic to Advance

Table of Content

DDL vs. DML: Difference Between DDL and DML

Parameter DDL DML
Definition DDL commands (statement) is used to create or define the database schema, structure, and constraints. DML command (or statement) is used to add, retrieve, or update the data, i.e., use to manage the data within the schema objects.
Full-Form Data Definition Language Data Manipulation Language
Commands CREATE, ALTER, TRUNCATE, RENAME, DROP SELECT, UPDATE, DELETE, INSERT
Where Clause Doesn’t use Use
Rollback Queries can’t be a rollback, i.e., changes made are permanent (Auto-Committed).  Queries can be a rollback, i.e., changes made are not permanent (Auto-Committed).
Classification No further classification. Classified into Procedural and Non-procedural DML.
Affect It affects the whole table. It only affects the defined rows of the table.

What is DDL?

DDL commands in SQL are used to deal with descriptions of the database schema and to create and modify the structure of the database objects in the database.

  • It doesn’t deal with the data itself.
  • It Creates, Deletes, and Drops the schema objects.
  • Allows multiple users to work on the same databases.
  • It adds comments to the data dictionary.
  • It allows for storing shared data and improves the security efficiency of the data access.

Examples of DDL Commands:

  • CREATE: It is used to create a new database or the components of the database (such as tables).
  • ALTER: It is used to change the structure of the existing table, i.e., add/modify/rename constraints, and columns in the table or the database.
  • DROP: It removes the table definition and all the data from one or more database tables. 
  • TRUNCATE: Removes all the records from the table or the specified partitions of the table.

What is DML?

DML commands are used to manipulate the data within the schema created by DDL. They are generally used for inserting, updating, and querying from the relational database.

  • It helps to manipulate the data.
    • Users are allowed to specify the data that is needed to manipulate the data.
  • Offers efficient human interaction with the system.

Examples of DML Commands

  • SELECT: It retrieves data from single or multiple tables.
  • UPDATE: It is used to update the existing record from the table.
  • DELETE: It removes one row from the table at a time.
  • INSERT: It is used to insert the data into the table.

Key Difference between DDL and DML Statements in SQL

  • DDL statements help define the database’s structure, while DML statements help to manage the database.
  • DDL statements affect the whole table, whereas the DML statements affect only the defined rows of the table.
  • CREATE, ALTER, TRUNCATE, RENAME, and DROP are the commands of DDL, while SELECT, UPDATE, DELETE, and INSERT are the commands of DML.
  • DML commands are further classified into Procedural and Non-Procedural, while DDL commands are not further classified.
  • Data in DML commands can be filtered using the WHERE clause, while the data in DDL commands can’t be filtered using the WHERE clause.

Conclusion

In this article, we have learned:

About the Author
author-image
Vikram Singh
Assistant Manager - Content

Vikram has a Postgraduate degree in Applied Mathematics, with a keen interest in Data Science and Machine Learning. He has experience of 2+ years in content creation in Mathematics, Statistics, Data Science, and Mac... Read Full Bio