The SQL DELETE command is used to remove the records from the relational database tables permanently.
Using DELETE command without the WHERE clause removes table data completely. For example:
DELETE FROM table_name;
This is the simplest syntax of using the DELETE SQL statement.
You should always use WHERE clause in order to remove only specific rows from the table. For example:
DELETE FROM table_name WHERE col_name = some_value;
If you require removing the table data completely then consider using the TRUNCATE statement. This is faster in execution as compared to DELETE FROM statement if the task is to remove table data completely.
Note that, the DELETE statement only removes table data. The table structure, constraints etc. remain in place.
The table data removed after using the DELETE command can be rolled back.
The next section shows using 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 which id = 10.
The DELETE query:
DELETE FROM sto_employees WHERE id = 10;
The table data before and after 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 again complete table data retrieval 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;
You should use TRUNCATE statement for removing table data completely. For learning more about it, go to TRUNCATE tutorial.
The demo of using IN clause in the DELETE statement
In the WHERE clause with DELETE statement, you may use various operators for removing the specific records.
The next query uses IN operator where I specified a few employee names to remove rows from the table.
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 BETWEEN operator with the DELETE command
Similarly, you may specify the range of values by using the SQL BETWEEN operator in the DELETE command for removing 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 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';
Note the table alias used after the DELETE command.