SQL, how to delete data and tables

Use DELETE to remove rows while keeping the table:

DELETE FROM people
WHERE id = 2;

Start with a SELECT using the same condition:

SELECT id, name
FROM people
WHERE id = 2;

This preview does not make the later delete safe by itself. Data can change between statements. For important work, use a transaction and inspect the affected-row count before committing.

This valid statement removes every row:

DELETE FROM people;

The table and its schema remain. DROP TABLE removes the table definition and its data:

DROP TABLE people;

Treat those as different operations. A migration may drop a retired table after a compatibility period. An application normally deletes particular rows.

Foreign keys can reject a delete when other rows still reference the target. A cascading foreign key can remove those child rows automatically. Read the relationship before assuming either behavior.

NULL follows the same three-valued logic as other filters. WHERE deleted_at < CURRENT_TIMESTAMP ignores rows where deleted_at is NULL, which is often exactly what a cleanup wants.

A backup is the recovery plan for a committed accidental delete. A transaction rollback works only before commit. I always preview with SELECT, then run DELETE inside a transaction when the row matters.

Some databases return the number of deleted rows. Check that count before you commit. Zero rows deleted often means the WHERE clause did not match what you expected.

TRUNCATE is another way to remove all rows quickly on some databases, but it has different locking and rollback rules. Stick with DELETE until you know you need truncate.

Try this on your own: write a SELECT and matching DELETE for one disposable row. Run both inside a transaction, inspect the result, and roll it back.