10.6 DELETE

Understanding `DELETE` in SQL

The DELETE statement removes rows from a table. It does not change the table structure, only its data.

This chapter focuses only on DELETE. Creation of tables, selection of data, and other operations are covered in their own chapters.

Basic `DELETE` Syntax

The general form of a DELETE statement is:

sql

DELETE FROM table_name
WHERE condition;

Key parts:

DELETE FROM tells the database you want to remove rows.
table_name is the table where rows will be deleted.
WHERE condition specifies which rows to delete.

Important rule
If you omit the WHERE clause, all rows in the table will be deleted.

Example table

Assume you have a users table:

sql

CREATE TABLE users (
    id      SERIAL PRIMARY KEY,
    name    VARCHAR(100),
    email   VARCHAR(200),
    active  BOOLEAN
);
INSERT INTO users (name, email, active) VALUES
('Alice', 'alice@example.com', TRUE),
('Bob',   'bob@example.com',   FALSE),
('Cara',  'cara@example.com',  TRUE),
('Dan',   'dan@example.com',   FALSE);

The data looks like this:

id	name	email	active
1	Alice	alice@example.com	TRUE
2	Bob	bob@example.com	FALSE
3	Cara	cara@example.com	TRUE
4	Dan	dan@example.com	FALSE

Deleting Specific Rows with `WHERE`

To delete only particular rows, you use a WHERE clause.

Delete by primary key

Primary keys uniquely identify each row. This is the safest way to delete a specific row.

sql

DELETE FROM users
WHERE id = 2;

After this, the row with id = 2 (Bob) is removed.

Resulting table:

id	name	email	active
1	Alice	alice@example.com	TRUE
3	Cara	cara@example.com	TRUE
4	Dan	dan@example.com	FALSE

Delete by another column

You can also delete using other columns.

Delete all inactive users:

sql

DELETE FROM users
WHERE active = FALSE;

This removes every row where active is FALSE.

Using multiple conditions

You can use logical operators like AND and OR.

Example, delete inactive users with a Gmail address:

sql

DELETE FROM users
WHERE active = FALSE
  AND email LIKE '%@gmail.com';

Only rows that match both conditions are deleted.

Using comparison operators

Operators such as =, <>, <, >, <=, >= can be used in WHERE.

Imagine an orders table:

sql

CREATE TABLE orders (
    id          SERIAL PRIMARY KEY,
    user_id     INTEGER,
    total       NUMERIC(10, 2),
    created_at  DATE
);

Delete orders with total less than 10:

sql

DELETE FROM orders
WHERE total < 10;

Delete orders older than 1 year:

sql

DELETE FROM orders
WHERE created_at < CURRENT_DATE - INTERVAL '1 year';

Deleting All Rows in a Table

Sometimes you want to empty a table completely.

`DELETE` without `WHERE`

sql

DELETE FROM users;

This removes all rows from users, but the table and its structure remain.

Effects:

Table definition is kept.
Indexes and constraints remain.
The operation can be rolled back if it is inside a transaction and not yet committed.

Dangerous operation
DELETE FROM table_name; without WHERE deletes every row.
Run it only when you are absolutely sure you want to clear the table.

`DELETE` vs `TRUNCATE` (conceptual)

TRUNCATE is another way to remove all rows from a table. Full details are usually covered elsewhere, but you should know the basic difference in behavior:

Feature	`DELETE FROM table`	`TRUNCATE TABLE table`
Uses `WHERE`	Yes	No
Removes some rows	Yes	No, always all rows
Can usually be rolled back	Yes, if in a transaction	Depends on DB, often yes
Triggers / foreign keys	Triggers fire, FK rules applied	Possibly restricted, vendor specific

In many backends, you use DELETE far more often than TRUNCATE, because you usually remove specific records.

Using `DELETE` with `IN` and `NOT IN`

Sometimes you want to delete rows that match a list of values.

Example with `IN`

Delete users with ids 1, 3, and 7:

sql

DELETE FROM users
WHERE id IN (1, 3, 7);

Example with `NOT IN`

Delete users that are not in a certain set:

sql

DELETE FROM users
WHERE id NOT IN (1, 2, 3);

Be careful when using NOT IN with NULL values in the list, because it can change the logic. This kind of null behavior is usually explained in more depth in SQL basics.

`DELETE` with `LIMIT` (vendor specific)

Standard SQL does not define LIMIT for DELETE, but some databases support it.

For example, in MySQL:

sql

DELETE FROM users
WHERE active = FALSE
LIMIT 10;

This deletes at most 10 rows that match the condition.

PostgreSQL does not support LIMIT directly with DELETE, but you can use a subquery:

sql

DELETE FROM users
WHERE id IN (
    SELECT id FROM users
    WHERE active = FALSE
    ORDER BY id
    LIMIT 10
);

Always check your database documentation, because syntax can differ.

`DELETE` with Subqueries

You can use subqueries inside WHERE to delete rows that relate to other tables or complex conditions.

Consider tables:

sql

CREATE TABLE users (
    id      SERIAL PRIMARY KEY,
    name    VARCHAR(100)
);
CREATE TABLE orders (
    id          SERIAL PRIMARY KEY,
    user_id     INTEGER,
    total       NUMERIC(10, 2)
);

Delete users who have no orders

sql

DELETE FROM users
WHERE id NOT IN (
    SELECT DISTINCT user_id FROM orders
);

This removes any user whose id does not appear in the orders table.

Delete orders for inactive users

Assume users has an active column.

sql

DELETE FROM orders
WHERE user_id IN (
    SELECT id FROM users
    WHERE active = FALSE
);

This deletes all orders that belong to inactive users.

`DELETE` and Foreign Keys

When tables are related by foreign keys, DELETE can be blocked or can cause automatic cascades, depending on how the foreign keys are defined.

Assume:

sql

CREATE TABLE users (
    id      SERIAL PRIMARY KEY,
    name    VARCHAR(100)
);
CREATE TABLE orders (
    id          SERIAL PRIMARY KEY,
    user_id     INTEGER REFERENCES users(id),
    total       NUMERIC(10, 2)
);

Deleting a referenced row

If you try:

sql

DELETE FROM users
WHERE id = 1;

and there are rows in orders with user_id = 1, the database may:

Reject the delete, if the foreign key does not allow it.
Cascade the delete and remove the related orders, if the foreign key uses ON DELETE CASCADE.

Examples of foreign key options:

Option	Effect when parent is deleted
`ON DELETE RESTRICT`	Prevent deletion if children exist
`ON DELETE CASCADE`	Automatically delete child rows
`ON DELETE SET NULL`	Set foreign key in child rows to `NULL`

Choosing these behaviors is part of schema design and is covered elsewhere, but you must remember that DELETE can be affected by them.

Safe Deletion Workflow

Because DELETE operations can permanently remove data, it is common to take some safety steps.

1. First run a `SELECT`

Instead of jumping directly to DELETE, first run a SELECT with the same WHERE condition:

sql

SELECT *
FROM users
WHERE active = FALSE
  AND email LIKE '%@example.com';

Check the returned rows. If they are exactly the rows you want to remove, then run:

sql

DELETE FROM users
WHERE active = FALSE
  AND email LIKE '%@example.com';

This is a very simple but very important habit.

Safety rule
Before a complex DELETE, run a SELECT with the same WHERE clause to confirm which rows will be deleted.

2. Use transactions for important deletes

In many databases you can use transactions:

sql

BEGIN;
DELETE FROM users
WHERE active = FALSE;
-- Check the number of deleted rows, or run a SELECT to verify
-- If correct:
COMMIT;
-- If wrong:
ROLLBACK;

If you call ROLLBACK, the changes made by DELETE are undone.

Transactions are explained in detail in their own chapter, but you should know that they are a powerful way to protect yourself when deleting or updating data.

Logical Delete vs Physical Delete

In backend applications, you often do not want to physically delete data from the database. Instead, you mark it as deleted. This is called a logical delete or soft delete.

Example of logical delete

Add a column:

sql

ALTER TABLE users
ADD COLUMN deleted_at TIMESTAMP NULL;

Instead of

sql

DELETE FROM users
WHERE id = 5;

you run:

sql

UPDATE users
SET deleted_at = NOW()
WHERE id = 5;

Your application then treats rows with deleted_at IS NOT NULL as deleted.

Compare:

Type of delete	SQL operation	Data still in table	Usually visible in app
Physical delete	`DELETE FROM ...`	No	No
Logical delete	`UPDATE ...`	Yes	No, if filtered out

Detailed design of soft deletes is an application level concern, but you should recognize that in many backends, DELETE is used rarely, and soft delete is preferred for auditability.

Common Mistakes with `DELETE`

Here are some frequent errors and how to avoid them.

Forgetting the `WHERE` clause

sql

DELETE FROM users;

instead of

sql

DELETE FROM users
WHERE id = 10;

To avoid this:

Always write the WHERE clause first when you start typing, then fill it in.
Or start with SELECT and convert it to DELETE after you confirm it.

Using `=` with `NULL`

In SQL, column = NULL does not work as expected. You must use IS NULL or IS NOT NULL.

Incorrect:

sql

DELETE FROM users
WHERE deleted_at = NULL;

Correct:

sql

DELETE FROM users
WHERE deleted_at IS NULL;

Details about NULL behavior belong in other chapters, but for DELETE you must remember this pattern.

Deleting from the wrong table

If you have similar table names, such as users and users_archive, double check that you are deleting from the correct table:

sql

DELETE FROM users_archive
WHERE ...

versus

sql

DELETE FROM users
WHERE ...

Always read the full statement before running it.

Summary

DELETE removes rows from a table, but not the table itself.
Use DELETE FROM table WHERE condition; to remove specific rows.
If you omit the WHERE clause, all rows are deleted.
Combine WHERE with operators, IN, and subqueries to target the correct rows.
Foreign keys and constraints can restrict or cascade deletes.
Use SELECT first and possibly transactions to perform deletes safely.
In real applications, soft deletes with a flag or timestamp are often used instead of physical DELETE.