DELETE Statement in SQL

The DELETE statement removes existing records from a table.

DELETE FROM students;

The above statement deletes all records from the students table.

The basic syntax of DELETE is:

DELETE FROM table_name
WHERE condition;

The WHERE condition identifies which records should be deleted.

You can delete a particular record using its ID.

DELETE FROM students
WHERE student_id = 101;

Only the student whose ID is 101 will be deleted.

The WHERE clause is used to select the records that should be removed.

DELETE FROM employees
WHERE department = 'Sales';

All employees from the Sales department will be deleted.

If you do not use WHERE, DELETE removes all records from the table.

DELETE FROM students;

This removes every record from the students table.

Text values must normally be written inside single quotes.

DELETE FROM students
WHERE city = 'Patna';

This deletes students whose city is Patna.

Numeric values do not require quotes.

DELETE FROM students
WHERE age = 18;

This deletes records where the age is 18.

A DELETE statement can remove multiple records when several rows match the condition.

DELETE FROM students
WHERE course = 'ADCA';

All students enrolled in ADCA will be deleted.

You can use AND to specify multiple conditions.

DELETE FROM students
WHERE city = 'Patna'
AND age = 18;

Only records satisfying both conditions will be deleted.

You can use OR when either condition can match.

DELETE FROM students
WHERE city = 'Patna'
OR city = 'Gaya';

Records from either Patna or Gaya will be deleted.

The IN operator allows you to specify multiple possible values.

DELETE FROM students
WHERE city IN ('Patna', 'Gaya', 'Arrah');

Students from any of these cities will be deleted.

NOT IN deletes records whose values are not included in the specified list.

DELETE FROM students
WHERE city NOT IN ('Patna', 'Gaya');

Students from other cities will be deleted.

The BETWEEN operator can be used to delete records within a range.

DELETE FROM students
WHERE age BETWEEN 18 AND 20;

Records with ages from 18 through 20 will be deleted.

The LIKE operator can be used for pattern matching.

DELETE FROM students
WHERE name LIKE 'A%';

This deletes students whose names start with the letter A.

Use IS NULL to delete records where a column contains NULL.

DELETE FROM students
WHERE phone IS NULL;

This deletes students whose phone number is NULL.

You can use IS NOT NULL to identify records containing a value.

DELETE FROM students
WHERE phone IS NOT NULL;

This deletes records where the phone column contains a value.

You can delete records based on a date.

DELETE FROM students
WHERE admission_date = '2026-01-10';

This deletes records having the specified admission date.

Comparison operators can be used with dates.

DELETE FROM students
WHERE admission_date < '2025-01-01';

This deletes records created before January 1, 2025.

You can also delete records after a particular date.

DELETE FROM students
WHERE admission_date > '2026-01-01';

Records after the specified date will be deleted.

DELETE can use comparison operators such as:

• = Equal to
• > Greater than
• < Less than
• >= Greater than or equal to
• <= Less than or equal to
• <> Not equal to

DELETE FROM students
WHERE age > 60;

Multiple conditions can be combined to precisely select records.

DELETE FROM students
WHERE course = 'Python'
AND city = 'Patna'
AND age > 25;

Only records matching all three conditions are deleted.

A subquery can be used to identify records that should be deleted.

DELETE FROM students
WHERE course_id IN (
    SELECT course_id
    FROM courses
    WHERE course_name = 'SQL'
);

The subquery finds the relevant course IDs before DELETE removes matching students.

If another table references a record through a foreign key, deleting the parent record may be restricted.

DELETE FROM departments
WHERE department_id = 10;

The database may prevent the deletion if related records still exist.

DELETE can be used inside a transaction so that changes can be reviewed before being permanently committed.

START TRANSACTION;

DELETE FROM students
WHERE student_id = 101;

You can then decide whether to commit or roll back the transaction.

DELETE removes selected records and can use a WHERE condition.

TRUNCATE removes all records from a table and does not use WHERE.

DELETE FROM students
WHERE city = 'Patna';

TRUNCATE TABLE students;

DELETE removes records from a table.

DROP TABLE removes the entire table structure along with its data.

DELETE FROM students;

DROP TABLE students;

It is a good practice to run a SELECT query first to check which records will be deleted.

SELECT *
FROM students
WHERE city = 'Patna';

After checking the result, you can use the same condition with DELETE.

DELETE FROM students
WHERE city = 'Patna';

A common mistake is forgetting the WHERE clause.

DELETE FROM students;

This statement deletes all records from the table.

Suppose a school database contains inactive students. You can delete inactive records using a condition.

DELETE FROM students
WHERE status = 'Inactive';

Only students whose status is Inactive will be removed.

Here is a complete example of safely selecting and deleting a particular record.

SELECT *
FROM students
WHERE student_id = 105;

DELETE FROM students
WHERE student_id = 105;

First, the record is checked using SELECT. Then the same condition is used with DELETE.