What is the difference between DDL and DML commands in SQL?

Main differences between Data Definition Language (DDL) and Data Manipulation Language (DML) commands are:

  • DDL vs. DML: DDL statements are used for creating and defining the Database structure. DML statements are used for managing data within Database.
  • Sample Statements: DDL statements are CREATE, ALTER, DROP, TRUNCATE, RENAME etc. DML statements are SELECT, INSERT, DELETE, UPDATE, MERGE, CALL etc.
  • Number of Rows: DDL statements work on whole table. CREATE will a create a new table. DROP will remove the whole table. TRUNCATE will delete all records in a table. DML statements can work on one or more rows. INSERT can insert one or more rows. DELETE can remove one or more rows.
  • WHERE clause: DDL statements do not have a WHERE clause to filter the data. Most of DML statements support filtering the data by WHERE clause.
  • Commit: Changes done by a DDL statement can not be rolled back. So there is no need to issue a COMMIT or ROLLBACK command after DDL statement. We need to run COMMIT or ROLLBACK to confirm our changed after running a DML statement.
  • Transaction: Since each DDL statement is permanent, we can not run multiple DDL statements in a group like Transaction. DML statements can be run in a Transaction. Then we can COMMIT or ROLLBACK this group as a transaction. E.g. We can insert data in two tables and commit it together in a transaction.
  • Triggers: After DDL statements no triggers are fired. But after DML statements relevant triggers can be fired.
Read the full book at www.amazon.com
Posted in SQL, SQL Interview Questions

Leave a Reply

Your email address will not be published. Required fields are marked *

*