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;

-- Deletes the employee with ID 5.
DELETE FROM employees WHERE id = 5;

-- Deletes all employees who have a retirement date set.
DELETE FROM employees WHERE retire_date IS NOT NULL;

-- Deletes logs older than a specific date.
DELETE FROM access_logs WHERE created_at < '2023-01-01';

-- Deletes products that no one has ordered (using a subquery).
DELETE FROM products
WHERE product_id NOT IN (SELECT DISTINCT product_id FROM orders);

-- Check the number of target rows before deleting (safe approach).
SELECT COUNT(*) FROM employees WHERE retire_date IS NOT NULL;
-- After confirming the count, run the DELETE.
DELETE FROM employees WHERE retire_date IS NOT NULL;

-- Example result of: DELETE FROM employees WHERE retire_date IS NOT NULL;
-- Query OK, 3 rows affected (0.02 sec)
-- (Indicates that 3 rows were deleted.)

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. In PostgreSQL, you can use DELETE ... USING to specify the deletion target with a JOIN-like syntax instead of a subquery.

-- MySQL (limit the number of rows deleted)
DELETE FROM access_logs WHERE created_at < '2023-01-01' LIMIT 1000;

-- PostgreSQL (use USING clause to join and filter the target)
DELETE FROM order_items
USING orders
WHERE order_items.order_id = orders.order_id AND orders.status = 'cancelled';

PostgreSQL supports DELETE ... RETURNING, which returns the data from deleted rows. This is useful for confirming what was deleted or for logging purposes.

-- 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.