A DEEP DIVE INTO DDL'S AND DML'S

_ DDL and DML, the basics_

I keep seeing these two terms thrown around whenever people talk SQL, so here's what I've picked up about them so far.

DDL

DDL stands for Data Definition Language. Basically, these are the commands that deal with the structure of a database — the tables themselves, not what's sitting inside them. If you're creating a table, changing its columns, or getting rid of it completely, that's DDL.

Say I'm building a small table to track books at a library:

sql
CREATE TABLE library.books (
book_id VARCHAR(20) NOT NULL,
title VARCHAR(150) NULL,
author VARCHAR(100) NULL,
date_added TIMESTAMP NULL
);

That's CREATE — it builds a brand new table with the columns you tell it to have.

Now say I forgot a column and need to add it later, maybe to track whether a book is checked out:

sql
ALTER TABLE library.books ADD COLUMN is_checked_out BOOLEAN;

ALTER changes a table that already exists, instead of building a new one.

If a table isn't needed anymore, DROP removes it completely — structure and all the data in it, gone:

sql
DROP TABLE library.books;

There's also TRUNCATE, which is a bit different from DROP. It empties out every row in the table but keeps the table itself standing, so you can start filling it up again without recreating it:

sql
TRUNCATE TABLE library.books;

I mixed up DROP and TRUNCATE for a while when I was starting out — DROP kills the table, TRUNCATE just empties it.

And then there's RENAME, for when a name just isn't working anymore. Maybe books should really be called catalog:

sql
ALTER TABLE library.books RENAME TO library.catalog;

You can rename a single column the same way, like if date_added should really be added_on:

sql
ALTER TABLE library.catalog RENAME COLUMN date_added TO added_on;

So that's DDL in a nutshell — it's the commands that shape what your database looks like.

DML

DML is Data Manipulation Language, and this is where things get more familiar if you've written any SQL at all. DML doesn't touch the structure of a table — it works with the actual rows and data inside it.

The one everybody starts with is SELECT, for pulling data out:

sql
SELECT * FROM library.catalog WHERE is_checked_out = TRUE;

This grabs every column, but only for the rows where a book is currently checked out.

To put new data in, there's INSERT:

sql
INSERT INTO library.catalog (book_id, title, author)
VALUES ('BK001', 'The Alchemist', 'Paulo Coelho');

That adds one new row. Notice I didn't fill in every single column — just the ones I had values for.

To change something that's already there, UPDATE is the one to reach for. Say BK001 just got checked out:

sql
UPDATE library.catalog
SET is_checked_out = TRUE
WHERE book_id = 'BK001';

Without the WHERE clause here, every single row in the table gets updated, not just the one you meant — learned that one the hard way on a test database, thankfully.

And finally DELETE, for getting rid of rows you don't want anymore:

sql
DELETE FROM library.catalog WHERE book_id = 'BK001';

This removes just that one row and it's permanent, so it's worth double-checking the WHERE clause before running it. You can also delete based on other conditions, not just an ID — like clearing out anything added before a certain date:

sql
DELETE FROM library.catalog WHERE added_on < '2024-01-01';

ACTUAL DIFFERENCE

DDL shapes the container — the tables, the columns, whether something exists at all. DML deals with what's poured into that container — adding, changing, reading, or removing the actual rows. If you ever forget which is which, ask yourself: am I changing the table itself, or just what's inside it? That's basically the whole distinction.