Subjects and lessons to explore.

Law

Databases & SQL · Foundations

DELETE: Removing Rows

8 min read 5 practice questions
8 key termsSourcesReport an error
Want it in plain words first? Jump to Eli explains — the same idea, no jargon.
On this page 9 sections
  1. In 30 seconds
  2. Why this matters
  3. The college version
  4. Eli explains
  5. Worked example
  6. Key takeaway
  7. Quick check
  8. Study tools
  9. Sources & references

In 30 seconds

DELETE removes rows from a table. Its shape is simple: DELETE FROM table WHERE — the statement names the table, and the condition names which rows go. Leave WHERE out and every row is removed, the classic danger. DELETE clears rows but leaves the table standing; clears the whole table, and DROP removes the table itself. Deletion is permanent, so check your WHERE twice before you run.

Why this matters

Removing data is part of every real database job: retired products, duplicate sign-ups, test records that were never meant to stay. DELETE is the tool, and it is the one statement where a small mistake has the biggest bill — one missing and every row in a table is gone, with no undo. Knowing what DELETE does, and what it does not do, separates routine cleanup from an accident you cannot take back. TRUNCATE and DROP are nearby statements that clear or remove tables entirely, and DELETE has to be told apart from both before you type it.

The college version

DELETE removes rows

DELETE is the SQL statement for removing rows from a table. W3Schools states it plainly: the is used to delete existing records in a table. Microsoft Learn's reference describes the same operation for SQL Server — it removes one or more rows from a table or view — and SQLite's documentation is equally direct. So the working definition is short and worth memorizing: DELETE removes rows from a table, the rows you no longer want. It is one of SQL's write operations, the counterpart of INSERT (which adds rows) and UPDATE (which changes rows). Notice what the definition does not say: DELETE does not remove the table, does not touch other tables, and does not alter columns or structure. It works on rows, and the rows it removes are gone from the table.

The shape of a DELETE statement

Every DELETE statement follows the same skeleton: DELETE FROM table WHERE condition. First comes the keyword DELETE, then FROM with the table name, then a WHERE clause with a condition that picks the rows — in full, DELETE FROM table_name WHERE condition. The condition works exactly as it does in SELECT: each row is tested, and only the rows that pass are affected. For example, a small studio tracks its catalog in a games table with columns title, genre, and release_year. To remove only the retired arcade line, write DELETE FROM games WHERE genre = 'arcade';. Run it, and the arcade titles disappear while the puzzle and racing rows stay untouched. Read the statement aloud and it describes the operation: delete, from the games table, where the genre equals arcade. If the sentence matches what you want gone, the statement is probably right.

The WHERE clause is the safety rail

The condition after WHERE is not a detail; it is the whole difference between removing a few rows and removing everything. W3Schools is blunt: if you omit the WHERE clause, all records in the table will be deleted. The PostgreSQL documentation says the same thing — if the WHERE clause is absent, the effect is to delete all rows in the table, and the result is a valid, but empty table — and SQLite's manual agrees. Why does SQL allow this? Because sometimes you genuinely want every row gone, and the language trusts you to say so. The trust cuts the other way too: DELETE FROM games; with no condition is a perfectly legal statement that silently empties the table. How WHERE works in detail — its operators, quoted values, and combined conditions — belongs to the WHERE lesson; here the point is simpler: WHERE is the safety rail, and running DELETE without it is the classic accident.

DELETE, TRUNCATE, and DROP: three different verbs

Three statements remove data, and mixing them up is expensive. DELETE removes the rows your condition names — a few, many, or all, depending on the WHERE clause. TRUNCATE clears the whole table: the PostgreSQL reference says it quickly removes all rows from a set of tables, with the same effect as an unqualified DELETE but faster, since it does not scan the rows. W3Schools adds the other half: TRUNCATE TABLE deletes all the records in a table but keeps the table structure, columns, and constraints — the empty table is still there, ready for new rows. DROP goes further than both: the statement permanently deletes an existing table in a database, table structure and all. So the one-line contrast to hold onto: DELETE removes rows with conditions, TRUNCATE clears the whole table, and DROP removes the table itself.

DELETE and relationships

In a relational database, tables often point at each other through foreign keys — a column in one table whose values reference rows in another. Those links give DELETE a second job: deciding what happens to the rows that point at the rows you are removing. The PostgreSQL reference describes the standard actions. With RESTRICT (or the default NO ACTION), the database blocks the deletion: it refuses to delete a referenced row while other rows still reference it, raising an error instead of leaving broken references. With , the deletion spreads: when a referenced row is deleted, the rows referencing it are automatically deleted as well. Which behavior applies is decided when the relationship is defined, not when the DELETE runs. The mechanics of foreign keys themselves belong to the foreign-keys lesson.

Deletion is permanent

The honest framing comes last because it is the one to carry out of the lesson: DELETE is permanent, and there is no undo. The reference pages do not soften this — W3Schools' first note on the statement is simply to be careful when deleting records in a table. Removed rows are gone from the table; nothing in the language brings them back, and there is no built-in undo button. Recovery, where it exists at all, comes from backups made beforehand, and rollback-style recovery belongs to the transactions topic. That is why careful users check the WHERE clause twice, and a cheap habit pays for itself: run the same condition as a SELECT first — SELECT title FROM games WHERE genre = 'arcade'; — and look at exactly which rows would be removed before the DELETE ever runs. If the preview shows the right rows, the DELETE is safe to run. If it shows too many, or none, fix the condition first.

Eli, the EliExplains learning guide

Eli explains

The same idea, in plain words

Explain it like I’m 10

DELETE is how SQL removes rows from a table, and it is ruthlessly literal. You name the table and the condition, and it removes exactly the rows that match — no more, no less. Forget the condition and it removes every row in the table, because an empty WHERE is a condition that every row passes. The table itself survives: after DELETE, the table is still there, just with fewer rows, or none at all. That is the part beginners forget — DELETE works on rows, not on the table. Rows are the only thing DELETE knows how to remove, and once they are gone, they are gone.

Picture it like this

Think of a class roster on a whiteboard. DELETE with a condition is erasing only the names of students who moved away: you point at the board and state the rule — anyone in room 12. DELETE without a condition is erasing the whole board: every name, every column. But notice the board is still on the wall; you did not take it down. TRUNCATE is wiping the board clean with one deliberate sweep, and DROP is taking the board off the wall and throwing it away.

Where the picture stops working

The analogy has limits. A person erasing a whiteboard can stop mid-stroke or redraw a name from memory; SQL cannot. When DELETE runs, the rows are gone immediately — there is no pause and no memory to redraw from, unless the database was backed up beforehand. The board also suggests you can see what you are about to erase, while in a database you are working from what you believe is in the table — which is exactly why checking the WHERE clause first matters.

Worked example

Novelty Nook, a small game studio, tracks its catalog in a games table with columns title, genre, and release_year. The team retired its arcade line and wants those rows gone. The statement DELETE FROM games WHERE genre = 'arcade' names the table first, then the condition that picks the rows — only games in the arcade genre match. Running it removes exactly those rows; the puzzle and racing titles stay. Before running, a teammate suggests the habit that saves careers: preview with the same condition as a SELECT — SELECT title FROM games WHERE genre = 'arcade' — to see exactly which rows will vanish. The preview returns the three arcade titles, the team confirms, and only then does the DELETE run. Had someone typed DELETE FROM games with no WHERE, the entire catalog would have been gone with no undo.

Key takeaway

DELETE removes rows — only the rows your WHERE condition names, and only from the table you name. Leave the WHERE out and every row goes. There is no undo: check the condition twice, then run.

Quick check

3 questions here, of 5 in this lesson’s practice set. Answers stay hidden until you check.

Question 1 of 3foundational

What does the DELETE statement do in SQL?

Choose an answer, then check it.
Question 2 of 3intermediate

In the statement DELETE FROM games WHERE genre = 'arcade', which part picks which rows are removed?

Choose an answer, then check it.
Question 3 of 3intermediate

A games table holds 120 rows. Which statement removes only the strategy games?

Choose an answer, then check it.
Practice all 5

Keep learning

Ready to build on this? Continue to the next lesson.

Practice this lesson
Study tools & related lessonsYou’ll learn to · Common mistakes · Easily confused · Key vocabulary · Related

You’ll learn to

  • Define DELETE as the SQL statement that removes rows from a table.
  • Write a basic DELETE statement in the shape DELETE FROM table WHERE condition.
  • Explain that omitting the WHERE clause removes every row in the table.
  • Contrast DELETE with TRUNCATE (clears all rows) and DROP (removes the table itself).
  • Explain that foreign keys can block or cascade the deletion of referenced rows.

Common mistakes

  • Running DELETE FROM games with no WHERE clause when only some rows were meant to go.

    Without WHERE, DELETE removes every row in the table. State the condition — DELETE FROM games WHERE genre = 'arcade' — and preview it with a SELECT first.

  • Writing DELETE * FROM games, as if DELETE took a column list like SELECT.

    DELETE has no column list and no asterisk. The shape is DELETE FROM table WHERE condition, and rows are removed whole.

  • Using DELETE when the goal is to clear the entire table fast.

    DELETE with no WHERE works, but TRUNCATE is the statement built for clearing all rows at once; it keeps the table ready for new rows.

  • Expecting DROP TABLE to behave like DELETE.

    DELETE removes rows and leaves the table standing; DROP TABLE removes the table itself, columns and all, with no way back short of recreating it.

  • Assuming deleted rows can be brought back with an undo command.

    Deletion is permanent — there is no built-in undo. Backups are the only recovery path, so double-check the WHERE clause before running.

Easily confused

DELETE vs. TRUNCATE

DELETE removes the rows your WHERE condition names; TRUNCATE clears all rows in one operation.

DELETE vs. DROP

DELETE removes rows but keeps the table; DROP removes the table itself.

DELETE with WHERE vs. DELETE without WHERE

With WHERE, only the matching rows go; without it, every row in the table goes.

Key vocabulary

DELETE statement
The SQL statement that removes rows from a table while leaving the table itself intact.
WHERE clause
The part of a DELETE statement that states which rows should be removed.
TRUNCATE
A statement that removes all rows from a table quickly while keeping the table structure.
DROP TABLE
A statement that removes a table itself from the database, along with all of its rows.
foreign key
A column whose values reference rows in another table, which can block or cascade deletions of those rows.
cascade
A foreign key behavior in which deleting a referenced row also deletes the rows that reference it.
condition
A true-or-false test written after WHERE that decides which rows a statement affects.
permanent deletion
A deletion that cannot be reversed, because removed rows are gone and no built-in undo exists.

Sources & references

  1. SQL DELETE Statement — W3Schools
  2. PostgreSQL Documentation: DELETE — PostgreSQL
  3. SQLite Query Language: DELETE — SQLite
  4. SQL DROP TABLE Statement — W3Schools
  5. PostgreSQL Documentation: TRUNCATE — PostgreSQL
  6. PostgreSQL Documentation: Constraints (Section 5.5.4, Primary Keys) — PostgreSQL Global Development Group
  7. DELETE (Transact-SQL) — Microsoft Learn

EliExplains lessons are original prose written from the open, credible references above. See Copyright & Licensing.

Researched 2026-08-21

Educational content only. It is not medical, legal or professional advice. Found an error? Tell us.