DDL vs. DML – Definition and Uses Explained



What is DDL vs DML, and why should you care? First, let’s start with a reality check. Understanding the difference between the acronyms DDL vs DML is actually not that critical even if you are a working database programmer.

Basically, it’s a bit of semantics that won’t help you do your job any better. Most likely, you will never use these terms when working on an actual project.

But, the difference between DDL and DML could be good to know. Read on.

What Is DDL vs. DML Used For?

First, before we get into why you shouldn’t care too much about DDL and DML (but we’re going to explain it to you anyway), let’s just do a quick overview of our subject matter.

Before we continue, know this: We are talking about databases here.

We are not talking about car models, such as the BMW M series vs. a Mercedes G series.

We are also not talking about Internet protocols, such as DSL, HTML, or XML.

We are talking about databases, and most likely, an SQL database. SQL stands for Structured Query Language, and DDL vs. DML relates to some of the functions used in SQL.

Sure, you could potentially apply DDL and DML to other types of databases, but we will focus on SQL here.

A Quick Overview of SQL

Why SQL? Because it is one of the most common, standard types of databases available today.

If you are new to database programming or don’t have much experience with it at all, you may not know much about SQL. This section is for you, otherwise, you can skip ahead.

SQL stands for Structured Query Language, and it is a standard language used for managing relational databases. Using SQL, you can input, manipulate, and retrieve information from an SQL database.

SQL can be pronounced two ways, either as a word like “sequel” or as the letters S, Q, and L.

Not all databases use SQL. However, it is one of the most popular types of relational databases out there. Chances are if you are visiting an interactive website, it is running an SQL database on the backend. SQL databases are pretty much the standard used all over the Internet to publish and retrieve web pages.

Popular content management systems (CMS systems) such as WordPress, Drupal, and Joomla all require an SQL database to run.

Because SQL is an open standard, anyone can use it to make a database program. For this reason, you can use a variety of database servers that are based on SQL.

The most popular one is MySQL, which is an open source, a free database server that is used to power the aforementioned content management systems such as WordPress and Drupal.

(MySQL’s original official pronunciation was my-ess-cue-ell, but many people say it like “my sequel.”)

PostreSQL (pronounced post-gres-cue-el) is another free open source database that can often be interchanged for MySQL. It is supposed to be a bit more powerful than MySQL, but MySQL caught on first and was deemed easier.

Other commercial versions of SQL database servers are Microsoft SQL Server and Oracle.

Each type of database software uses basic SQL commands such as CREATE, ALTER, INPUT, and SELECT, but they may also have some advanced functions that differ slightly from the other types of SQL database servers.

It is these commands that we are talking about when we discuss DDL vs. DML.

Why Understanding DDL vs. DML Is Not That Important for the Average Programmer

You could spend your life creating SQL queries and not know that such a thing as DDL vs DML is even an issue.

As technology expert Jim Dennis writes on Quora:

“The terms are somewhat obscure and not often used in practice. They make the kinds of distinctions that are relevant to the standards maintenance process rather than real-world usage.”

In other words, you can work in a technology office for years building the most extensive database applications, and, most likely, no-one, we repeat, no-one is going to bring up DDL vs. DML whatsoever. If they do, it might just be to pick at you and test your knowledge.

So, if you are a database programmer and feeling a bit sheepish because some marketing guy came over to you and asked you if you knew what DDL was, don’t feel bad.

You are not alone, and that marketing guy is still just a marketing guy.

He just wanted to rib you. He probably saw a reference to this obscure acronym on the Internet, and basically “name dropped” it on you as a test.

But the distinction between DDL vs. DML is simply not important to your day to day job as a programmer, period.

Who Should Understand the Difference Between DDL and DML?

The main people who should know what DDL vs DML is are the people who are working on maintaining, updating, and creating the language that runs a database. For Oracle, which uses their own version of SQL, that language is SQL.

Therefore, if you are part of a standards committee helping to maintain SQL, then you probably should know the difference between DDL vs DML.

If you are trying to create a new database language from scratch, or make your own version of SQL, knowing about DDL and DML might be helpful.

And, if you want to shut up your marketing friend the next time he harasses you with pop acronym quizzes, then maybe you want to know.

And, if you want to know just out of curiosity, read on.

What is DDL vs. DML?

Finally! Let us get to the meat of the article and look at what exactly these vague acronyms actually mean. The actual concepts are not that difficult, and we will also explain with some code examples, so you can see the difference in action.

Once again, we will be focusing on SQL here.

DDL Stands for Data Definition Language

Data definition language or DDL basically refers to any statement that deals with the structure that houses the data. This would include words that create, delete, or manipulate database tables, fields, indexes, functions, and the like.

These commands in SQL include CREATE, ALTER, DROP, TRUNCATE, COMMENT, and RENAME.

In SQL, DDL commands would include examples such as:

CREATE TABLE Contacts (     ContactID int,     FirstName varchar(255),    LastName varchar(255),       StreetAddress varchar(255),     City varchar(255),     State varchar(2)  );

Any time you set up a new database, you are using data definition language to do it.

DDL Stands for Data Manipulation Language

DDL, aka data manipulation language, is any command that manipulates the actual data in the database. These include commands that add more data to the records, modify the records, or delete items from a database.

In SQL, these commands include SELECT, INSERT, UPDATE, DELETE, MERGE, and CALL.

Here are some example SQL statements that represent data manipulation language or DDL:

SELECT FirstName, LastName, ZipCode FROM Customer;

This would pull up a list from the table Customer that includes the first name, last name, and zip code of each customer.

Here is a variation of that statement:

SELECT FirstName, LastName, ZipCode FROM Customer ORDER BY ZipCode;

This version would output the same list, but this time it would be ordered by ZipCode in ascending order.

The default sort for ORDER BY is ascending (low to high or a to z). If you want to reverse the sort, you can use this statement:

SELECT FirstName, LastName, ZipCode FROM Customer ORDER BY ZipCode DESC;

This would sort the zip codes in the reverse order, from high to low.

Other Database Acronyms You Might Want to Know

If you really want to shut up your marketing friend who asked you about DDL and DML, ask them if they know what DCL and TCL stand for.

DCL Stands for Data Control Language

Data control language (DCL) is all about access control for your database. In SQL, commands like GRANT and REVOKE are examples of commands you would use for data control language.

TCL Stands for Transaction Control Language

Transaction control language (TCL) refers to any commands used to initiate or rollback a transaction. Example SQL transaction commands include COMMIT, ROLLBACK, SAVEPOINT, and SET TRANSACTION.




Leave a Comment