DML

Data Manipulation Language (DML) is the part of SQL that lets applications query, insert, update, and delete rows stored in relational tables. While DDL (Data Definition Language) shapes the schema and DCL (Data Control Language) governs permissions, DML is the workhorse that drives everyday business logic. Because DML statements are usually wrapped in transactions, understanding their syntax, side‑effects, and performance characteristics is essential for building reliable, high‑throughput applications.

A key practical difference is that DDL statements are often auto‑committed, meaning they end any open transaction implicitly. In contrast, DML statements respect transaction boundaries, allowing you to roll back a group of changes if something goes wrong.

The Core DML Statements at a Glance

• SELECT – Retrieves rows; can be combined with WHERE, JOIN, GROUP BY, HAVING, ORDER BY, LIMIT, etc.
• INSERT – Adds new rows; supports single‑row, multi‑row, and INSERT … SELECT forms.
• UPDATE – Modifies existing rows; must be scoped with a WHERE clause to avoid touching the whole table.
• DELETE – Removes rows; also requires a WHERE clause for safety.
• MERGE (or UPSERT) – Inserts a row when it does not exist, otherwise updates it; useful for synchronization tasks.

Below each statement is explored in depth, with syntax, practical examples, and safety tips.

SELECT – The Most Versatile DML Command

SELECT is the entry point for reading data. Its flexible clause structure lets you filter, aggregate, and order results in a single statement.

SELECT [DISTINCT] column1, column2, …
FROM   table_name [AS alias]
[WHERE  condition]
[GROUP BY column1, column2, …]
[HAVING  aggregate_condition]
[ORDER BY column1 [ASC|DESC], …]
[LIMIT n [OFFSET m]];

Real‑World Example

Assume a table employees:

1. List IT staff with their salaries

   SELECT name, salary
   FROM   employees
   WHERE  department = 'IT';
   

2. Average salary per department

   SELECT department, AVG(salary) AS avg_salary
   FROM   employees
   GROUP BY department;
   

3. Paginated view (MySQL syntax)

   SELECT *
   FROM   employees
   ORDER BY emp_id
   LIMIT  10 OFFSET 20;   -- page 3, 10 rows per page
   

Best practice: Avoid SELECT * in production code. Explicitly list only the columns you need; this reduces I/O, improves cache usage, and makes execution plans more predictable.

INSERT – Adding New Records

INSERT comes in three common flavors: single‑row, multi‑row, and insert‑from‑select.

Single‑Row Insert

INSERT INTO table_name (col1, col2, col3)
VALUES (val1, val2, val3);

INSERT INTO employees (emp_id, name, department, salary)
VALUES (4, 'Dave', 'Finance', 7500);

Multi‑Row Insert

INSERT INTO employees (emp_id, name, department, salary)
VALUES
  (5, 'Eve',  'IT',      8200),
  (6, 'Frank','HR',      5800);

Insert From a Query

INSERT INTO archive_employees (emp_id, name, department, salary)
SELECT emp_id, name, department, salary
FROM   employees
WHERE  hire_date < '2020-01-01';

Performance tip: When loading large batches, disable auto‑commit (SET autocommit = 0) and commit once after the batch finishes. This reduces transaction overhead and can dramatically improve throughput.

UPDATE – Changing Existing Data

The UPDATE statement modifies one or more columns for rows that satisfy a condition.

UPDATE table_name
SET    column1 = expr1,
       column2 = expr2,
       …
[WHERE condition];

Sample Updates

1. Give every IT employee a 10 % raise

   UPDATE employees
   SET    salary = salary * 1.10
   WHERE  department = 'IT';
   

2. Move an employee to a new department and bump salary

   UPDATE employees
   SET    department = 'Marketing',
          salary = salary + 500
   WHERE  emp_id = 2;
   

Safety reminder: Always run a SELECT with the same WHERE clause before executing an UPDATE. This lets you verify the exact rows that will be affected.

DELETE – Removing Unwanted Rows

DELETE erases rows that match a predicate. Without a WHERE clause, the entire table is cleared (subject to transaction semantics).

DELETE FROM table_name
[WHERE condition];

Practical Deletions

1. Purge low‑salary employees

   DELETE FROM employees
   WHERE salary < 5000;
   

2. Empty a table efficiently (DDL alternative)

   • MySQL: TRUNCATE TABLE employees; – fast, auto‑committed, bypasses row‑level logging.
   • Standard DML: DELETE FROM employees; – respects transaction boundaries.

Anti‑mistake technique: In MySQL, add LIMIT to delete in chunks, or use a loop with ROWCOUNT in SQL Server. This prevents long‑running locks and makes rollbacks more manageable.

MERGE (UPSERT) – Synchronizing Data

MERGE (or the INSERT … ON CONFLICT pattern) lets you insert a row when it does not exist, or update it when it does. It is especially handy for ETL pipelines and cache refreshes.

Oracle‑style MERGE

MERGE INTO target_table t
USING (SELECT ? AS emp_id, ? AS name, ? AS department, ? AS salary FROM dual) s
ON (t.emp_id = s.emp_id)
WHEN MATCHED THEN
  UPDATE SET t.name = s.name,
             t.department = s.department,
             t.salary = s.salary
WHEN NOT MATCHED THEN
  INSERT (emp_id, name, department, salary)
  VALUES (s.emp_id, s.name, s.department, s.salary);

PostgreSQL UPSERT

INSERT INTO employees (emp_id, name, department, salary)
VALUES (7, 'Grace', 'IT', 8800)
ON CONFLICT (emp_id) DO UPDATE
SET    name = EXCLUDED.name,
       department = EXCLUDED.department,
       salary = EXCLUDED.salary;

When to use: If you need to keep a staging table in sync with a master table, MERGE eliminates the need for separate SELECT‑then‑INSERT/UPDATE logic, reducing race conditions.

Transactions – Keeping DML Safe

Because DML changes data, it should be executed inside a transaction to guarantee the ACID properties:

• Atomicity – All statements succeed or none do.
• Consistency – The database moves from one valid state to another.
• Isolation – Concurrent transactions do not interfere.
• Durability – Once committed, changes survive crashes.

Basic Transaction Control

START TRANSACTION;   -- or BEGIN;
-- DML statements
INSERT INTO …;
UPDATE …;
DELETE …;
COMMIT;              -- make changes permanent
-- or
ROLLBACK;            -- discard everything

Example: Simple Money Transfer

START TRANSACTION;

UPDATE accounts
SET    balance = balance - 500
WHERE  account_id = 1001
  AND  balance >= 500;   -- prevent overdraft

UPDATE accounts
SET    balance = balance + 500
WHERE  account_id = 2002;

COMMIT;

If the first UPDATE fails (e.g., insufficient funds), a ROLLBACK restores both accounts to their original balances, avoiding “money disappearing” or “money appearing out of thin air”.

Performance Optimization & Best Practices

• Leverage indexes on columns used in WHERE, JOIN, and ORDER BY. Missing indexes force full‑table scans.
• Batch writes – Use multi‑row INSERT, COPY (PostgreSQL), or LOAD DATA (MySQL) for bulk loads.
• Return only needed columns – SELECT column1, column2 beats SELECT *.
• Avoid implicit type conversion – Mismatched data types can disable index usage.
• Size transactions wisely – Too large locks resources; too small increases commit overhead. A sweet spot is often a few thousand rows per transaction.
• Parameterize queries – Prevent SQL injection and enable plan reuse (PreparedStatement in Java, sp_executesql in SQL Server).
• Monitor execution plans – Tools like EXPLAIN, EXPLAIN ANALYZE, or database‑specific visualizers reveal hidden bottlenecks.

Closing Thoughts

Mastering DML means more than memorizing syntax; it requires an awareness of transaction scope, index interaction, and security considerations. By consistently applying the patterns outlined above—precise WHERE clauses, disciplined transaction handling, and thoughtful performance tuning—you can write SQL that is both robust and efficient. Whether you are building a tiny CRUD microservice or a high‑volume data‑warehouse pipeline, DML remains the primary conduit through which business logic meets persistent storage. Use it wisely, and your applications will enjoy reliable data integrity and scalable performance.