DDL vs DML: Understanding SQL Command Types (With Visual Examples)

Learn the difference between DDL and DML in SQL. Visual guide covering CREATE, ALTER, DROP vs SELECT, INSERT, UPDATE, DELETE with examples and quiz.

Every SQL command falls into one of two categories: commands that define your database structure (DDL) or commands that work with the actual data (DML). Understanding this distinction is fundamental to writing effective SQL.

🎯 Quick Navigation

• Quick Answer: DDL vs DML
• What is DDL?
• What is DML?
• Key Differences
• The Complete Picture: DDL, DML, DCL, TCL
• The TRUNCATE Controversy
• Transaction Behavior
• Quiz: Test Your Knowledge
• FAQ

Quick Answer: DDL vs DML

Think of it like architecture vs interior design. DDL builds the house (tables, indexes, schemas). DML fills it with furniture (rows of data).

Category	Purpose	Commands	Affects
DDL	Define structure	CREATE, ALTER, DROP, TRUNCATE	Tables, indexes, schemas
DML	Manipulate data	SELECT, INSERT, UPDATE, DELETE	Rows within tables

DDL vs DML at a Glance

What is DDL (Data Definition Language)?

DDL commands define the structure of your database. They create, modify, and remove database objects like tables, indexes, and views.

DDL Commands:

• CREATE: Build new tables, indexes, views, schemas
• ALTER: Modify existing structures (add columns, change data types)
• DROP: Completely remove database objects
• TRUNCATE: Remove all rows AND reset the table (see the controversy section below)

Creating a Database for Crime Investigations:

-- CREATE: Build the suspects table
CREATE TABLE suspects (
    suspect_id INT PRIMARY KEY,
    name VARCHAR(100),
    last_seen DATE
);

-- ALTER: Add a column for alibis
ALTER TABLE suspects ADD COLUMN alibi TEXT;

-- DROP: Remove the table entirely
DROP TABLE suspects;

Each command changes the database structure itself, not the data inside.

🏗️CREATE TABLE

🔧ALTER TABLE

💥DROP TABLE

🗑️TRUNCATE TABLE

DDL commands modify database structure, not data

🕵️Detective's Note

Think of DDL as the architect's blueprints. Before you can investigate a crime (query data), someone has to build the police station (create the database structure).

What is DML (Data Manipulation Language)?

DML commands work with the data inside your tables. They let you read, add, modify, and remove individual rows.

DML Commands:

• SELECT: Retrieve/read data (some classify this as DQL)
• INSERT: Add new rows of data
• UPDATE: Modify existing data values
• DELETE: Remove specific rows of data

Investigating Suspects with DML:

-- INSERT: Add a suspect to the database
INSERT INTO suspects (suspect_id, name, last_seen)
VALUES (1, 'John Doe', '1986-08-14');

-- SELECT: Find suspects seen after a certain date
SELECT * FROM suspects WHERE last_seen > '1986-08-01';

-- UPDATE: Record an alibi for a suspect
UPDATE suspects SET alibi = 'Claims he was at the marina'
WHERE suspect_id = 1;

-- DELETE: Remove a cleared suspect
DELETE FROM suspects WHERE suspect_id = 1;

Each command works with rows of data, not the table structure.

UPDATEsuspects

DML command targeting a table

SETalibi = 'At the marina'

New value to assign

WHEREsuspect_id = 1

Filter to specific row

Anatomy of a DML UPDATE statement

DML commands are what you'll use most when investigating data. If you want to practice SELECT, INSERT, and UPDATE in a real database, SQLNoir's detective cases let you write real queries to crack mysteries.

DDL vs DML: Key Differences

The core distinction is simple: DDL changes structure, DML changes data. But this one difference leads to several important practical implications.

❓Are you CHANGING the database structure?

🏗️YES → Use DDL (CREATE, ALTER, DROP)

❓Are you WORKING with data inside tables?

📊YES → Use DML (SELECT, INSERT, UPDATE, DELETE)

Quick decision guide: structure changes = DDL, data changes = DML

Aspect	DDL	DML
Full Name	Data Definition Language	Data Manipulation Language
Purpose	Define database structure	Manipulate data within tables
Commands	CREATE, ALTER, DROP, TRUNCATE	SELECT, INSERT, UPDATE, DELETE
Affects	Tables, indexes, views, schemas	Rows/records within tables
Auto-Commit	Yes (changes are permanent immediately)	No (can be rolled back in transaction)
Permissions	Higher privileges required	Lower privileges (read/write access)
Frequency	Rare (schema changes)	Constant (CRUD operations)
WHERE Clause	Not applicable	Used to filter affected rows
Can Use Rollback?	No (in most databases)	Yes
Triggers Fired?	No	Yes (INSERT, UPDATE, DELETE triggers)

Complete DDL vs DML Comparison

The Complete Picture: DDL, DML, DCL, and TCL

SQL commands are actually divided into four categories, not just two. Here's the complete picture:

All Four SQL Command Types:

-- DDL: Define structure
CREATE TABLE evidence (evidence_id INT PRIMARY KEY);

-- DML: Manipulate data
INSERT INTO evidence VALUES (1);

-- DCL: Control access
GRANT SELECT ON evidence TO detective_jones;

-- TCL: Manage transactions
COMMIT;

Category	Stands For	Commands	Purpose
DDL	Data Definition Language	CREATE, ALTER, DROP, TRUNCATE	Define database structure
DML	Data Manipulation Language	SELECT, INSERT, UPDATE, DELETE	Work with data
DCL	Data Control Language	GRANT, REVOKE	Control access permissions
TCL	Transaction Control Language	COMMIT, ROLLBACK, SAVEPOINT	Manage transactions

The Four Categories of SQL Commands

Some textbooks add a fifth category: DQL (Data Query Language) to separate SELECT from other DML commands. The logic is that SELECT reads data without modifying it. In practice, most people group SELECT with DML.

The TRUNCATE Controversy: DDL or DML?

TRUNCATE is the most debated command. It removes all data from a table, which sounds like DML. But it's officially classified as DDL. Why?

TRUNCATE vs DELETE Behavior:

-- DML: DELETE can be filtered and rolled back
DELETE FROM suspects WHERE status = 'cleared';
ROLLBACK; -- Works! Data is restored

-- DDL: TRUNCATE cannot be rolled back (in most databases)
TRUNCATE TABLE suspects;
-- ROLLBACK won't work - data is gone permanently

❌DELETE (DML)

DELETE FROM suspects;

•Can filter with WHERE clause

•Fires DELETE triggers

•Can be rolled back in a transaction

•Slower on large tables (row by row)

•Maintains auto-increment values

✅TRUNCATE (DDL)

TRUNCATE TABLE suspects;

•Removes ALL rows (no WHERE)

•Does NOT fire triggers

•Cannot be rolled back (most databases)

•Instant on large tables (drops pages)

•Resets auto-increment to 1

TRUNCATE behaves like a structural operation despite removing data

The bottom line: TRUNCATE is DDL because of how it works, notwhat it does. It effectively drops and recreates the table structure internally. For a deeper dive, see our DELETE vs TRUNCATE guide.

Transaction Behavior: Why It Matters

The most important practical difference: DDL commands auto-commit, while DML commands can be rolled back. This affects how you work in production.

DML: Safe Inside Transactions

BEGIN TRANSACTION;

DELETE FROM suspects WHERE name = 'Wrong Person';
-- Oops! Deleted the wrong suspect

ROLLBACK;
-- Phew, data is restored!

DML mistakes can be undone if you're inside a transaction.

DDL: No Going Back

DROP TABLE evidence;
-- No going back now... the table is gone

-- ROLLBACK won't help here

DDL auto-commits immediately. No transaction can save you.

⚠️Case File Warning

Always back up your database before running DDL commands in production. Unlike DML mistakes that can be rolled back, a DROP TABLE is permanent. Many a detective has lost crucial evidence to an accidental DROP.

PostgreSQL exception: PostgreSQL allows DDL inside transactions, so you can rollback a DROP TABLE if you haven't committed yet. But this is the exception, not the rule. MySQL, Oracle, and SQL Server all auto-commit DDL.

Case File #003🔍

The Miami Marina Murder

●●○Intermediate

Now that you understand how DML queries work, put your SELECT and JOIN skills to the test. Investigate surveillance records, hotel check-ins, and witness statements to find the killer.

Start Investigation

Quiz: DDL or DML?

Test your understanding. Can you classify each SQL command correctly?

🔍 Command Classification Challenge

Q1.CREATE INDEX idx_name ON suspects(name);

Q2.SELECT * FROM suspects WHERE last_seen > '1986-08-01';

Q3.TRUNCATE TABLE old_cases;

Q4.GRANT SELECT ON evidence TO detective_jones;

Frequently Asked Questions

Is SELECT DDL or DML?

SELECT is DML (Data Manipulation Language). Some categorize it separately as DQL (Data Query Language) since it reads data without modifying it, but it's most commonly grouped with DML alongside INSERT, UPDATE, and DELETE.

What is the main difference between DDL and DML?

DDL (Data Definition Language) defines database STRUCTURE, such as creating, altering, and dropping tables. DML (Data Manipulation Language) works with the DATA inside those tables, including selecting, inserting, updating, and deleting rows.

Can DDL commands be rolled back?

In most databases (MySQL, Oracle, SQL Server), DDL commands auto-commit and cannot be rolled back. PostgreSQL is an exception. It allows DDL within transactions that can be rolled back. Always back up before running DDL in production.

Why is TRUNCATE considered DDL and not DML?

TRUNCATE is DDL because: (1) it cannot be rolled back in most databases, (2) it doesn't fire DELETE triggers, (3) it resets auto-increment counters, and (4) it's implemented as DROP + CREATE internally. It removes data but behaves like a structural operation.

What are the 4 types of SQL commands?

SQL commands are categorized into 4 types: DDL (CREATE, ALTER, DROP, TRUNCATE), DML (SELECT, INSERT, UPDATE, DELETE), DCL (GRANT, REVOKE for permissions), and TCL (COMMIT, ROLLBACK, SAVEPOINT for transactions).

Ready to put DML into practice?

Understanding DDL vs DML is foundational for any SQL work. Now that you've got the concepts down, the best way to cement your DML skills is through practice. SQLNoir lets you run SELECT, JOIN, and WHERE queries against real databases while solving detective mysteries.

Start Your Investigation →