AlgoInsight

Q&A

How to use "delete" in SQL

By AlgoInsight Team2026-06-07databasessqlsql

Overview

DELETE is one of the core data-manipulation statements. Like UPDATE, its single most important habit is pairing it with a precise WHERE - a missing or wrong condition can wipe an entire table. Understanding DELETE vs TRUNCATE vs DROP, and using transactions for safety, is what this question targets.

graph TD
    A["DELETE FROM table"] --> B{WHERE condition?}
    B -->|yes| C[Only matching rows removed]
    B -->|NO WHERE| D[⚠ ALL rows deleted!]
    style D fill:#6366f1,color:#fff
    style C fill:#1e1b4b,color:#fff

Basic Syntax

DELETE FROM employees
WHERE id = 101;

Removes the single row where id = 101.

Conditional / Bulk Deletes

DELETE FROM orders
WHERE status = 'cancelled'
  AND created_at < '2025-01-01';

Any WHERE expression works - combine conditions with AND/OR, use IN, LIKE, etc.

Deleting Based on Another Table

Subquery

DELETE FROM orders
WHERE customer_id IN (
    SELECT id FROM customers WHERE inactive = 1
);

JOIN (syntax varies by database)

-- MySQL
DELETE o FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.inactive = 1;

-- PostgreSQL uses USING
DELETE FROM orders o
USING customers c
WHERE o.customer_id = c.id AND c.inactive = 1;

DELETE vs TRUNCATE vs DROP

Statement Removes WHERE? Rollback Resets identity Speed (all rows)
DELETE Rows (some or all) Yes Yes (in txn) No Slower (row-by-row, logged)
TRUNCATE All rows No Limited (DB-dependent) Usually yes Very fast
DROP The whole table No Yes (in txn, some DBs) N/A Removes structure too

Safety Practices

  1. Always write the WHERE first, or test it with a SELECT:
    SELECT * FROM employees WHERE id = 101;   -- verify the target rows first
  2. Use a transaction so mistakes can be undone:
    BEGIN;
DELETE FROM employees WHERE id = 101;
-- check row count, then:
COMMIT;   -- or ROLLBACK;
  3. Be mindful of foreign keys - deleting a parent row may fail or cascade (ON DELETE CASCADE) depending on constraints.
  4. Consider soft deletes (a deleted_at flag) when you need to retain/recover data instead of physically removing it.

Foreign Keys and Cascades

If other tables reference the rows you delete, the database may block the delete (constraint violation) or, if configured with ON DELETE CASCADE, automatically delete the dependent rows too. Know which behavior your schema uses before bulk-deleting.

Why This Matters in Interviews

SQL interviews use DELETE to test correctness and operational safety. The headline: DELETE without WHERE removes every row (a famous production disaster). Cover deleting from related tables (subquery/JOIN), the DELETE vs TRUNCATE vs DROP distinctions (logging, rollback, identity reset, what's removed), and safety habits (SELECT-first, transactions, foreign-key/cascade awareness). Mentioning soft deletes as an alternative shows design maturity.

Common Pitfalls

References

More answers