Learn the difference between DDL and DML in SQL. Visual guide covering CREATE, ALTER, DROP vs SELECT, INSERT, UPDATE, DELETE with examples and quiz.
Jump into the SQLNoir case files and put these tips to work.
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.
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
DDL commands define the structure of your database. They create, modify, and remove database objects like tables, indexes, and views.
-- 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.
DDL commands modify database structure, not data
DML commands work with the data inside your tables. They let you read, add, modify, and remove individual rows.
-- 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.
suspectsalibi = 'At the marina'suspect_id = 1Anatomy 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.
The core distinction is simple: DDL changes structure, DML changes data. But this one difference leads to several important practical implications.
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
SQL commands are actually divided into four categories, not just two. Here's the complete picture:
-- 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.
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?
-- 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 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 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.
The most important practical difference: DDL commands auto-commit, while DML commands can be rolled back. This affects how you work in production.
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.
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.
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.
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 InvestigationTest your understanding. Can you classify each SQL command correctly?
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;
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.
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.
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.
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.
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 →