SQL or Structured Query Language can be classified into various types of statements. While there are more advanced ways of running queries, millions of people still write handcrafted codes to run SQL queries, to create and manage SQL databases. There are four types of statements or language codes in SQL: DDL, DML, DCL and TCL.
DDL is an acronym for data definition language while DML is data manipulation language. Although not a subject we are discussing here but you should know that DCL is data control language and TCL is transaction control.
What are DDL and DML?
DDL or data definition language is any statement that would be used to define and create the database, including its schema or structure. DML or data manipulation language is any statement that would be used to change, manage or manipulate the data that is already within the structure or schema of the database.
Difference Between DDL and DML
There are many differences between DDL and DML. The primary difference between DDL and DML is obviously the purpose. Beyond that, you cannot use the ‘where’ clause with DDL or data definition language statements but it is usable with DML or data manipulation language statements. DDL statements cannot be rolled back as they are auto committed. DML statements are not auto commit and they can always be rolled back. DDL statements are usually faster to execute as they don’t rollback the table space or copy the original content. DML is a tad slower since it makes changes.
Examples of DDL and DML
Common DDL statements are: Create, Alter, Drop, Truncate, Comment and Rename. Using the codes you can create objects, change structures, delete objects, remove records from a table, add comments to the dictionary and rename objects in databases.
Common DML statements are: Select, Insert, Update, Delete, Merge, Call, Explain Plan and Lock Table among others. Using these codes you can retrieve data, insert new data, update original data, delete records, concatenate two sets of data or tables, call on a Java subprogram or SQL code, determine the access path and control concurrency.
Just to give you an idea, some data control language or DCL examples are Grant and Revoke while TCL or transaction control statements are Commit, Savepoint, Rollback and Set Transaction among others.
The exact scope of every command will obviously depend on the type of database you have and the kind of database management system you are using.
