Skip to content
Tutorials Center
My learning

Topics

Lesson 59 of 125 ยท Changing Data

DELETE

Learn SQL DELETE with live examples. Remove rows with WHERE and subqueries, delete duplicates, see how foreign keys block or cascade, and compare DELETE with TRUNCATE.

DELETE removes rows from a table. It's the shortest of the data-changing statements and the most unforgiving: a deleted row is gone, with no recycle bin to restore it from. The only ways back are a transaction you haven't committed yet, or a backup.

This lesson shows how to delete exactly the rows you mean, how to delete based on other tables, what happens when other rows still point at the ones you remove, and how DELETE compares with TRUNCATE and DROP TABLE. You'll also meet soft deletes, the common alternative of hiding rows instead of removing them. The examples run on the shop database and reset on every run.

The basic form

SQL
DELETE FROM table_name
WHERE condition;

There's no column list, because DELETE always removes whole rows. To clear a single value, use UPDATE ... SET column = NULL instead.

The shop database has six customers who signed up but never ordered anything. Remove one of them, Kofi Adeyemi (customer 40):

SQL Delete one row ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The runner reports OK, 1 row affected., and 39 customers remain. As with UPDATE, deleting by primary key guarantees that at most one row goes.

Preview first, then delete

The WHERE clause of a DELETE is exactly the WHERE clause of a SELECT. That gives you a simple, reliable routine: write the SELECT first, check its rows, then replace SELECT ... with DELETE, keeping the condition untouched.

Suppose the shop wants to remove cancelled orders from before April 2025. Step one is the preview:

SQL Preview the rows ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Five orders. Now delete them, and add RETURNING (SQLite 3.35+, PostgreSQL, MariaDB) so the statement reports exactly what it removed:

SQL DELETE ... RETURNING ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

RETURNING is useful beyond checking: an application can move the returned rows into an archive, or log them, in the same step. SQL Server uses OUTPUT deleted.id for the same job.

But there's a problem with this delete. Those five orders had 13 rows in order_items, and those rows still exist, pointing at orders that no longer do. They are now orphans. Keep reading to see how the database can prevent that.

DELETE without WHERE

Leave out the WHERE and every row in the table is deleted:

SQL
DELETE FROM order_items;   -- all 292 rows are gone

The table itself remains, empty, with its columns, indexes and constraints. The same safety habits from the UPDATE lesson apply: preview with SELECT, compare the affected-row count with what you expected, and wrap risky deletes in a transaction so you can ROLLBACK if the count looks wrong.

Highlighting half a statement is a classic accident. Many SQL editors run only the selected text. If you select DELETE FROM orders but not the WHERE line below it, the editor happily runs a delete without a condition. Put the whole statement on screen and select all of it, or let the editor run the statement under the cursor.

Deleting based on other tables

The condition can use a subquery, so you can delete rows depending on what other tables contain. This is the fix for the orphans above: delete an order's items before the order, using the same condition in both statements.

SQL Delete children, then parents ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The first statement removes 13 items, the second 5 orders, and the check finds no orphans. In real code, run both deletes in one transaction so a failure in between can't leave half the job done.

NOT EXISTS works just as well. This removes all six customers who have never placed an order:

SQL
DELETE FROM customers
WHERE NOT EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.customer_id = customers.id
);

Several databases also let you join other tables directly in a DELETE. The syntax is not standard:

Database Deleting with a join
Standard SQL (all databases, incl. SQLite) DELETE FROM order_items WHERE order_id IN (SELECT ...); or WHERE EXISTS (...)
PostgreSQL DELETE FROM order_items AS oi USING orders AS o WHERE o.id = oi.order_id AND o.status = 'cancelled';
MySQL / MariaDB DELETE oi FROM order_items AS oi JOIN orders AS o ON o.id = oi.order_id WHERE o.status = 'cancelled';
SQL Server DELETE oi FROM order_items AS oi JOIN orders AS o ON o.id = oi.order_id WHERE o.status = 'cancelled';

Removing duplicate rows

A classic cleanup task: a table contains the same record several times, and you want to keep one copy. The usual trick is to keep the row with the smallest id in each group of duplicates and delete the rest.

SQL Delete duplicates

Press Run (or Ctrl + Enter) to see the result here.

The subquery finds the first id per email (1, 2 and 4), and every other row is deleted: 3 rows affected. Afterward, add a UNIQUE constraint on email so duplicates can't come back (see Constraints). The ROW_NUMBER lesson shows a more flexible version that can keep, say, the newest row per group instead.

Foreign keys: blocking and cascading deletes

Orphaned rows are a sign of a database that doesn't protect itself. A foreign key declares that a column must point at an existing row in another table. Once it's enforced, the database decides what happens when you delete a row that others still reference. The two most common choices:

  • ON DELETE RESTRICT (or the default, NO ACTION): the delete is refused while child rows exist.
  • ON DELETE CASCADE: deleting the parent automatically deletes its children too.

SQLite only enforces foreign keys after PRAGMA foreign_keys = ON;, which every connection must run. That's why the orphans earlier were allowed: the shop database declares its foreign keys, but this connection doesn't enforce them. PostgreSQL, MySQL (InnoDB) and SQL Server always enforce them.

The next example turns enforcement on and gives the two child tables different rules. The final DELETE fails on purpose:

SQL CASCADE and RESTRICT

Press Run (or Ctrl + Enter) to see the result here.

The first delete removes playlist 1 and its two tracks, leaving only the Focus tracks. The runner still says OK, 1 row affected., because the count includes only the rows a statement deletes directly, not the ones removed by a cascade. The second stops with Error: FOREIGN KEY constraint failed, because a share still points at playlist 2. Cascades are convenient but powerful: one innocent-looking delete can remove thousands of rows in other tables, so use them only where the children truly can't exist without the parent. The Foreign Keys lesson covers every option, including SET NULL.

DELETE vs TRUNCATE vs DROP

To empty a whole table, most databases offer TRUNCATE TABLE as a faster alternative to DELETE without WHERE. Instead of removing rows one by one, it deallocates the table's storage in one go. SQLite has no TRUNCATE, but it applies the same optimization automatically to a DELETE FROM without WHERE (unless triggers or foreign keys need to see each row).

DELETE FROM t TRUNCATE TABLE t DROP TABLE t
Removes the rows you choose (or all) all rows the rows and the table
WHERE allowed yes no no
Row triggers fire yes no no
Resets identity counter no yes in MySQL and SQL Server; PostgreSQL needs RESTART IDENTITY n/a
Can be rolled back yes PostgreSQL and SQL Server yes; MySQL and Oracle no (implicit commit) PostgreSQL and SQL Server yes; MySQL and Oracle no
Referenced by foreign keys checked per row usually refused (PostgreSQL: CASCADE option) refused, or drops constraints
SQL
-- PostgreSQL
TRUNCATE TABLE order_items RESTART IDENTITY;

-- MySQL / SQL Server
TRUNCATE TABLE order_items;

Use DELETE when you need a condition, triggers or a reliable rollback; TRUNCATE to empty big staging or test tables quickly; and DROP TABLE only when the table itself should disappear.

Soft deletes

Many applications never physically delete important rows. Instead they add a column such as deleted_at and mark rows as deleted:

SQL
UPDATE customers
SET deleted_at = '2026-01-15 10:30'
WHERE id = 40;

-- Every normal query then filters them out
SELECT first_name, last_name
FROM customers
WHERE deleted_at IS NULL;

Soft deletes make "undo" trivial, keep history for audits and leave foreign keys intact. The costs: every query must remember the deleted_at IS NULL filter (a view helps), UNIQUE constraints must allow a deleted and a live row with the same value, and the table keeps growing. Personal data may also have to be truly erased on request under privacy laws such as the GDPR, so a soft delete isn't always enough.

Deleting millions of rows? One giant DELETE can lock a busy table for minutes and swell the transaction log. Delete in batches instead, for example DELETE FROM logs WHERE id IN (SELECT id FROM logs WHERE created_at < '2025-01-01' LIMIT 5000);, repeated until it affects 0 rows.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does DELETE FROM orders; do?

    Without WHERE, DELETE removes every row. The table, its columns and constraints remain; DROP TABLE would remove the table itself.
  2. 2. With foreign keys enforced, tracks.playlist_id has ON DELETE CASCADE. What happens when you delete a playlist that has 3 tracks?

    CASCADE deletes the child rows along with the parent. RESTRICT would block the delete, and SET NULL would clear the column.
  3. 3. A table has rows with ids 1-6, where ids 1, 3 and 6 share one email and ids 2 and 5 share another. How many rows does DELETE ... WHERE id NOT IN (SELECT MIN(id) FROM t GROUP BY email) remove?

    The kept ids are 1 (first email), 2 (second email) and 4 (unique). Ids 3, 5 and 6 are deleted, so 3 rows.
  4. 4. Which statement is true about TRUNCATE TABLE?

    TRUNCATE removes all rows without per-row work. It has no WHERE and fires no row triggers; in MySQL and Oracle it commits implicitly. SQLite has no TRUNCATE.
  5. 5. Why does SQLite allow orphaned order_items after deleting orders in the shop database?

    SQLite parses foreign keys but enforces them only after PRAGMA foreign_keys = ON on each connection.

Summary

  • DELETE FROM table WHERE condition; removes whole rows; without WHERE, it removes them all.
  • Preview with a SELECT using the same WHERE, check the row count and use RETURNING to see what was removed.
  • Subqueries (IN, EXISTS) delete based on other tables; delete children before parents, ideally in one transaction.
  • Enforced foreign keys block (RESTRICT) or cascade (CASCADE) deletes. In SQLite, turn them on with PRAGMA foreign_keys = ON.
  • TRUNCATE empties a table fast but has no WHERE, skips triggers and can't always be rolled back; DROP TABLE removes the table itself.
  • Soft deletes (deleted_at) hide rows instead of removing them.

Next: Upserts with ON CONFLICT, where you'll insert a row or update it if it already exists.