Difference between DML and DDL statements in SQL

Background

SQL as we all know stands for structured Query language. SQL Statements can be categorized as -

1. DDL (Data definition language)
2. DML (Data manipulation language) 
3. DCL (Data Control Language)
4.  TCL (Transaction Control)

In this post we will see what these statements are and what are the differences between the. This post is written in the context of PL/SQL which is scripting language for oracle DB. So some of the statements may be different or just related to oracle DB.

Definitions

• DDL (Data definition language): As the name suggests these statements  are used define database structure or schema.
  
  Example -
  
  
  1. CREATE - to create objects in the database
  2. ALTER - alters the structure of the database
  3. DROP - delete objects from the database
  4. TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
  5. COMMENT - add comments to the data dictionary
  6. RENAME - rename an object.


• DML (Data manipulation language): These statements are used to change or alter data with the database or schema.
  
  Example -
  
  
  1. SELECT - retrieve data from the a database
  2. INSERT - insert data into a table
  3. UPDATE - updates existing data within a table
  4. DELETE - deletes all records from a table, the space for the records remain
  5. MERGE - UPSERT operation (insert or update)
  6. CALL - call a PL/SQL or Java subprogram
  7. EXPLAIN PLAN - explain access path to data
  8. LOCK TABLE - control concurrency


• DCL (Data Control Language) : These statements are use to control access or priveledges.
  
  Example-
  
  
  1. GRANT - gives user's access privileges to database
  2. REVOKE - withdraw access privileges given with the GRANT command


• TCL (Transaction Control) : These statements control transactions like Commit and rollback.
  
  Example-
  
  
  1. COMMIT - save work done
  2. SAVEPOINT - identify a point in a transaction to which you can later roll back
  3. ROLLBACK - restore database to original since the last COMMIT
  4. SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use
   
  

Differences

1. As the name and definition suggests DDL statements are for defining the schema or database where as DML statements are of manipulating the database data.
2. We can use where clause with DML statements but not with DDL statements.
3. DDL statements are auto commit meaning they cannot be rolled back where as DML statements can be rolled back.
4. During the execution of DDL command. DDL command would not copy the actual content to rollback tablespace, hence it is fast compared to DML command.

Difference between delete and truncate command ? - Interview Q

This is a very famous database interview question. If you know these commands truncate and delete - both are used to clean the data in the table. 

• Notice delete is a DML statement where as truncate is a DDL statement. 
• Hence with truncate you cannot use where clause and rollback is not possible. Delete will remove all row entries which will satisfy the where clause. 
• Also truncate is faster than delete as no entries are logged into rollback space. 
• Lastly delete will trigger all the DML triggers associated with delete which will not be the case with truncate.
• Delete will lock each row for deletion whereas truncate locks the entire table.

Note : drop statement will drop the entire table including all of it's row entries.