MySQL DELETE Statement

The DELETE statement in MySQL deletes or removes one or more existing records (rows) from a database table. For example, you may want to delete inactive users, canceled orders, duplicate records, expired products, or old log entries from a table.

The DELETE statement can remove a single row, multiple rows, or all rows from a table depending on your requirements. However, it does not remove specific columns because DELETE operates only on rows.

The DELETE command is one of the most important SQL commands because databases often need to remove outdated, duplicate, or unwanted data.

The DELETE statement gives you complete control over which records MySQL removes. By using conditions with the WHERE clause, you can delete a single row, multiple rows, or specific groups of rows that satisfy a particular condition.

Syntax of DELETE Statement in MySQL

The basic syntax of the DELETE statement in MySQL is:

DELETE FROM table_name
WHERE condition;

In the above syntax:

• The DELETE FROM statement tells MySQL to remove records from a table.
• The table_name specifies the name of the table from which records will be deleted.
• The WHERE clause is optional. It specifies the condition that determines which records MySQL should delete.
• MySQL deletes only those records that satisfy the specified condition in the WHERE clause.
• If you omit the WHERE clause in the DELETE statement, MySQL deletes all rows from the table.

Flow of DELETE Operation

When MySQL executes a DELETE statement, it performs the following steps:

• Reads the table.
• Evaluates the WHERE condition.
• Identifies matching rows.
• Removes matching records.
• Updates indexes.
• Commits the changes.

For large tables, this process may take time because MySQL must check every matching row.

Creating a Table in MySQL

Let’s take an example where we will create a database table named students. Look at the query below.

CREATE TABLE students
(
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    city VARCHAR(50)
);

Insert Records into the Table:

INSERT INTO students
VALUES
(1,'John',18,'Delhi'),
(2,'David',19,'Mumbai'),
(3,'Smith',20,'Kolkata'),
(4,'Alex',18,'Chennai'),
(5,'James',21,'Pune');

Display Records:

SELECT * FROM students;

Output:

Delete a Single Record in Database Table

You can delete a specific record by using the WHERE clause with a specific condition from a table.

Example 1: Deleting a Single Record

DELETE FROM students 
WHERE id = 3;

Output:

In this example, MySQL evaluates the condition id = 3 and searches for the row that satisfies it. If a matching row exists, MySQL deletes that row and leaves all other rows unchanged.

Delete Multiple Records

You can delete multiple records from a table at once by specifying a condition with a WHERE clause that matches more than one row.

Example 2: Deleting Multiple Records

DELETE FROM students
WHERE age = 18;

Output:

In this example, MySQL evaluates the condition age = 18 and searches for all rows that satisfy it. If multiple students have an age of 18, MySQL deletes all matching rows from the students table. MySQL removes the records for John and Alex because both students have an age of 18. If no row satisfies the condition, MySQL does not delete any records.

Verifying Records Before Deleting

You should always verify the matching records using a SELECT statement before executing a DELETE query. Look at the below query for it.

SELECT *
FROM students
WHERE age = 18;

This query allows you to review the records that will be deleted before running the DELETE statement.

Delete All Records from a Table

You can delete every row in the database table by omitting the WHERE clause from the DELETE statement.

DELETE FROM students;

What happens when this query executes?

• All rows from the table are removed.
• Table structure remains unchanged.
• Columns remain available.
• Constraints remain available.

MySQL DELETE with AND Operator

The AND operator combines two or more conditions in the WHERE clause. MySQL deletes a record only when all specified conditions are true.

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

In this example, MySQL evaluates two conditions:

• The student’s age must be 18.
• The student’s city must be Dhanbad.

MySQL searches for rows that satisfy both conditions simultaneously. If a row meets both conditions, MySQL deletes it. If a row satisfies only one condition, MySQL does not delete it.

For example, suppose the students table contains the following records:

When MySQL executes the DELETE statement:

• John is deleted because his age is 18 and his city is Dhanbad.
• David is not deleted because his city is Mumbai.
• Alex is not deleted because his age is 20.
• Smith is not deleted because his city is Chennai.

After deletion, the table contains:

Best Practice

Before executing the DELETE statement, check the records that match the conditions:

SELECT *
FROM students
WHERE age = 18
AND city = 'Delhi';

This SQL query displays the records that MySQL will delete, helping you avoid accidental data loss.

MySQL DELETE with OR Operator

The OR operator combines two or more conditions in the WHERE clause. MySQL deletes a record if at least one of the specified conditions is true. If all conditions are false, MySQL does not delete the row from the table.

DELETE FROM students
WHERE city = 'Dhanbad'
OR city = 'Mumbai';

In this example, MySQL evaluates two conditions:

• The city is Dhanbad.
• The city is Mumbai.

MySQL searches for all rows that satisfy either condition. If a row satisfies at least one of the conditions, MySQL deletes it.

For example, suppose the students table contains the following records:

When MySQL executes the DELETE statement:

• John is deleted because his city is Dhanbad.
• David is deleted because his city is Mumbai.
• Alex is not deleted because his city is Chennai.
• Smith is not deleted because his city is Kolkata.

After deletion, the table contains:

Best Practice

Before deleting records, you must verify the matching rows using a SELECT statement:

SELECT *
FROM students
WHERE city = 'Dhanbad'
OR city = 'Mumbai';

This query shows the records that MySQL will delete, helping you avoid accidental data loss.

DELETE Statement with BETWEEN Operator

The BETWEEN operator with DELETE statement allows you to delete records whose values fall within a specified range. The range includes both the starting and ending values.

DELETE FROM students
WHERE age BETWEEN 18 AND 20;

In this example, MySQL searches for all students whose age is between 18 and 20, inclusive. This means that students with ages 18, 19, and 20 satisfy the condition. MySQL deletes all rows that match the specified age range.

For example, suppose the students table contains the following records:

When MySQL executes the DELETE statement:

• John is deleted because his age is 18.
• David is deleted because his age is 19.
• Smith is deleted because his age is 20.
• Alex is not deleted because his age is 21.

After deletion, the table contains:

Equivalent Query

The above query is equivalent to:

DELETE FROM students
WHERE age >= 18
AND age <= 20;

MySQL DELETE Statement with IN Operator

The IN operator with DELETE statement allows you to delete records that match any value in a specified list. The IN operator compares a column value against a list of values.

DELETE FROM students
WHERE id IN (1, 3, 5);

In this example, MySQL checks whether the value of the id column exists in the specified list (1, 3, 5). If a student’s ID is 1, 3, or 5, MySQL deletes that record from the table.

For example, suppose the students table contains the following records:

When MySQL executes the DELETE statement:

• MySQL deletes John’s row because the ID is 1.
• MySQL deletes Smith’s row because the ID is 3.
• MySQL deletes James’s row because the ID is 5.
• David and Alex remain because their IDs are not in the specified list.

After deletion, the table contains:

Equivalent Query

The above query is equivalent to:

DELETE FROM students
WHERE id = 1
OR id = 3
OR id = 5;

MySQL DELETE Statement with LIMIT Clause

The LIMIT clause with DELETE statement in MySQL restricts the number of rows that the DELETE statement can remove. It is useful when you want to delete only a specific number of records instead of deleting all matching rows.

DELETE FROM students
LIMIT 2;

In this example, MySQL deletes only two rows from the students table.

Suppose the table contains the following records:

When MySQL executes the above query, it removes only the first two rows selected by MySQL. The remaining records may look like the following: