What does DELETE FROM orders WHERE status = 'DRAFT' remove, and what happens if you omit WHERE?
answer
- Rows, never columns
- WHERE is optional here
- No column list in the syntax
- Missing WHERE means the whole table
- Clearing one field is UPDATE ... SET NULL
basics
~10 sIt removes every whole row of orders whose status is 'DRAFT'; the table and all other rows stay. With no WHERE clause, DELETE FROM orders removes every row in the table.
solid answer
~50 s`DELETE` operates on **whole rows of exactly one table**. The `WHERE` search condition is a row filter: each row of `orders` is tested, and only rows for which the condition is TRUE are removed, so `status = 'DRAFT'` removes the draft orders and leaves everything else untouched. Drop the `WHERE` clause and the filter is gone — `DELETE FROM orders` removes every row, one row at a time, and the statement is still ordinary DML, so it can be rolled back if it runs inside an open transaction that you have not committed. What `DELETE` never does is remove *part* of a row: there is no column list, so `DELETE status FROM orders` is not valid SQL. Clearing one column is `UPDATE orders SET status = NULL`. And the table itself, with its columns, indexes and constraints, survives a `DELETE` — removing the table is `DROP TABLE`.
code
sql · 8 lines-- preview first: identical FROM/WHERE, different head
SELECT COUNT(*) FROM orders WHERE status = 'DRAFT';
-- then the destructive statement
DELETE FROM orders WHERE status = 'DRAFT';
-- no WHERE clause: removes every row, table stays
DELETE FROM orders;go deeper
Be ready to write a correct DELETE with a WHERE clause on the spot and to say plainly that DELETE removes whole rows, never one column, and never the table itself.
Explain that WHERE is an ordinary search condition evaluated per row, that omitting it is legal and removes everything, and that the statement reports an affected-row count you should check.
Show the habit as well as the syntax: preview with the twin SELECT, run destructive statements inside an explicit transaction, verify the row count, and only then commit.
Own the guard rails around destructive DML in a team: who may run ad-hoc deletes on production, whether such statements go through reviewed migrations, and what the recovery path is when someone commits an unqualified DELETE.
## What a DELETE statement actually does `DELETE` is the DML statement that removes rows. Its standard form is: ```sql DELETE FROM orders WHERE status = 'DRAFT'; ``` The unit of deletion is the **row**, and the target is **exactly one table**. There is no column list anywhere in the syntax, because "delete half a row" is not a thing a relational table can represent — a row either exists or it does not. Beginners frequently try `DELETE status FROM orders` or `DELETE * FROM orders`, borrowing the shape of `SELECT *`; both are syntax errors. The standard also requires the `FROM` keyword, though some dialects (T-SQL, for example) accept `DELETE orders WHERE ...` without it — writing `DELETE FROM` always works and always reads unambiguously. ## WHERE is a row filter, not a safety feature The `WHERE` clause of a `DELETE` uses the same search-condition grammar as a `SELECT`: comparisons, `AND`/`OR`/`NOT`, `IN`, `BETWEEN`, `LIKE`, `EXISTS`, subqueries. Each row of the target table is evaluated against it, and a row is removed only when the condition evaluates to TRUE. That is why the safest way to understand a `DELETE` you are about to run is to run its twin `SELECT` first — keep the `FROM` and `WHERE` byte-for-byte identical and only swap the head of the statement: ```sql SELECT COUNT(*) FROM orders WHERE status = 'DRAFT'; ``` Whatever count that returns is exactly the number of rows the `DELETE` will remove. ## Omitting WHERE `WHERE` is optional. `DELETE FROM orders;` is legal SQL and removes **every** row of `orders`. The engine does not warn you, does not require a confirmation, and does not treat it as a special statement — it is the same statement with the filter set to "all rows". Interactive clients sometimes add their own guard rails (a "safe update mode", a required key predicate), but those are client or session features, not SQL semantics, and you should never rely on one being switched on. An unqualified `DELETE` is still DML: it removes rows one by one, fires whatever row triggers the table has, respects constraints, and participates in the surrounding transaction. If you are inside an explicit transaction that has not committed, `ROLLBACK` puts the rows back. If your session is in autocommit mode — the default in most client tools and drivers — the statement commits the moment it succeeds and there is nothing to roll back. ## DELETE removes rows; other statements remove other things It helps to hold three statements apart: - `DELETE FROM orders WHERE ...` — removes rows, keeps the table. - `UPDATE orders SET note = NULL WHERE ...` — keeps the rows, clears a column value. - `DROP TABLE orders` — removes the table definition itself along with its data, indexes, constraints and privileges. So "delete the customer's phone number" is an `UPDATE`, not a `DELETE`, and "delete the orders table" is a `DROP`. ## How many rows did it remove? A `DELETE` reports the number of rows it affected. SQL's diagnostics area exposes it as `ROW_COUNT` (retrieved with `GET DIAGNOSTICS`), and every client API surfaces the same number as an "update count" — JDBC's `executeUpdate` return value, for instance. A count of zero is not an error: a `DELETE` that matches nothing succeeds quietly. When you expect a statement to remove exactly one row and it reports zero, that is a signal to check the predicate rather than assume success. Conversely, a count much larger than expected is the moment to `ROLLBACK` if you were prudent enough to open a transaction first. ## Practical habits Write the `SELECT` first and convert it to a `DELETE`. Prefer deleting by primary key when you mean one row. Open an explicit transaction for anything destructive on a production table, inspect the affected-row count, and only then `COMMIT`. And read your own predicate out loud before running it — the difference between `WHERE status = 'DRAFT'` and `WHERE status <> 'DRAFT'` is one character and the entire table.
- Does DELETE FROM orders remove the table's columns, indexes or constraints?No. `DELETE` only removes rows; the table definition, its columns, indexes, constraints, triggers and privileges all remain, and the table is simply left empty. Removing the object itself is `DROP TABLE orders`, which is DDL and takes no `WHERE` clause.
- How do you find out how many rows a DELETE actually removed?The statement reports an affected-row count: SQL exposes it as `ROW_COUNT` in the diagnostics area, and client APIs return it as an update count (for example JDBC's `executeUpdate`). Zero is a valid, successful result — it just means nothing matched the predicate.
- Is a DELETE reversible?Only through the transaction. Inside an explicit transaction, `ROLLBACK` undoes the removal completely. Under autocommit — the default in most drivers and consoles — the statement commits immediately and the rows are gone as far as SQL is concerned; recovery then becomes a backup or point-in-time-restore question, not a SQL one.
DELETE is the shredder for whole pages in a filing cabinet: it can throw away any page you point at, but it cannot erase one line from a page, and it never removes the cabinet.
saying these in an interview costs you the question
- Thinks DELETE can remove a single column's value
- Writes DELETE * FROM orders, copying SELECT syntax
- Claims the engine rejects a DELETE with no WHERE
- Says DELETE removes the table like DROP TABLE does
- Assumes a DELETE can never be rolled back