SQL looks like one language, but under the hood it splits into five families of commands, each with a distinct job. Learn the split and the whole language suddenly makes sense: you always know which command you need and why it behaves the way it does.
People call SQL a single language, yet the standard groups its statements into sublanguages by purpose. The five you will meet every day are DDL (define structure), DML (change data), DQL (read data), DCL (control access), and TCL (manage transactions). Some textbooks fold DQL into DML and count only four, but keeping SELECT in its own bucket makes the mental model cleaner. This guide walks all five in the order you tend to use them, with real commands, runnable examples, and a master table you can pin above your desk. The concepts are portable across engines; where MySQL and PostgreSQL disagree, the difference is called out inline.
What this guide covers
1. The five sublanguages at a glance
Every SQL statement you will ever write belongs to one of five categories, sorted by what it does to the database. Before we go deep on each, here is the master reference. Keep this table close: once you can place a command in the right row, most of its behaviour follows automatically, including whether it commits on its own.
| Category | Purpose | Key commands | Auto commit? |
|---|---|---|---|
| DDL (Data Definition) | Define and change the shape of the database | CREATE, ALTER, DROP, TRUNCATE, RENAME | Yes (implicit commit in most engines) |
| DML (Data Manipulation) | Add, edit, and remove rows of data | INSERT, UPDATE, DELETE, MERGE | No (needs COMMIT) |
| DQL (Data Query) | Read and return data | SELECT | N/A (reads only) |
| DCL (Data Control) | Grant and revoke permissions | GRANT, REVOKE | Yes (usually auto committed) |
| TCL (Transaction Control) | Commit or undo groups of DML | COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION | They are the commit controls |
Four or five? Some references list only four sublanguages by folding SELECT into DML. Others split out DQL so that reading data has its own name. Both views are correct; this guide keeps DQL separate because SELECT behaves very differently from the write commands and deserves its own mental slot. The wider concept is described well on Wikipedia's SQL page.
2. DDL: defining structure
Data Definition Language is how you build and reshape the database itself: databases, tables, columns, indexes, and views. DDL does not touch the rows inside a table; it touches the container that holds them. The core commands are CREATE to make a new object, ALTER to change one, DROP to delete one entirely, TRUNCATE to empty a table instantly, and RENAME to change a name. For a fuller tour, see the DDL commands guide.
Here is DDL doing its whole job on one small schema: create a table, add an index, alter a column, and drop what you no longer need.
-- CREATE: define a new table and its columns
CREATE TABLE customers (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
email VARCHAR(150) UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- ALTER: add a column after the table exists
ALTER TABLE customers ADD country CHAR(2);
-- TRUNCATE: empty every row fast, keep the structure
TRUNCATE TABLE customers;
-- DROP: remove the object and all its data
DROP TABLE IF EXISTS customers;The key idea to carry forward: DROP destroys the object, TRUNCATE keeps the empty shell but wipes the contents in one fast operation, and ALTER edits the definition in place. Unlike the DML commands you will meet next, DDL statements commit themselves in most databases, which is why they demand extra care.
3. DML: changing data
Data Manipulation Language works on the rows inside your tables. Where DDL builds the shelves, DML puts things on them, rearranges them, and takes them off. The three you use constantly are INSERT to add rows, UPDATE to change existing rows, and DELETE to remove them. MERGE (sometimes called upsert) combines insert and update in one statement. The full walkthrough lives in the DML commands guide.
-- INSERT: add new rows
INSERT INTO customers (name, email, country)
VALUES ('Sara Ahmed', 'sara@example.com', 'AE');
-- UPDATE: change rows that match the WHERE clause
UPDATE customers SET country = 'SA' WHERE id = 42;
-- DELETE: remove rows that match the WHERE clause
DELETE FROM customers WHERE id = 42;Never forget the WHERE. An UPDATE or DELETE with no WHERE clause hits every row in the table. Run SELECT * FROM customers WHERE id = 42; first, confirm it returns exactly the rows you mean to touch, then swap in the write. Because DML does not commit on its own, you can wrap it in a transaction and roll back if something looks wrong, which is the safety net DDL does not give you.
The critical difference from DDL: DML changes are not permanent until you commit them. Inside a transaction, an INSERT or DELETE can be rolled back as if it never happened. That is exactly what TCL, covered below, is for.
4. DQL: reading data
Data Query Language is the smallest family by command count and the largest by usage: it is essentially the single SELECT statement and its clauses. SELECT reads data and returns a result set without changing anything on disk. Because it only reads, it has no commit behaviour to worry about. The complete clause order is covered in the DQL commands guide.
SELECT country, COUNT(*) AS customers
FROM customers
WHERE created_at >= '2025-01-01'
GROUP BY country
HAVING COUNT(*) > 10
ORDER BY customers DESC
LIMIT 5;A single SELECT can filter with WHERE, fold rows into groups with GROUP BY, filter those groups with HAVING, sort with ORDER BY, and cap the output with LIMIT. The database evaluates the clauses in a specific order that differs from how you write them, which is why understanding SELECT deeply pays off more than any other command. It is the one you will type thousands of times.
Why DQL gets its own name. SELECT never writes, never locks rows for modification the way an UPDATE does, and never needs a COMMIT. Treating it as a separate sublanguage keeps that read only nature front of mind, which matters when you reason about performance and concurrency.
5. DCL: controlling access
Data Control Language governs who is allowed to do what. It has two commands: GRANT gives a user or role a privilege, and REVOKE takes it away. This is the backbone of database security, so a least privilege habit here prevents a lot of trouble later. See the SQL security guide for how DCL fits into a wider hardening plan.
-- GRANT: give a user specific privileges
GRANT SELECT, INSERT ON customers TO 'analyst'@'localhost';
-- Grant read access on every table in a schema
GRANT SELECT ON shop.* TO 'reporting'@'%';
-- REVOKE: take a privilege back
REVOKE INSERT ON customers FROM 'analyst'@'localhost';Give each account only the privileges its job requires. A reporting user rarely needs more than SELECT; an application account might need SELECT, INSERT, UPDATE, and DELETE but almost never DROP. Like DDL, DCL statements are normally committed as soon as they run, so a REVOKE takes effect right away.
6. TCL: managing transactions
Transaction Control Language decides whether your DML changes stick or vanish. A transaction is a group of statements that must all succeed together or all fail together. COMMIT makes the whole group permanent, ROLLBACK undoes it, and SAVEPOINT sets a checkpoint you can roll back to without discarding everything. This is what makes operations like moving money between accounts safe.
BEGIN; -- or START TRANSACTION
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
SAVEPOINT after_debit;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- Something wrong with the credit? undo just that step:
-- ROLLBACK TO after_debit;
COMMIT; -- make both updates permanentIf the second UPDATE fails, a ROLLBACK restores both balances to where they started, so money is never lost or duplicated. Without a transaction, a crash between the two updates would leave one account debited and the other never credited. TCL only governs DML, which is the key link between these two families: the writes from section 3 stay provisional until a TCL command finalises them.
The ACID payoff. Transactions give you atomicity (all or nothing), consistency, isolation, and durability. TCL is how you drive those guarantees by hand. Many client libraries wrap each statement in an implicit commit unless you open a transaction yourself, so learn your engine's default before relying on rollback.
7. Auto commit: where TRUNCATE and DDL sit
Here is the trap that catches people who think every command can be undone. DDL statements such as CREATE, ALTER, DROP, and TRUNCATE carry an implicit commit in most engines. The moment they run, any open transaction is committed and the change is permanent. You cannot wrap a DROP TABLE in a transaction and expect ROLLBACK to bring it back on a typical MySQL setup.
| Statement | Family | Filter with WHERE? | Can ROLLBACK undo it? |
|---|---|---|---|
DELETE | DML | Yes | Yes, inside a transaction |
TRUNCATE | DDL | No | No (auto commits in MySQL) |
DROP | DDL | No | No (auto commits in MySQL) |
UPDATE | DML | Yes | Yes, inside a transaction |
TRUNCATE is DDL, not DML. It looks like a bulk DELETE, but it is classified as DDL: it deallocates the table's data in one operation, ignores WHERE, resets identity counters, and in MySQL auto commits so it cannot be rolled back. PostgreSQL is the notable exception: most of its DDL, including TRUNCATE, is transactional and can sit inside BEGIN ... ROLLBACK. When you need to clear rows you might want back, use DELETE inside a transaction, not TRUNCATE.
The practical rule: treat DDL and DCL as one way doors unless you are on PostgreSQL and have confirmed the statement is transactional. Treat DML as reversible, but only while the transaction stays open. Once you COMMIT, even DML is permanent.
8. How often you use each category
The five families are not equally busy. In everyday application and analytics work, reading data dominates, writing is frequent, and structural or permission changes are occasional. This chart is a representative picture of where your SQL keystrokes actually land over a typical working week.
The takeaway: invest most of your learning in SELECT and the DML write commands, because together they are the vast majority of the job. DDL matters intensely but occasionally, usually at project setup or during a migration. DCL is rare for most developers and often handled by a database administrator, but knowing it exists keeps your security thinking sharp. For a compact lookup of the exact syntax across all five families, keep the SQL cheat sheet open in a second tab.
9. Putting it together
The five sublanguages are less a set of rules to memorise and more a map that tells you what any command will do before you run it. When you meet a new statement, ask which family it belongs to and the important behaviour follows.
- DDL shapes the database and commits itself. Handle with care, because rollback usually will not save you.
- DML changes rows but stays provisional until a transaction commits, so it is your reversible workhorse.
- DQL is just
SELECT: it reads, never writes, and never commits. - DCL hands out and withdraws privileges. Grant the minimum each account needs.
- TCL is the finger on the commit button that makes DML permanent or throws it away.
Learn the split once and every SQL statement you meet slots neatly into place. From here, go deeper into the writing side with the DML commands guide, sharpen your reads with the DQL commands guide, and build the whole picture into muscle memory with the SQL beginner course.