SQL DELETE Statement

The DELETE statement in SQL is the fundamental part of the Data Manipulation Language (DML) that will delete only specific rows while maintaining data integrity. The DML is a part of the Database Management System, unlike the DROP statement, which will delete the entire table with structure and data. In this blog, you will learn about the DELETE statement in SQL with examples in detail. 

Table of Contents:

• What is the SQL DELETE Statement?
• Difference between DROP, TRUNCATE, and DELETE in SQL
• Methods to Use the DELETE Statement in SQL
  • Method 1: Deleting a Single Row in SQL
  • Method 2: Deleting Multiple Rows in SQL
  • Method 3: Deleting All Rows in SQL
  • Method 4: Using ROLLBACK DELETE Operations in SQL
  • Method 5: Using the COMMIT Statement After DELETE Operations in SQL
• Advanced Methods to Use the DELETE Statement in SQL
  • Method 1: Using DELETE with JOINs in SQL
  • Method 2: Using DELETE with Subqueries in SQL
• Comparison Table of DELETE with Subqueries
• Alternative Approaches to DELETE a Record in a Table
  • What are Soft Deletes?
  • What is Archiving Data?
• Features of Soft Deletes & Archiving
  • When to Use Soft Deletes and Archiving Data?
• Conclusion

What is the SQL DELETE Statement? 

The DELETE statement in SQL is used to remove specific rows from a table without affecting the structure. It comes under the Data Manipulation Language (DML) operation. This will only modify the data and will not affect the schema or structure of the table. It will be very useful when you want to remove data from a large dataset. 

Syntax:

DELETE FROM table_name
WHERE some_condition;

The Syntax has table_name from the table that has all the records, and by mentioning the table_name, you will know which table should be removed and where the condition will delete that particular row from the mentioned table. 

Master the DELETE Statement in SQL – Unlock Advanced SQL Skills Today!

Enroll now and transform your future!

Explore Program

Difference between DROP, TRUNCATE, and DELETE in SQL

Methods to Use the DELETE Statement in SQL

There are some methods to delete a row or a specific value from the table. 

Method 1: Deleting a Single Row in SQL

The DELETE statement in SQL uses the WHERE clause to specifically delete a record from the table. 

Example:

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(50),
    city VARCHAR(50),
    email VARCHAR(100)
);
INSERT INTO customers (customer_id, name, city, email) VALUES
(1, 'Sai', 'Karnataka', '[email protected]'),
(2, 'Matheena', 'Shimla', '[email protected]'),
(3, 'Johar', 'Chattisgarh', '[email protected]');
DELETE FROM customers WHERE customer_id = 2;
SELECT * FROM customers;

Output:

Explanation: The DELETE statement removed a single row from the table using the WHERE clause and specifying the conditions. 

Method 2: Deleting Multiple Rows in SQL

The DELETE statement in SQL can delete multiple rows by specifying a condition that matches more than one row. 

Example:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    status VARCHAR(50)
);
INSERT INTO orders (order_id, customer_id, status) VALUES
(101, 1, 'Pending'),
(102, 2, 'Shipped'),
(103, 3, 'Shipped'),
(104, 4, 'Pending');
DELETE FROM orders WHERE status = 'Pending';
SELECT * FROM orders;

Output:

Explanation: The DELETE statement removed the rows that had matching conditions, like it removed the rows that had “Pending” status in the orders table. 

Method 3: Deleting All Rows in SQL

In SQL, the DELETE statement can be used to delete all the rows in the table, but it won’t delete the structure of the table. Instead, it will remove the rows inside the table. 

Example:

CREATE TABLE inventory (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    stock INT
);
INSERT INTO inventory (product_id, product_name, stock) VALUES
(1, 'Laptop', 50),
(2, 'Phone', 30),
(3, 'Tablet', 20);
SELECT * FROM inventory;

Output:

Explanation: This is the table before deleting all the rows. 

Query to delete all rows

CREATE TABLE inventory (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    stock INT
);
INSERT INTO inventory (product_id, product_name, stock) VALUES
(1, 'Laptop', 50),
(2, 'Phone', 30),
(3, 'Tablet', 20);
DELETE FROM inventory; 
SELECT * FROM inventory;

Output:

Explanation: The delete statement removed all the rows from the table, but it did not affect the structure of the table.  

Method 4: Using ROLLBACK DELETE Operations in SQL

The DELETE statement can be undone using the ROLLBACK command, as it is a DML statement. The changes can be undone only if the query is not committed to transactions. 

Example:

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(50),
    course VARCHAR(50)
);
INSERT INTO students (student_id, name, course) VALUES
(1, 'Karan', 'Machine Learning'),
(2, 'Ravi', 'Finance'),
(3, 'Hari', 'Python'),
(4, 'Gayathri', 'Hadoop'),
(5, 'Prem', 'Cybersecurity');
DELETE FROM students WHERE student_id = 5;
SELECT * FROM students;

Output:

Explanation: This is the table after deleting the 5th row using the WHERE clause condition ID 5 has been removed from the table.

Now, let us perform a rollback operation:

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(50),
    course VARCHAR(50)
);
INSERT INTO students (student_id, name, course) VALUES
(1, 'Karan', 'Machine Learning'),
(2, 'Ravi', 'Finance'),
(3, 'Hari', 'Python'),
(4, 'Gayathri', 'Hadoop'),
(5, 'Prem', 'Cybersecurity');
BEGIN TRANSACTION;
DELETE FROM students WHERE student_id = 5;
ROLLBACK;
SELECT * FROM students;

Output:

Explanation: The ROLLBACK statement undoes the changes that were made by the DELETE statement. 

Method 5: Using the COMMIT Statement After DELETE Operations in SQL

The DELETE operation can be undone, but to delete all the changes permanently, you can use
The COMMIT statement helps in making the changes permanent. Use the COMMIT TRANSACTIONS only if you are sure that the records that need to be deleted are correct. 

Example:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    salary INT
);
INSERT INTO employees (employee_id, name, department, salary) VALUES
(1, 'Salim', 'HR', 50000),
(2, 'Yash', 'Sales', 55000),
(3, 'Pavithra', 'IT', 60000),
(4, 'Dharan', 'Sales', 52000),
(5, 'Jagan', 'HR', 53000),
(6, 'Marium', 'IT', 58000),
(7, 'Gaurav', 'Marketing', 57000);
BEGIN TRANSACTION;
DELETE FROM employees WHERE employee_id = 2;
COMMIT;
SELECT * FROM employees;

Output:

Explanation: The DELETE statement is used within a transaction. The COMMIT statement will make sure the changes made are permanent and cannot be undone.  

Advanced Methods to Use the DELETE Statement in SQL

There are some advanced methods with JOINs, subqueries, and other functions to perform DELETE operations on them. 

Method 1: Using DELETE with JOINs in SQL

The DELETE statement with JOINs will remove the rows from multiple tables.

Example:

Table before deletion.

CREATE TABLE categories (
    category_id INT PRIMARY KEY,
    category_name VARCHAR(50)
);
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    category_id INT,
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
);
INSERT INTO categories (category_id, category_name) VALUES
(1, 'Electronics'),
(2, 'Discontinued');
INSERT INTO products (product_id, product_name, category_id) VALUES
(101, 'Laptop', 1),
(102, 'Old Model Phone', 2);
SELECT * FROM products; 

Output:

Explanation: This is the product table after inserting the values.

The table after the DELETE statement is used. 

CREATE TABLE categories (
    category_id INT PRIMARY KEY,
    category_name VARCHAR(50)
);
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    category_id INT,
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
);
INSERT INTO categories (category_id, category_name) VALUES
(1, 'Electronics'),
(2, 'Discontinued');
INSERT INTO products (product_id, product_name, category_id) VALUES
(101, 'Laptop', 1),
(102, 'Old Model Phone', 2);
DELETE p FROM products p
JOIN categories c ON p.category_id = c.category_id
WHERE c.category_name = 'Discontinued';
SELECT * FROM products; 

Output: 

Explanation: The JOIN combines the tables categories and products, and then the DELETE statement deletes the product based on the category “Discontinued.” 

Method 2: Using DELETE with Subqueries in SQL

You can use subqueries with the DELETE statement in SQL to improve the performance, as subqueries will be useful to remove data from another table efficiently. 

Example:

CREATE TABLE authors (
    author_id INT PRIMARY KEY,
    name VARCHAR(50),
    country VARCHAR(50)
);
CREATE TABLE books (
    book_id INT PRIMARY KEY,
    title VARCHAR(100),
    author_id INT,
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
);
INSERT INTO authors (author_id, name, country) VALUES
(1, 'APJ AK', 'USA'),
(2, 'Kiran', 'Unknown');
INSERT INTO authors (author_id, name, country) VALUES
(3, 'Chaubey', 'India');
INSERT INTO books (book_id, title, author_id) VALUES
(101, 'Wings of Fire', 1),
(102, 'Money', 2);
INSERT INTO books (book_id, title, author_id) VALUES
(103, 'Unhabitated', 3);
DELETE FROM books
WHERE author_id IN (
    SELECT author_id FROM authors WHERE country = 'Unknown'
);
SELECT * FROM books;

Output:

Explanation: Using the subquery WHERE the data from books has been deleted concerning the keyword from the authors table “Unknown.” The DELETE statement removed the data of books whose authors’ countries are unknown. 

1. Scalar Subqueries in SQL

A scalar subquery always returns only one value. It will be used to delete rows based on a condition derived from another table. Delete the book written by the most recently added author. It will be efficient in MySQL. 

Example:

CREATE TABLE authors (
    author_id INT PRIMARY KEY,
    name VARCHAR(50),
    country VARCHAR(50),
    added_date DATE 
);
CREATE TABLE books (
    book_id INT PRIMARY KEY,
    title VARCHAR(100),
    author_id INT,
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
);
INSERT INTO authors (author_id, name, country, added_date) VALUES
(1, 'APJ AK', 'India', '2024-01-01'),
(2, 'Kiran', 'USA', '2024-03-01'),
(3, 'Chaubey', 'UK', '2024-08-17');
INSERT INTO books (book_id, title, author_id) VALUES
(101, 'Wings of Fire', 1),
(102, 'Money', 2),
(103, 'Unhabitated', 3);
DELETE FROM books
WHERE author_id = (
    SELECT author_id FROM authors ORDER BY added_date DESC LIMIT 1
);
SELECT * FROM books;

Output:

Explanation: The subquery retrieved the most recently added author ID, and then the DELETE query removed the book written by the author. 

2. Multi-Row Subqueries in SQL

The DELETE statement uses multi-row subqueries in SQL, like IN, ANY, or ALL subqueries. 

Example:

CREATE TABLE authors (
    author_id INT PRIMARY KEY,
    name VARCHAR(50),
    country VARCHAR(50)
);
CREATE TABLE books (
    book_id INT PRIMARY KEY,
    title VARCHAR(100),
    author_id INT,
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
);
INSERT INTO authors (author_id, name, country) VALUES
(1, 'Karan', 'USA'),
(2, 'APJ AK', 'Unknown'),
(3, 'Alice', 'Unknown');
INSERT INTO books (book_id, title, author_id) VALUES
(101, 'Homies', 1),
(102, 'Begin Again', 2),
(103, 'Aircraft', 3);
DELETE FROM books
WHERE author_id IN (
    SELECT author_id FROM authors WHERE country = 'Unknown'
);
SELECT * FROM books;

Output:

Explanation: The subquery finds all the authors’ countries from the author table, and by the condition using the WHERE clause, it filters the authors who have an unknown country, and with the DELETE statement, it removes all the books that were linked to the author’s country. 

3. EXISTS Subqueries in SQL

The EXISTS subquery in SQL verifies whether the subquery returns at least one row or value. 

Example: 

CREATE TABLE books (
    book_id INT PRIMARY KEY,
    title VARCHAR(100),
    author_id INT
);
CREATE TABLE authors (
    author_id INT PRIMARY KEY,
    name VARCHAR(50)
);
INSERT INTO books (book_id, title, author_id) VALUES
(101, 'Homies', 1),
(102, 'Begin Again', 2),
(103, 'Aircraft', 3);
INSERT INTO authors (author_id, name) VALUES
(1, 'Karan'),
(2, 'APJ AK');
DELETE FROM books
WHERE NOT EXISTS (
    SELECT 1 FROM authors WHERE authors.author_id = books.author_id
);
SELECT * FROM books;

Output:

Explanation: The EXISTS query checks whether all the authors have matching books, and if there is no matching data, then the DELETE statement will remove those books with no matching author. 

Comparison Table of DELETE with Subqueries

Alternative Approaches to DELETE a Record in a Table

Instead of deleting the data permanently, you can archive the data or use soft deletes, which will retain your data. This will prevent data loss, and it won’t create a problem during the audit. The soft delete and archive have better data integrity and recovery options. It will be efficient if you run it in the MySQL database. 

What are Soft Deletes?

Soft delete is basically used to flag a record as deleted, but it actually won’t delete the record physically from the database. 

This can be achieved by adding a deleted_at timestamp column to the table. In the table, if the value is NULL, then it is considered active. If that column contains a date or time, then the record is considered deleted. 

Advantages of Soft Deletes:

1. The data will only be considered as deleted. It can be restored if the data is needed again. 
2. As the timestamp records the time of deletion, it is helpful in auditing and tracking. 

Example:

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100),
    deleted_at DATETIME DEFAULT NULL 
);
INSERT INTO users (user_id, name, email) VALUES
(1, 'Karnan', '[email protected]'),
(2, 'Bheem', '[email protected]'),
(3, 'Aditya', '[email protected]');
UPDATE users SET deleted_at = NOW() WHERE user_id = 2;
SELECT * FROM users WHERE deleted_at IS NULL;

Data before restoring:

--Restoring a Soft-Deleted Record
UPDATE users SET deleted_at = NULL WHERE user_id = 2;

Data after restoring: 

Explanation: The ID = 2 has been deleted using the DELETE statement, and then by using SET deleted_at = NULL, the data has been restored again. 

What is Archiving Data?

Archiving Data is moving your deleted data into a separate table. This helps to maintain the database performance, and if you want the deleted data, you can easily restore it. You can move all the old data and rarely accessed data into the archive table. This will keep the database clean and neat. 

Advantages of Archiving:

1. It will keep the main table or database efficient and free of junk.
2. You can easily restore data for auditing purposes.
3. If you delete data accidentally, you can restore it from the archive. 

Example:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    status VARCHAR(50), -- 'Completed', 'Pending', etc.
    order_date DATE
);
CREATE TABLE archived_orders (
    order_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    status VARCHAR(50),
    order_date DATE,
    archived_at DATETIME
);
INSERT INTO orders (order_id, customer_name, status, order_date) VALUES
(1, 'Alice', 'Completed', '2024-01-10'),
(2, 'Bob', 'Pending', '2024-03-05'),
(3, 'Charlie', 'Completed', '2023-12-15');
INSERT INTO archived_orders (order_id, customer_name, status, order_date, archived_at)
SELECT order_id, customer_name, status, order_date, NOW()
FROM orders WHERE status = 'Completed';
DELETE FROM orders WHERE status = 'Completed';
---To display active orders
SELECT * FROM orders;

This is the table after deletion: 

To display the archived data:

SELECT * FROM archived_orders;

Explanation: The SELECT * FROM archived_orders; retrieved the archived records from the database.

Features of Soft Deletes & Archiving

When to Use Soft Deletes and Archiving Data?

• Use Soft deletes when you want to undelete the data quickly, like in posts or user names. 
• If you want better performance and functionality, or want to see the historical records, like checking old orders, or checking transactions you can use archiving data. 

Start Your SQL Adventure!

Start Your SQL Journey for Free Today

Explore Program

Conclusion

In conclusion, the DELETE statement in SQL helps you to manage the data by removing specific rows or data from the table without affecting the table structure. You can use advanced techniques like JOINs and subqueries like scalar, multi-row, and EXISTS to improve the efficiency of the data. To restore the data with better performance, you can use soft deletes and archive data. By using the DELETE statement properly, the performance of the database can be improved.

Take your skills to the next level by enrolling in an SQL Training Course today and gaining hands-on experience. Also, prepare for job interviews with SQL interview questions, which are prepared by industry experts.