SQL DELETE Statement

Introduction

If you are someone who handles databases at work, I’m sure you use SQL a lot. Doesn’t SQL make it a breeze to work with and edit the contents of large databases? With so many built-in functions for all kinds of queries, SQL is a must-know tool for all data scientists and data analysts. In this article we are going to learn about one such SQL function – SQL DELETE. This is a DML (Data manipulation language) command that comes in very handy. We will look at the syntax of the SQL DELETE statement and learn how to use it through practical examples.

If you’re just starting out to explore SQL, here’s a beginner’s guide to help you: SQL For Data Science: A Beginner Guide

Overview

• Learn what the DELETE statement in SQL does.
• Learn the syntax of the statement and where to use it.
• Practice using the DELETE statement in a sample dataset.

What is SQL DELETE?

The SQL DELETE statement is a tool used to delete records existing from the table. The DELETE statement can remove records selectively from the table as well. To remove records selectively WHERE clause is used. DELETE statement is very important as it manages the integrity of the table. Note: we can delete single or multiple records based on our condition.

Basic Syntax

Below is the basic syntax of the SQL DELETE statement

DELETE FROM table_name
WHERE condition;

Note:

• WHERE clause is very important, if WHERE clause and appropriate condition is not specified, Unwanted deletion or entire deletion of records in table may occur.
• It is advised to have a backup of your data before performing any manipulations on your table.
• Be cautious with tables that have foreign key constraints.

Let’s experiment with the DELETE command with a sample table.

Creating Sample Table

To test the DELETE statement in SQL, we will first create a sample employees’ table.

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

We will insert some sample records

INSERT INTO employees (first_name, last_name, department, salary) VALUES
('John', 'Doe', 'Sales', 60000),
('Jane', 'Smith', 'Marketing', 65000),
('Jim', 'Brown', 'Sales', 55000),
('Jake', 'White', 'IT', 70000),
('Jill', 'Black', 'IT', 72000),
('Janet', 'Green', 'HR', 50000),
('James', 'Blue', 'HR', 52000),
('Jack', 'Yellow', 'Marketing', 67000),
('Julia', 'Purple', 'Sales', 63000),
('Jerry', 'Orange', 'Marketing', 62000);

DELETE a Specified Record

DELETE FROM employees
WHERE employee_id = 5;

The above code specifically deletes the record with employee_id = 5. This will delete only one record as there is only one record with employee_id as 5.

In the above image we can see that the record with employee_id as 5 was deleted. We can see Query OK, 1 row affected which implies 1 row has been deleted.

DELETE Multiple Records

DELETE FROM employees
WHERE department = 'Sales';

The above code will delete all the records with the department as Sales. This is how WHERE clause can be used to delete multiple records.

In the above image we can see that 3 rows have been deleted. There were 3 records with sales as their department. Those were deleted using our DELETE command.

DELETE all records in the table

DELETE FROM employees;

When we don’t specify our WHERE clause DELETE command will delete all the records from the table.

We can see that all the remaining 6 records from our table have been deleted. Hence we have to be careful when using the DELETE command.

DELETE Using a Subquery

DELETE FROM employees
WHERE salary < (
    SELECT avg_salary FROM (
        SELECT AVG(salary) AS avg_salary FROM employees
    ) AS derived_table
);

We would sometimes have to DELETE based on some conditions which require subqueries. The above is the example to delete records with a salary less than average salary. Since our table is empty we will add the same sample records, then use our query.

In the above image we can see that records with salary less than average salary have been deleted.

Performance Considerations

Some tips to consider when performing DELETE commands.

• Indexes:  Ensuring the columns being indexed inorder to increase the speed of the search during DELETE command
• Batch Deletion: When dealing with large tables, ensuring deletion of records in small batches in order to avoid locking the table for a long time.

Conclusion

SQL Delete statements are important to manage tables in relational databases. With the understanding of the DELETE command you can remove records effectively from tables. Always use the WHERE clause to target specific records, and ensure you have proper backups before performing delete operations. With the employees sample we can get the basics of the DELETE command.

Learn More: SQL: A Full Fledged Guide from Basics to Advanced Level

Frequently Asked Questions

badrinarayan6645541 26 Jun, 2024

Data science intern at Analytics Vidhya, specializing in ML, DL, and AI. Dedicated to sharing insights through articles on these subjects. Eager to learn and contribute to the field's advancements. Passionate about leveraging data to solve complex problems and drive innovation.