SQL - DELETE

Overview

DELETE removes rows based on a filter. TRUNCATE removes all rows and is typically faster but more restrictive.

DELETE FROM employees WHERE department_id = 3;
-- TRUNCATE TABLE employees;  -- be careful

DELETE FROM employees WHERE department_id = 3;
-- TRUNCATE TABLE employees;  -- be careful

DELETE FROM employees WHERE department_id = 3;
-- SQLite has no TRUNCATE; use DELETE without WHERE
-- and optionally VACUUM to reclaim file space.

Prerequisite

Load the Standard Test Data.

Expected Output (live; wrapped in a transaction)

Sample Use Cases (3)

1) Delete rows with salary < 85000

2) Delete employees with NULL hired_at

3) Delete by department using join

In SQLite, use subquery; in MySQL/SQL Server, you can delete with join syntax.