DELETE

Deletes rows that match the condition.

DELETE FROM table_name WHERE condition;

Deletes all rows in the table (WHERE omitted).

DELETE FROM table_name;

Syntax	Description
DELETE FROM table_name WHERE condition	Deletes rows that match the condition. Returns the number of rows deleted.
DELETE FROM table_name	Deletes all rows in the table. This is the behavior when WHERE is omitted.
DELETE ... LIMIT count	A MySQL extension. Limits the maximum number of rows to delete.

The following examples use the employees table.

Deletes the employee with ID 5.

sample_delete.sql

DELETE FROM employees WHERE id = 5;

Query OK, 1 row affected (0.01 sec)

Verifies the deletion result.

sample_delete.sql

SELECT * FROM employees;

+----+------------------+------------+-------------+
| id | name             | department | retire_date |
+----+------------------+------------+-------------+
|  3 | Ayanami Rei      | NERV       | NULL        |
|  4 | Ikari Shinji     | NERV       |  2023-03-31 |
|  6 | Katsuragi Misato | NERV       | NULL        |
+----+------------------+------------+-------------+
3 rows in set

Deletes all employees who have a retirement date set (executed against the original table).

sample_delete.sql

DELETE FROM employees WHERE retire_date IS NOT NULL;

Query OK, 2 rows affected (0.02 sec)

Verifies the deletion result. Ikari Shinji and Soryu Asuka who had retirement dates have been deleted.

sample_delete.sql

SELECT * FROM employees;

+----+------------------+------------+-------------+
| id | name             | department | retire_date |
+----+------------------+------------+-------------+
|  3 | Ayanami Rei      | NERV       | NULL        |
|  6 | Katsuragi Misato | NERV       | NULL        |
+----+------------------+------------+-------------+
2 rows in set

Deletes all employees in the 'NERV' department (executed against the original table).

sample_delete.sql

DELETE FROM employees WHERE department = 'NERV';

Query OK, 4 rows affected (0.02 sec)

Check the number of target rows before deleting (safe approach). The original table has 2 employees with a retirement date. After confirming the count, run the DELETE.

sample_delete.sql

SELECT COUNT(*) FROM employees WHERE retire_date IS NOT NULL;

+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+
1 row in set

sample_delete.sql

DELETE FROM employees WHERE retire_date IS NOT NULL;

Query OK, 2 rows affected (0.02 sec)

The basic DELETE FROM ... WHERE syntax works across all major databases.

DELETE FROM employees WHERE retire_date IS NOT NULL;

Limiting the number of deleted rows with DELETE ... LIMIT is a MySQL-only feature. PostgreSQL supports DELETE ... RETURNING, which returns the data from deleted rows — useful for confirming what was deleted or for logging purposes.

-- MySQL(limit delete count)
DELETE FROM employees WHERE retire_date IS NOT NULL LIMIT 100;

-- PostgreSQL(return deleted rows)
DELETE FROM employees WHERE retire_date IS NOT NULL RETURNING id, name;

DELETE removes rows, but the table itself and its structure (column definitions, indexes, etc.) remain intact. Deleted rows can also be restored with ROLLBACK as long as the operation is within a transaction.

Omitting the WHERE clause deletes all rows. Always verify the number of target rows with SELECT COUNT(*) or similar before executing. It is strongly recommended to run operations in a production environment within a transaction.

If you need to delete all rows from a table, consider using TRUNCATE, which is faster and does not write to the transaction log. Note, however, that TRUNCATE cannot be protected by a transaction.