# SQL DELETE: Remove Rows From a Table

- URL: https://betteranalyst.com/sql/delete

SQL guide · Changing data

The `DELETE` statement removes rows from a table. You name the table and add a `WHERE` clause that selects the rows to remove. The table structure, its indexes and its constraints all stay in place, so `DELETE` only affects data. If you want to drop the table itself, use `DROP TABLE`.

As with `UPDATE`, the `WHERE` clause is critical. A `DELETE` without it removes every row in the table. Writing the matching `SELECT` first, confirming the row count, and running the delete inside a transaction are the standard habits that prevent expensive mistakes. After the delete you can check how many rows were affected before committing.

Deletes are checked against foreign keys. If other tables reference the rows you are removing, the statement fails unless the key was defined with `ON DELETE CASCADE` or `ON DELETE SET NULL`. For emptying a whole table quickly, `TRUNCATE TABLE` is usually faster because it does not log each row, but it cannot take a `WHERE` clause.

## Basic syntax

```
DELETE FROM table_name
WHERE condition;
```

Only rows matching the `WHERE` condition are removed. Without `WHERE`, all rows are deleted but the table remains. The `FROM` keyword is required in most databases, although SQL Server lets you omit it.

## SQL DELETE examples

### Delete a single row

Deleting by primary key removes exactly one record. This is the safest form because the key is unique, so no other rows can match.

```
DELETE FROM customers
WHERE customer_id = 42;
```

### Delete rows matching a condition

Several conditions can be combined. This removes cancelled orders from before 2024, which is a typical clean-up job. Check the row count before committing.

```
DELETE FROM orders
WHERE status = 'cancelled'
  AND order_date < '2024-01-01';
```

### Delete using a subquery

A subquery can find rows in another table. This removes customers who have never placed an order by testing for the absence of related rows with `NOT EXISTS`.

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

### Preview first, then delete in a transaction

Running a `SELECT` with the same condition shows what would be removed. Wrapping the delete in a transaction lets you roll back if the affected row count is not what you expected.

```
SELECT COUNT(*) FROM orders WHERE status = 'draft';

BEGIN;
DELETE FROM orders WHERE status = 'draft';
-- check the reported row count, then:
COMMIT;
```

## Where SQL dialects differ

**PostgreSQL:** Supports `DELETE ... USING other_table WHERE ...` to delete based on a join, and `RETURNING` to see the removed rows. There is no `LIMIT` on `DELETE`.

**MySQL:** Supports multi-table deletes such as `DELETE o FROM orders o JOIN customers c ON ...`, and `ORDER BY ... LIMIT n` for single-table deletes. In safe update mode, a delete without a key in `WHERE` is blocked.

**SQL Server:** Supports `DELETE TOP (n)`, joins with `DELETE t FROM t INNER JOIN ...`, and `OUTPUT deleted.*` to capture removed rows. `FROM` is optional after `DELETE`.

**SQLite:** Has no joined delete, so use `WHERE id IN (SELECT ...)`. A `DELETE` without `WHERE` is optimised as a truncate. `LIMIT` on `DELETE` is only available in builds compiled with the right option, and `RETURNING` arrived in 3.35.0.

**Oracle:** Uses `DELETE FROM t WHERE ...` with subqueries instead of joins, and `RETURNING ... INTO` to capture values. Limit rows with `WHERE ROWNUM <= n` or `FETCH FIRST n ROWS ONLY` in newer versions.

## Common mistakes

- Running `DELETE FROM table_name` without `WHERE` and removing every row. Always write and check the `WHERE` clause first.
- Not running a matching `SELECT` beforehand, so the wrong set of rows gets deleted. Preview the rows, then delete.
- Ignoring foreign keys. Deleting a parent row fails if child rows exist, unless cascading is configured, and cascades can remove far more data than you expect.
- Using `DELETE` to empty a very large table. It logs every row and is slow, so `TRUNCATE TABLE` is a better choice when you want everything gone and no `WHERE` is needed.
- Using `NOT IN` with a subquery that can return `NULL`. The comparison then matches nothing, so use `NOT EXISTS` instead.

## Related SQL guides

- [SQL UPDATE](https://betteranalyst.com/sql/update)
- [SQL INSERT INTO](https://betteranalyst.com/sql/insert-into)
- [SQL TRUNCATE TABLE](https://betteranalyst.com/sql/truncate-table)
- [SQL DROP TABLE](https://betteranalyst.com/sql/drop-table)
- [SQL WHERE](https://betteranalyst.com/sql/where)
- [SQL FOREIGN KEY](https://betteranalyst.com/sql/foreign-key)

[Browse all SQL guides](https://betteranalyst.com/sql)

## Need help writing the SQL?

Describe your tables and the result you want, then review the generated query against your database before running it.

[Try the AI SQL generator](https://betteranalyst.com/ai-sql-query-generator)