SQL DELETE Statement

SQL DELETE Statement is used to remove one or more records from a database table. The primary use case of this statement is when you need to erase certain data without impacting the overall structure of the table.

The basic syntax of a Delete Query in SQL is as follows:

DELETE FROM table_name WHERE condition;

In this syntax, "table_name" is the table you want to delete the data, and "condition" is a filter applied to select specific rows to be removed.

SQL Delete Row

The process of DELETE row from table SQL is a fundamental aspect of database operations. You would typically need to delete rows from a table when you want to eliminate specific records based on certain criteria.

Let's see an example to DELETE a row SQL. Suppose we have the following "Employees" table:

Now how to delete a row in SQL? Simple, to delete a record of an employee with an "EmployeeID" of 1 the SQL DELETE Statement would be:

DELETE FROM Employees WHERE EmployeeID = 1;

After executing this SQL DELETE ROW statement, the "Employees" table would look like this:

The row containing the information of “John Doe” from the “IT” department has been removed using SQL DELETE FROM!

SQL Delete Multiple Rows

The power of the SQL DELETE ROW statement comes from its flexibility. You can DELETE MULTIPLE ROWS IN SQL by setting a condition that matches multiple records. For example, if you need to remove all employees in the "Sales" department from the "Employees" table, the command would look like this:

DELETE FROM Employees WHERE Department = 'Sales';

After executing this DELETE FROM SQL statement, the "Employees" table would no longer include any employees from the "Sales" department. You can see how useful this SQL DELETE Rows feature is!

In SQL, you can DELETE ALL ROWS FROM TABLE SQL by not specifying a WHERE clause. The syntax is as follows:

DELETE FROM table_name;

For instance, to Delete All rows from our "Employees" table you will use the following DELETE data from table SQL statement:

DELETE FROM Employees;

After running this SQL DELETE Statement, the "Employees" table would be completely empty.

Caution: Exercise caution when deleting records from a table. Pay attention to the presence of the WHERE clause in the DELETE statement, as it defines which record(s) will be removed. If you overlook the WHERE clause, the deletion operation will delete all records in the table.

In some scenarios, you might encounter duplicate rows in your table, and it's essential to know how to DELETE DUPLICATE ROWS IN SQL. This process typically involves two steps: identifying duplicates and deleting them.

Assuming we have a "Duplicates" table with the following data:

The duplicates in this table are rows with IDs 1 and 2, and 3 and 4. To delete these duplicates, you can use the following SQL DELETE Statement:

DELETE FROM Duplicates WHERE ID NOT IN (SELECT MIN(ID) FROM Duplicates GROUP BY Name);

After executing this SQL DELETE ROW statement, the "Duplicates" table would only include unique rows:

In some scenarios, you might need to delete a row only if it exists in the table. You can achieve this using the SQL DELETE IF EXISTS approach. Although in SQL Server we can also use built-in IF EXISTS statement to check if a row exists and can perform further actions, in pure SQL this is typically done using a subquery. For instance, to delete an employee named "John Doe", only if his data exists in the "Employees" table, you would write:

DELETE FROM Employees WHERE EmployeeID = (SELECT EmployeeID FROM Employees WHERE FirstName = 'John' AND LastName = 'Doe');

If "John Doe" exists in the "Employees" table, this SQL DELETE Statement will remove his record.

In addition to deleting rows, if we want to know how to DELETE table in SQL entirely, then SQL also provided a way for that. It is performed using the DROP TABLE statement:

DROP TABLE table_name;

For instance, to delete our "Employees" table, we would use:

DROP TABLE Employees;

This SQL DELETE TABLE statement will remove the "Employees" table from the database entirely, including all its data and schema.

When working with relational databases, we often need to work with multiple tables simultaneously. It's important to know how to SQL Delete with Join. Let's consider two tables: "Employees" and "Departments".

DELETE e
FROM Employees e
JOIN Departments d ON e.DepartmentID = d.DepartmentID
WHERE d.DepartmentName = 'Sales';

Without table alias. The above delete SQL query will look like this.

DELETE Employees
FROM Employees 
JOIN Departments  ON Employees.DepartmentID = Departments.DepartmentID
WHERE Departments.DepartmentName = 'Sales';

This SQL DELETE ROW FROM TABLE statement will remove all employees from the "Sales" department.