SQL DELETE Query

Introduction to DELETE Query in SQL

Have you ever wondered what happens when you delete your account on a social media platform or hit unsubscribe from a mailing list? It may seem like a simple action, but have you ever considered what happens in the backend that actually removes your information from their records?

When you click the Delete account or Unsubscribe button, the backend communicates with the database and deletes the row with your information. Once your data is removed, you will no longer be able to log in to the social media platform or receive any emails from the concerned mailing list since you do not exist as a user in their directory.

In the database layer, we run the DELETE query for the user whose data is no longer required and the account needs to be deleted. This is where SQL comes in - SQL (Structured Query Language) is a powerful programming language used to manage relational databases. One of its key features is the ability to delete records from a table using a DELETE query.

In the further sections of this article, we will dive deeper into how to write a DELETE query in SQL to remove unwanted records from our table. But before that, let's set up a sample employee table to use as an example. By the end of this article, you'll have a clear understanding of how to use DELETE queries to manage your database and keep your data organized.

Syntax

The syntax for a DELETE query in SQL is as follows:

• table_name: denotes the name of the table from which the records are required to be deleted.
• condition: specifies the criteria that must be met for a record to be deleted.

Example

Sample Table

Suppose we have a table employee with details of all employees working at a company 'X'. The table has not been updated for a long time and we still have the data of all employees, even if they left the organization years back. Now we don't really need these extra records, right? To remove these extra records, we use the DELETE query in SQL.

Let us first create an employee table that has the Employee ID, First Name, Last Name, Work Location, and Designation of each employee using the below query.

We then enter some records into the empty employee table.

Once the above data is inserted, we run the below SELECT query to display all the records in our employee table.

Output:

Delete Records Based on Filter Condition in SQL

Suppose in the above table, we need to delete the records of an employee with ID 58461, who has left the organization. We can use the WHERE clause to specify a condition that filters all employees having ID 58461. All the filtered records can now be deleted, leaving the other records intact. To delete the records in our employee table where the EmployeeID is 58461, the SQL query will look like this:

Output:

We get the following output when we run the SELECT query again on the employee table.

Notice that the record of the employee having ID as 58461 is deleted from the employee table.

Explanation:

The DELETE query first filters the records based on the condition if EmployeeID is equal to 58461. For all the records where this condition is True, it will execute the DELETE command and remove the rows from the table. We can also use other arithmetic operators like <, >, <=, >=, etc in our filter condition.

Using Logical Operators in the Filter Condition

Suppose we want to have more than one filter condition in our WHERE clause. For example, we want to delete records of employees who are both - Software Engineers and are from Bangalore. Since the filter condition is a boolean expression, we can make use of logical operators like AND and OR to specify more than one condition.

The syntax would remain the same as earlier, i.e. we will still use the WHERE clause. Also, we will use the same parameters: tableName and filterConditions. There can be multiple filter conditions, separated by AND or OR operators, in the form of boolean expressions.

1. Example (AND Operator):

Let us delete the records of all Software Engineers from Bangalore. The SQL query will have two conditions - one for checking if the employee is a Software Engineer and another for checking if he is from Bangalore, separated by the AND clause.