To first understand the key differences between DML and DDL when writing SQL we should define what these two acronyms actually stand for:
- DDL – Data Definition Language
- DML – Data Manipulation Language
DDL is to define and DML is to manipulate. These are the main important distinctions when remembering how they differ. We can use DDL to define or modify an object. On the other hand DML is used for manipulating data within objects.
Common DDL Statements:
- CREATE – used to create objects (ex. TABLE, VIEW, INDEX, etc..)
- DROP – used to delete objects (ex. TABLE, VIEW, INDEX, etc..)
- ALTER – used to alter the structure objects (ex. adding or removing columns in a table)
- TRUNCATE – used to empty and reset a tables contents while by-passing constraints
Common DML Statements:
- SELECT – used to retrieve data from objects
- INSERT – used to insert data into a table
- UPDATE – used to update data in a table
- DELETE – used to delete records in a table
We can see the differences are pretty clear when comparing DML vs DDL, which is the reasoning for the separate classifications. Another easy way to remember the differences between the two is that DDL affects objects while DML affects data. In the example above, updating the DimCustomer table is directly changing the data within the table, while altering the table is changing the structure by adding two new columns.
While DML and DDL are arguably the most important, they aren’t the only two SQL classifications. Without going into great detail there’s also DCL (Data Control Language), DQL (Data Query Language), and TCL (Transaction Control Language). DCL is used for permissions, DQL refers specifically to a SELECT statement, and TCL commands relate directly to a transaction (ex. COMMIT and ROLLBACK).