SQL Fundamentals: DDL, DML, DCL, and TCL in Practice

A staff-engineer guide to SQL statement categories and how CRUD operations actually behave against a production relational database.

August 14, 2026

backend-engineersqlddldmlcrudmysql

SQL Fundamentals

Every backend engineer eventually has to reason about what actually happens when a SELECT, INSERT, or ALTER TABLE statement runs against a live database. This guide covers the four SQL statement categories — DDL, DML, DCL, TCL — not as a syntax reference, but as a map of how they behave under real production conditions: locking, replication lag, schema migrations, and the operational blast radius of getting them wrong.

We use MySQL/InnoDB syntax as the primary dialect (matching the roadmap's MySQL documentation references) and call out PostgreSQL differences where they matter for portability.

1. The Four Categories of SQL

SQL statements are grouped by what they act on and who is allowed to run them. This taxonomy matters operationally — DDL and DML have fundamentally different transactional and locking behavior in most databases.

Category	Purpose	Example statements	Transactional?
DDL	Defines and modifies schema structure	`CREATE`, `ALTER`, `DROP`, `TRUNCATE`, `RENAME`	Auto-commits in MySQL/InnoDB; transactional in PostgreSQL
DML	Reads and writes row data	`SELECT`, `INSERT`, `UPDATE`, `DELETE`, `MERGE`	Yes, always
DCL	Grants or revokes access	`GRANT`, `REVOKE`	Auto-commits in most databases
TCL	Controls transaction boundaries	`BEGIN`, `COMMIT`, `ROLLBACK`, `SAVEPOINT`	Governs the other categories

⚠️

MySQL trap: In MySQL/InnoDB, DDL statements cause an implicit commit of any open transaction. If you run ALTER TABLE in the middle of a transaction, everything before it is committed immediately — you cannot roll it back. PostgreSQL, by contrast, supports transactional DDL: you can wrap a CREATE TABLE and a DROP TABLE in the same transaction and roll both back. This is one of the sharpest dialect differences engineers get bitten by when porting migrations between the two.

2. DDL: Data Definition Language

DDL statements define the shape of your data. They are the statements your migration tool (Flyway, Liquibase, Django migrations) generates.

CREATE TABLE

sql

CREATE TABLE orders (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id     BIGINT UNSIGNED NOT NULL,
    status          ENUM('PENDING', 'CONFIRMED', 'SHIPPED', 'CANCELLED') NOT NULL DEFAULT 'PENDING',
    total_cents     BIGINT NOT NULL CHECK (total_cents >= 0),
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id) REFERENCES customers (id)
        ON DELETE RESTRICT,
 
    INDEX idx_orders_customer_status (customer_id, status)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

ALTER TABLE — the production-critical one

ALTER TABLE is the statement that causes the most production incidents, because on large tables it can lock reads/writes or rebuild the entire table.

sql

-- Adding a nullable column: cheap, metadata-only in MySQL 8+ (instant DDL)
ALTER TABLE orders ADD COLUMN notes VARCHAR(500) NULL;
 
-- Adding a NOT NULL column without a default on a huge table: dangerous
-- Requires a full table rewrite pre-8.0, and even with instant DDL
-- support, backfilling existing rows still needs a batched UPDATE.
ALTER TABLE orders ADD COLUMN priority TINYINT NOT NULL DEFAULT 0;
 
-- Adding an index: use ALGORITHM=INPLACE to avoid a full table copy
ALTER TABLE orders
    ADD INDEX idx_orders_created_at (created_at),
    ALGORITHM = INPLACE, LOCK = NONE;

🚨

Production incident pattern: Running a blocking ALTER TABLE on a multi-million-row table during peak traffic is one of the most common causes of self-inflicted outages. MySQL 8.0's instant DDL (ALGORITHM=INSTANT) covers adding/dropping columns and some index operations without a table rebuild, but not all changes qualify (e.g., changing a column's data type usually still requires a copy). Always check ALGORITHM support for your exact change, and for genuinely blocking operations use an online schema change tool like gh-ost or pt-online-schema-change, or run it during a maintenance window.

DROP vs TRUNCATE vs DELETE

This trio is a classic interview trap because all three "remove data" but behave completely differently.

Statement	Category	Removes	Rolls back?	Resets AUTO_INCREMENT	Fires triggers	Speed
`DELETE FROM t`	DML	Rows (with `WHERE`, selective)	Yes (in a transaction)	No	Yes	Slow (row-by-row logging)
`TRUNCATE TABLE t`	DDL	All rows	No (auto-commits)	Yes	No	Fast (deallocates pages)
`DROP TABLE t`	DDL	Table + data + structure	No (auto-commits)	N/A (table gone)	No	Fast

sql

-- Selective removal, transactional, slow on large row counts
DELETE FROM sessions WHERE expires_at < NOW();
 
-- Wipe the whole table instantly, cannot be rolled back, resets identity
TRUNCATE TABLE audit_log_staging;
 
-- Remove the table entirely — schema and data both gone
DROP TABLE IF EXISTS legacy_orders;

✅

Prefer DELETE ... WHERE in batches (LIMIT 1000 loops) for large selective cleanups in production — it avoids long-held locks and keeps replication lag bounded. Reserve TRUNCATE for tables you fully own and want emptied unconditionally, such as staging tables in an ETL pipeline.

3. DML: Data Manipulation Language

DML is what your application actually runs on every request. This is where CRUD lives.

CREATE → INSERT

sql

-- Single-row insert
INSERT INTO orders (customer_id, status, total_cents)
VALUES (42, 'PENDING', 259900);
 
-- Multi-row insert — one round trip, far more efficient than N single inserts
INSERT INTO order_items (order_id, product_id, quantity, unit_price_cents)
VALUES
    (1001, 55, 2, 45000),
    (1001, 61, 1, 12000),
    (1001, 73, 3, 8000);
 
-- Upsert (MySQL dialect)
INSERT INTO inventory (product_id, warehouse_id, quantity)
VALUES (55, 1, 100)
ON DUPLICATE KEY UPDATE quantity = quantity + VALUES(quantity);
 
-- Upsert (PostgreSQL dialect — different syntax, same intent)
-- INSERT INTO inventory (product_id, warehouse_id, quantity)
-- VALUES (55, 1, 100)
-- ON CONFLICT (product_id, warehouse_id)
-- DO UPDATE SET quantity = inventory.quantity + EXCLUDED.quantity;

READ → SELECT

sql

SELECT o.id, o.status, o.total_cents, c.email
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = 'CONFIRMED'
  AND o.created_at >= '2026-08-01'
ORDER BY o.created_at DESC
LIMIT 50;

UPDATE → in-place mutation

sql

-- Always scope UPDATE with a WHERE clause and, ideally, an indexed predicate
UPDATE orders
SET status = 'SHIPPED', updated_at = NOW()
WHERE id = 1001
  AND status = 'CONFIRMED';   -- guards against double-processing races
 
-- Check affected rows to detect no-op / already-processed cases
-- (application code checks the driver's "rows affected" count)

🚨

Running UPDATE orders SET status = 'CANCELLED'; with no WHERE clause is the single most common catastrophic SQL mistake in production. Most teams enforce safe update mode (sql_safe_updates in MySQL, or a linter/CI check) that refuses UPDATE/DELETE statements without a WHERE or LIMIT clause. Treat this as a mandatory guardrail, not an optional habit.

DELETE → row removal

sql

DELETE FROM order_items
WHERE order_id = 1001;
 
-- Batched delete pattern for large tables — bounds lock duration and
-- replica lag by committing in small chunks
-- (pseudocode loop around this statement, executed repeatedly until 0 rows affected)
DELETE FROM sessions
WHERE expires_at < NOW()
LIMIT 1000;

How CRUD maps to real database behavior

💡

Why this matters for latency: A COMMIT is only as fast as the storage engine's log flush (fsync), not the eventual write of data pages to disk. This is why innodb_flush_log_at_trx_commit is one of the most consequential tuning knobs in MySQL — set to 1 (default) for full ACID durability on every commit, or 2 for a small durability/performance trade-off where you risk losing the last second of commits on an OS crash.

4. DCL: Data Control Language

DCL governs who can do what. In backend engineering this shows up as application database users, read replicas with restricted grants, and least-privilege service accounts.

sql

-- Create a scoped service account for an application
CREATE USER 'order_service'@'%' IDENTIFIED BY 'a-strong-secret';
 
-- Grant only what the service needs — no DDL, no other schemas
GRANT SELECT, INSERT, UPDATE, DELETE ON ecommerce.orders TO 'order_service'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON ecommerce.order_items TO 'order_service'@'%';
 
-- A read-only analytics account
GRANT SELECT ON ecommerce.* TO 'analytics_reader'@'%';
 
-- Revoke access when a service is decommissioned
REVOKE ALL PRIVILEGES ON ecommerce.* FROM 'legacy_service'@'%';
 
FLUSH PRIVILEGES;

Principle	Practice
Least privilege	Application accounts get DML only, never `DROP`/`ALTER`/`GRANT`
Separate migration user	A distinct account with DDL rights runs migrations in CI/CD, not the app's runtime user
Per-service accounts	Each microservice gets its own credentials scoped to its own tables/schema
No shared superuser	`root`/`postgres` credentials never ship in application config

⚠️

A surprisingly common production security gap: application service accounts granted ALL PRIVILEGES "to avoid permission issues during development" and never tightened before shipping. Audit SHOW GRANTS FOR 'user'@'host'; periodically — an over-privileged application account turns a SQL injection bug into a full data-loss incident instead of a contained one.

5. TCL: Transaction Control Language

TCL statements group DML into atomic units. We cover isolation levels and locking in depth in the Transactions and Concurrency guide — here we cover the statement mechanics.

sql

START TRANSACTION;
 
UPDATE accounts SET balance = balance - 5000 WHERE id = 1;
UPDATE accounts SET balance = balance + 5000 WHERE id = 2;
 
-- Verify the invariant before committing
SELECT balance FROM accounts WHERE id = 1;
 
COMMIT;

SAVEPOINT for partial rollback

sql

START TRANSACTION;
 
INSERT INTO orders (customer_id, status, total_cents) VALUES (42, 'PENDING', 100000);
SAVEPOINT before_items;
 
INSERT INTO order_items (order_id, product_id, quantity, unit_price_cents)
VALUES (LAST_INSERT_ID(), 999, 1, 100000);  -- suppose this fails a business rule
 
-- Roll back only the item insert, keep the order
ROLLBACK TO SAVEPOINT before_items;
 
COMMIT;  -- order is persisted, the failed item insert is not

Statement	Effect
`START TRANSACTION` / `BEGIN`	Opens an explicit transaction
`COMMIT`	Makes all changes in the transaction durable and visible
`ROLLBACK`	Discards all changes since the transaction started
`SAVEPOINT name`	Marks a point to roll back to without discarding the whole transaction
`ROLLBACK TO SAVEPOINT name`	Undoes changes after the savepoint, transaction stays open
`RELEASE SAVEPOINT name`	Discards a savepoint without rolling back

✅

Most ORMs (Hibernate, Spring Data JPA, Sequelize) manage BEGIN/COMMIT/ROLLBACK for you around a @Transactional boundary. Understanding the raw TCL statements still matters — it's what lets you correctly reason about nested @Transactional propagation, why an exception inside a transactional method triggers a rollback, and why calling a @Transactional method from another method in the same class silently skips the proxy and doesn't start a new transaction.

6. Query Execution Order vs Write Order

A subtlety that trips up even experienced engineers: SQL is declarative, and the order you write clauses in a SELECT is not the order they execute in.

💡

This is why you can't reference a SELECT-defined alias in a WHERE clause (WHERE executes before SELECT), but you can reference it in ORDER BY (which executes after). It also explains why WHERE filters rows before grouping while HAVING filters groups after aggregation — using the wrong one is a frequent source of "why is my query returning wrong counts" bugs.

7. NULL Semantics

NULL is not a value — it represents the absence of a value, and it breaks ordinary boolean logic in ways that cause real bugs.

sql

SELECT * FROM customers WHERE referred_by = NULL;   -- returns ZERO rows, always
SELECT * FROM customers WHERE referred_by IS NULL;  -- correct way to check
 
-- NULL in aggregates is silently excluded
SELECT AVG(discount_pct) FROM orders;  -- ignores NULL rows, not treated as 0
 
-- NULL in comparisons produces UNKNOWN, not TRUE or FALSE
SELECT 1 = NULL;        -- NULL (not FALSE)
SELECT NULL = NULL;     -- NULL (not TRUE)
SELECT NULL <=> NULL;   -- 1 (MySQL's null-safe equality operator)

Operator	Behavior with NULL
`= NULL` / `<> NULL`	Always evaluates to `UNKNOWN` — never matches
`IS NULL` / `IS NOT NULL`	Correct way to test for NULL
`<=>` (MySQL) / `IS NOT DISTINCT FROM` (PostgreSQL)	Null-safe equality — `NULL <=> NULL` is true
`COALESCE(a, b, c)`	Returns first non-NULL argument
Aggregate functions (`SUM`, `AVG`, `COUNT(col)`)	Ignore NULLs (except `COUNT(*)`)

⚠️

COUNT(*) counts all rows including those with NULL columns; COUNT(column_name) counts only rows where that column is non-NULL. Mixing these up is a frequent source of subtly wrong analytics dashboards.

Key takeaways

DDL in MySQL/InnoDB auto-commits and can implicitly close your open transaction — never mix schema changes into a business transaction.
DELETE, TRUNCATE, and DROP all "remove data" but differ completely in rollback behavior, speed, and side effects — know which one you actually need.
Large ALTER TABLE operations need a plan: check instant-DDL eligibility, use ALGORITHM=INPLACE, or reach for gh-ost/pt-online-schema-change before touching a hot production table.
Multi-row INSERT statements dramatically reduce round trips compared to looping single-row inserts from application code.
Always scope UPDATE/DELETE with a WHERE clause; enable safe-update mode in every environment, not just production.
NULL breaks standard equality (= NULL never matches) — use IS NULL/IS NOT NULL and be deliberate about how aggregates treat NULL.
Least-privilege DCL grants contain the blast radius of application bugs and SQL injection — never run production traffic through a superuser account.
Understand SQL's logical execution order (FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY) to reason correctly about aliasing and filtering bugs.

Interview Questions

What are the four categories of SQL statements, and how does each differ in transactional behavior?
What is the difference between DELETE, TRUNCATE, and DROP? When would you use each?
Why does MySQL implicitly commit an open transaction when a DDL statement runs, and how does PostgreSQL differ?
How would you safely add a NOT NULL column with a default value to a 100-million-row production table?
What's the difference between WHERE and HAVING? Why can't you reference a SELECT alias in WHERE?
Explain how NULL behaves in comparisons and aggregate functions. What bugs does this commonly cause?
What is the purpose of SAVEPOINT, and how does it differ from a full ROLLBACK?
Why should application database accounts avoid GRANT ALL PRIVILEGES?
What does ON DUPLICATE KEY UPDATE do, and how is it different from PostgreSQL's ON CONFLICT DO UPDATE?
How does a COMMIT achieve durability at the storage engine level? What is the role of the redo/WAL log?
Why is looping single-row INSERT statements from application code an anti-pattern? What would you do instead?
What's the difference between COUNT(*) and COUNT(column_name)?
How would you enforce that no engineer accidentally runs an UPDATE without a WHERE clause in production?

Java Concurrency: Threads, ExecutorService, Futures, and Synchronization

Schema Design and Normalization for Backend Systems