SQL Tutorial · Chapter 27 of 32
The SQL DELETE statement removes rows from a table. Combined with a WHERE clause it deletes only the rows that match; without WHERE it empties the entire table. DELETE removes data but keeps the table structure, which separates it from TRUNCATE (fast removal of all rows) and DROP (removal of the table itself).
SQL DELETE Syntax
DELETE FROM table_name
WHERE condition;
-- remove every row (keep the table)
DELETE FROM table_name;There is no column list, because DELETE always removes whole rows. To blank out a single column, use UPDATE and set it to NULL instead.
Sample Table (Before)
| OrderID | CustomerID | Product | Amount | OrderDate |
|---|---|---|---|---|
| 101 | 1 | Laptop | 1200 | 2026-01-05 |
| 102 | 2 | Mouse | 25 | 2026-01-07 |
| 103 | 1 | Monitor | 300 | 2026-01-12 |
| 104 | 3 | Keyboard | 45 | 2026-02-02 |
| 105 | 6 | Headset | 80 | 2026-02-15 |
| 106 | 4 | Laptop | 1150 | 2026-03-01 |
Example 1: Delete One Row by Primary Key
DELETE FROM Orders
WHERE OrderID = 102;
SELECT OrderID, Product FROM Orders ORDER BY OrderID;| OrderID | Product |
|---|---|
| 101 | Laptop |
| 103 | Monitor |
| 104 | Keyboard |
| 105 | Headset |
| 106 | Laptop |
Five rows remain. Filtering on the primary key is the safest form of DELETE because it can never touch more than one row.
Example 2: Delete Several Rows with a Condition
DELETE FROM Orders
WHERE Amount < 100;
SELECT OrderID, Product, Amount FROM Orders ORDER BY OrderID;| OrderID | Product | Amount |
|---|---|---|
| 101 | Laptop | 1200 |
| 103 | Monitor | 300 |
| 106 | Laptop | 1150 |
Orders 104 (45) and 105 (80) are gone along with 102 from the previous step.
Example 3: Delete Orphan Rows with a Subquery
Starting again from the original six rows, remove orders whose customer does not exist in Customers.
DELETE FROM Orders
WHERE CustomerID NOT IN (SELECT CustomerID FROM Customers);
SELECT OrderID, CustomerID FROM Orders ORDER BY OrderID;| OrderID | CustomerID |
|---|---|
| 101 | 1 |
| 102 | 2 |
| 103 | 1 |
| 104 | 3 |
| 106 | 4 |
Only order 105 (customer 6) is removed. Prefer NOT EXISTS if the subquery column can contain NULL.
Example 4: Delete Inside a Transaction
A transaction lets you inspect the result and undo it if the row count is wrong.
BEGIN; -- BEGIN TRANSACTION in SQL Server
DELETE FROM Orders WHERE OrderDate < '2026-02-01';
SELECT COUNT(*) FROM Orders; -- expect 3
ROLLBACK; -- or COMMIT to keep the change| COUNT(*) |
|---|
| 3 |
After ROLLBACK all six rows are back. Autocommit is on by default in most tools, so start the transaction explicitly.
DELETE vs TRUNCATE vs DROP
| DELETE | TRUNCATE | DROP | |
|---|---|---|---|
| Removes | Selected rows (or all) | All rows | The whole table |
| WHERE allowed | Yes | No | No |
| Speed on big tables | Slow (row by row, logged) | Very fast | Very fast |
| Resets auto-increment | No | Usually yes | N/A |
| Fires triggers | Yes | No | No |
Works in MySQL, SQL Server, PostgreSQL, SQLite
- Basic DELETE … WHERE is identical in all four.
- Multi-table deletes differ: MySQL
DELETE o FROM Orders o JOIN Customers c ON ..., SQL ServerDELETE o FROM Orders o JOIN ..., PostgreSQLDELETE FROM Orders USING Customers WHERE .... SQLite has no join form; use a subquery. - TRUNCATE exists in MySQL, SQL Server and PostgreSQL; SQLite uses
DELETE FROM tablewith an internal optimisation. - Foreign keys with
ON DELETE CASCADEremove child rows automatically; without it, deleting a referenced customer fails.
Soft Deletes
Many applications never run DELETE on business data. Instead they add an IsDeleted flag or a DeletedAt timestamp and UPDATE it, then filter those rows out of normal queries. This keeps history for audits and makes accidental deletions reversible. Use a real DELETE for temporary, staging or log data that has no long-term value.
Common Mistakes
- Running DELETE without WHERE and emptying the table.
- Writing
DELETE * FROM Orders; DELETE takes no column list (MS Access is the only exception). - Deleting a parent row while child rows still reference it, causing a foreign key error.
- Using DELETE to clear a huge table when TRUNCATE would take seconds instead of minutes.
Practice Exercise
- Delete all orders placed in February 2026.
- Delete every order belonging to customers from the UK, using a subquery.
Show answers
-- 1
DELETE FROM Orders WHERE OrderDate BETWEEN '2026-02-01' AND '2026-02-28';
-- removes 104 and 105
-- 2
DELETE FROM Orders
WHERE CustomerID IN (SELECT CustomerID FROM Customers WHERE Country = 'UK');
-- removes 102Related Chapters
- SQL UPDATE – change rows instead of removing them
- SQL INSERT INTO – add rows
- SQL Subqueries – targeting rows with a nested query
- SQL from Scratch: full course overview
FAQ
What is the difference between DELETE and TRUNCATE?
DELETE removes rows one at a time, can use WHERE and can be rolled back. TRUNCATE removes every row at once, cannot filter, is much faster and usually resets auto-increment counters.
Can I recover rows after a DELETE?
Only if the DELETE ran inside a transaction that you then ROLLBACK, or if you restore from a backup. Committed deletes are permanent.
Does DELETE remove the table?
No. The table and its columns, indexes and constraints stay; only the data goes. DROP TABLE removes the table itself.
Chapter 27 of 32 · SQL from Scratch: all 32 chapters
