The SQL DELETE statement is used to remove the records from the relational database tables permanently.
Using the DELETE command without the WHERE clause removes table data completely. For example:
This is the simplest syntax of using the DELETE SQL statement.
You should use the WHERE clause to remove only specific rows from the table. For example:
DELETE FROM table_name WHERE col_name = some_value;
The table data removed after using the DELETE command can be rolled back.
The next section shows using a DELETE statement with various examples.
The example of deleting a row
In the first example of a delete query in SQL, I will remove only one row from the table.
For that, the DELETE statement is used with the WHERE clause. The condition is set in the WHERE clause to remove the record of an employee whose id = 10.
The DELETE query:
DELETE FROM sto_employees WHERE id = 10;
The table data before and after the delete query:
How to empty a table?
As mentioned earlier, the DELETE command removes the table data completely when used without a WHERE clause. The query below shows removing the table data completely.
I wrote four queries as follows:
- The first query retrieves the complete data from the sto_employees table.
- While the second query is the “DELETE FROM” without the WHERE clause. This is placed inside the ROLLBACK block in MS SQL Server. So, after emptying the table the ROLLBACK command is executed.
- Before ROLLBACK, the table data is displayed in the graphic below.
- The last query is used to retrieve complete table data after the ROLLBACK.
See the queries and resultsets:
SELECT * FROM sto_employees; DECLARE @DelROLLBACKRemoval varchar(20) = 'RollbackDelete'; BEGIN TRAN @DelROLLBACKRemoval DELETE FROM sto_employees; SELECT * FROM sto_employees; ROLLBACK TRAN @DelROLLBACKRemoval; SELECT * FROM sto_employees;
The demo of using the IN clause in the DELETE statement
In the WHERE clause with the DELETE statement, you may use various operators for removing the specific records.
The next query uses the IN operator where I specified a few employee names to remove rows from the table.
The query:
DELETE FROM sto_employees WHERE emp_name IN ('John', 'Jimmy', 'Shaun');
You may see the difference before and after executing the DELETE command. In the second table, three records are removed from the employee’s table.
Using the BETWEEN operator with the DELETE command
Similarly, you may specify the range of values by using the SQL BETWEEN operator in the DELETE command to remove records.
The following query removes data where I used the BETWEEN operator for emp_salary column:
DELETE FROM sto_employees WHERE emp_salary BETWEEN 4000 AND 5000;
An example of using JOIN with DELETE statement
The JOIN clause enables combining different tables in the RDBMS that are related by common fields.
The JOIN clause is generally used with the SELECT statement for retrieving data.
You may also use the JOIN clause with the DELETE statement. See an example query below where I joined two tables – sto_employees and sto_orders by an INNER JOIN clause.
DELETE EMP FROM sto_employees EMP INNER JOIN sto_orders ORD ON EMP.id=ORD.emp_id WHERE emp_id IN (1,3,5) AND emp_name = 'Jimmy';